How to implement a LEFT JOIN connection in an ActiveRecord application on Ruby on Rails? Suppose there are two tables catalogs and items and you need to display a list of directories with the number of positions in them (even if there are no positions in the catalog). On pure SQL, the problem would be solved 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 to perform such a query using ActiveRecord?

    1 answer 1

    Let's create for the catalogs and items tables models 1: N. Moreover, in the Catalog model, we will additionally create the total method, which will refer to the calculated total column in the resulting query (in fact there is no such column in the catalogs table).

     class Item < ActiveRecord::Base belongs_to :catalog end class Catalog < ActiveRecord::Base has_many :items def total self[:total] end end

    Up to Rails 5

    Prior to Rails 5, only the joins method is available, in which the LEFT JOIN and ON constructs should be explicitly specified. Moreover, using the assignment of aliases AS will not work - the table names will have to be written explicitly. In addition, it is necessary to explicitly register all the columns of the catalogs table, adding them with the 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

    Beginning with Rails 5, a separate left_joins method is available in ActiveRecord. Otherwise, the order of forming such requests and getting results remains 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]]