How to sort a model using .order instead of .sort_by?

Question

Product has many product attributes:

class Product < ApplicationRecord
  has_many :product_attributes
end

class ProductAttribute < ApplicationRecord
  belongs_to :product
end

I can sort it with sort_by:

@products.includes(:product_attributes).to_a.sort_by do |product|
  product.product_attributes.find_by(title: "Volume").value.to_i
end

Is it possible to make the same sort with order method?
I don’t understand how to order by particular attribute title (like "Volume", etc).

@products = Product.includes(:product_attributes).order( ??? )

Here is similar question:
Rails - Sort by join table data
Maybe I don't see the obvious, but I think it doesn't answer my question. I select item not by attribute name, but by attribute value, like "Volume". In other words, I find_by by attribute's value with title "Volume" (look at the code above). And I don't understand how to make such selection with order.

You can do this with a scope, as in the linked answer, or by just appending the scope components to `Product`. — tadman, Oct 20 '22 at 17:39
You'll need a `where(title: 'Volume').order(:value)` as a basis for this, or build up with a `left_outer_join` to restrict to one row. — tadman, Oct 20 '22 at 18:52
I'm guessing that this is some sort of Entity Attribute Value system? What datatype is `value`? Is it a numerical type or a string? — max, Oct 21 '22 at 08:28

Ritesh Choudhary · Answer 1 · 2022-10-21T07:20:23.607

Try with following

class Product < ApplicationRecord
  has_many :product_attributes
end

class ProductAttribute < ApplicationRecord
  belongs_to :product
end

Product.includes(:product_attributes)
    .where(product_attributes: {title: "Volume" })  
    .order('product_attributes.title ASC')

Reference for Order by the associations two levels deep.

Rails order by association field

Reference for Rails, querying associated records with conditions

Rails, querying associated records with conditions

You can also use Scope With Argument like below

class Product < ApplicationRecord
  has_many :product_attributes

  scope :sort_by_product_attribute_title, ->(title){
    includes(:product_attributes)
        .where(product_attributes: {title: title }) 
        .order('product_attributes.title ASC')  }  
end

class ProductAttribute < ApplicationRecord
  belongs_to :product
end

Product.sort_by_product_attribute_title("Volume")

One of the potential issues here is that even through you are doing an outer join the where clause still applies to the entire query and not just the joined rows. — max, Oct 21 '22 at 09:01

max · Answer 2 · 2022-10-21T09:11:47.873

There are a few different possible solutions - one them is to do a custom join with an additional restiction in the ON clause:

class Product < ApplicationRecord

  has_many :product_attributes

  def self.order_by_volume
    joins(join_on_product_attribute('Volume'))
      .order("volumes.value desc")
  end

  private 

  def self.join_on_product_attribute(title, join_alias = title.downcase.pluralize)
    products, attrs = Product.arel_table, ProductAttribute.arel_table.alias(join_alias)
    products.join(attrs, Arel::Nodes::OuterJoin)
               .on(
                 products[:id].eq(attrs[:product_id])
                   .and(attrs[:title].eq(title))
               )
               .join_sources.first
  end
end

irb(main):084:0> Product.order_by_volume
  Product Load (0.5ms)  SELECT "products".* FROM "products" LEFT OUTER JOIN "product_attributes" "volumes" ON "products"."id" = "volumes"."product_id" AND "volumes"."title" = 'Volume' ORDER BY volumes.value desc

Other possible solutions are a subquery or a lateral join to select the rows off the product_attributes table that just correspond to the volume attribute.

Thank you for your answer, it seems applicable. But I changed the design completely. Apologies for the delayed response. — Max Berdnikau, Feb 06 '23 at 21:29

How to sort a model using .order instead of .sort_by?

2 Answers2