Change 'ORDER BY' Chain on ActiveRecord Subset

Question

Background:

I have Product model which includes 4 categories

class Product < ActiveRecord::Base
  enum category: [:recent, :cheapest, :most_expensive, :popular]
end

I've implemented a custom ORDER BY for each category with pagination (LIMIT 10), so when I'm getting products list, I'm getting multiple SQL queries with different ORDER BY in each query like this:

recent: SELECT "products".* FROM "products" ORDER BY "products"."created_at" DESC LIMIT 10

cheapest:SELECT "products".* FROM "products" ORDER BY "products"."price" ASC LIMIT 10

most_expensive: SELECT "products".* FROM "products" ORDER BY "products"."price" DESC LIMIT 10

popular: SELECT "products".* FROM "products" ORDER BY "products"."popularity" DESC, "products"."created_at" DESC LIMIT 10

So as mentioned, each of the above queries, results a Product::ActiveRecord_Relation contains 10 products with different order for each query.

Question:

I've added new column to Product model which is featured with boolean value, and I need to apply ORDER BY featured DESC at the beginning of each query with keeping the other ORDER BY fields as it is(i.e. popular query should be like this SELECT "products".* FROM "products" ORDER BY "products"."featured" DESC, "products"."popularity" DESC, "products"."created_at" DESC LIMIT 10).

Note: ORDER BY featured DESC is just appended at the beginning of the previous ORDER BY statement, and it is applied on the subset not on the whole model.

What I have tried?

I have tried the following scenarios:

• Add @products = @products.order(featured: :desc) in the controller but the result is not as expected because it adds the order to the end of existing order by chain.
• Use default_scope in Product model default_scope { order(featured: :desc) } but the result is not as expected because it implements the order on the whole model, but the expected result is applying the order only on the subset(10 records).
• using reorder in the controller @products = @products.reorder('').order(featured: :desc) but the result still not as expected because this remove the old order and actually I need to keep it but at the end of ORDER BY chain

The only solution I'm able to do is by using string variable to save previous order by chain, then use reorder('').order(featured: :desc) and finally append the string at the end of new ORDER BY:

current_order = @products.to_sql[@products.to_sql.downcase.index('order by')[email protected]_sql.downcase.index('limit')-1]
@products = @products.reorder("featured desc, #{current_order}" )

But I'm sure there is a better solution which I need your support to achieve it.

Summary:

As summarised in the comments below, I need the following implementation:

Given just r where r = M.order(:a), I want to run r.something(:b) and get the effect of M.order(:b).order(:a) rather than the M.order(:a).order(:b) that r.order(:b) would give you

Answer 1

Is there a reason you're not using scope chaining here? This seems like a perfect case to use it in. The use of enum is unclear, as well.

Something like this:

# /app/models/product.rb
class Product < ActiveRecord::Base
 scope :recent, { order(created_at: :desc) }
 scope :cheapest, { order(price: :asc) }
 scope :most_expensive, { order(price: :desc) }
 scope :popular, { order(popularity: :desc) }
 scope :featured, { where(featured: true) }
end

Then in your controller you could do:

# /app/controllers/products_controller.rb
...
Product.featured.cheapest.limit(10)
Product.featured.most_expensive.limit(10)
...

and so on.

AREL should build the query correctly, and IIRC you can swap the sequence of the scopes (featured after cheapest, for example) if you want them to be applied differently.