CODEWITHSUNDEEP
ruby-on-railsrubyactiverecordsql-order-byrails-activerecord
Moamen Naanou
Moamen Naanou

Reputation: 1713

Change 'ORDER BY' Chain on ActiveRecord Subset

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:

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

Upvotes: 5

Views: 1100

Answers (1)

armahillo
armahillo

Reputation: 306

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.

Upvotes: 1

Related Questions