Implementation of LEFT JOIN compounds in ActiveRecord of the annex to Ruby on Rails

[shizuka]

How to achieve LEFT JOIN The compound in ActiveRecord annex to Ruby on Rails? Two tables allowed catalogs and items and a list of catalogues with the number of entries in them should be removed (even if there is no catalogue). In net SQL, the task would be as follows:

SELECT
  c.id AS id,
  c.name AS name,
  COUNT(i.id) AS total
FROM
  catalogs AS c
LEFT JOIN
  items AS i
ON
  c.id = i.catalog_id
GROUP BY
  c.id
id |   name    | total

----+-----------+-------

2 | Каталог 2 |     2

1 | Каталог 1 |     0

3 | Каталог 3 |     1

How do you do this request with ActiveRecord?

[yajahira]

Let's do the tables. catalogs and items Model 1:N. The model. Catalog further develop the method totalto contact the listed column total in response request (a column in the table) catalogs No.

class Item < ActiveRecord::Base
  belongs_to :catalog
end
class Catalog < ActiveRecord::Base

has_many :items
def total

self[:total]

end

end

Before Rails 5

Before the Rails 5 version, only the method is available joinsin which construction LEFT JOIN and ON I'd like to read it clearly. Take advantage of the appointment of the Alias. AS I can't. The names of the tables will have to be clearly signed. In addition, all columns of the table will have to be clearly marked catalogsby supplementing their expression COUNT(i.id) AS total♪

catalogs = Catalog

.joins('LEFT JOIN items ON catalogs.id = items.catalog_id')

.select('catalogs.id AS id, catalogs.name AS name, COUNT(items.id) AS total')

.group('catalogs.id')

catalogs.collect{|x| [x.id, x.name, x.total] }

=> [[2, "Каталог 2", 2], [1, "Каталог 1", 0], [3, "Каталог 3", 1]]

Rails 5

Starting with Rails 5, ActiveRecord has a separate method left_joins♪ Otherwise, such requests and results remain the same.

catalogs = Catalog

.left_joins(:items)

.select('catalogs.id AS id, catalogs.name AS name, COUNT(items.id) AS total')

.group('catalogs.id')

catalogs.collect{|x| [x.id, x.name, x.total] }

=> [[2, "Каталог 2", 2], [1, "Каталог 1", 0], [3, "Каталог 3", 1]]