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



  • 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?



  • 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]]




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2