Rails: Using group() on a joined table column without raw SQL-CodePudding

I have a small problem with grouping an ActiveRecord::Relation. I am trying to group a quary by a joined table column without using raw SQL.

The code at the moment looks like that:

Sale::Product.joins(stock_product::supplier).group('core_suppliers.id').first

Result:

Sale::Product Load (42989.5ms)  SELECT  `sale_products`.* FROM `sale_products` INNER JOIN `stock_products` ON `stock_products`.`deleted_at` IS NULL AND `stock_products`.`id` = `sale_products`.`stock_product_id` INNER JOIN `core_suppliers` ON `core_suppliers`.`id` = `stock_products`.`core_supplier_id` GROUP BY core_suppliers.id ORDER BY `sale_products`.`id` ASC LIMIT 1

I tried to solve this problem by using merge:

Sale::Product.joins(stock_product: :supplier).merge(::Core::Supplier.group(:id)).first

Result:

Sale::Product Load (32428.4ms)  SELECT  `sale_products`.* FROM `sale_products` INNER JOIN `stock_products` ON `stock_products`.`deleted_at` IS NULL AND `stock_products`.`id` = `sale_products`.`stock_product_id` INNER JOIN `core_suppliers` ON `core_suppliers`.`id` = `stock_products`.`core_supplier_id` GROUP BY `sale_products`.`core_supplier_id` ORDER BY `sale_products`.`id` ASC LIMIT 1

I don't understand why Active::Record doesn't group my association by the column of the merged table. Especially since this way works with ```order()````.

Thanks for your help in advance

CodePudding user response：

You can try Arel library that was introduced in Rails 3 for use in constructing SQL queries.

Just replace ::Core::Supplier.group(core_supplier: :id) to ::Core::Supplier.arel_table[:id] in your code:

Sale::Product.joins(stock_product::supplier).group(::Core::Supplier.arel_table[:id]).first

Update

If you don't want to use Arel directly in your queries you can hide Arel implementation in ApplicationRecord like this:

class ApplicationRecord < ActiveRecord::Base
  def self.[](attribute)
    arel_table[attribute]
  end
end

And than your query can be rewritten like this:

Sale::Product.joins(stock_product::supplier).group(::Core::Supplier[:id]).first