CODEWITHSUNDEEP
ruby-on-railsrubypostgresql
Muhammad Faisal Iqbal
Muhammad Faisal Iqbal

Reputation: 1836

Rails order by range first

I have model named Group which has many users,

Group contains the fields for min_age and max_age describing the user's with minimum age and maximum age.

Each user has its settings where sets preferred age group like 18 to 25

When a user searches for groups than I have order groups with age b/w 18 to 25 first and than rest

I am doing it with 2 queries like

groups = Group.where("min_age >=? AND max_age <=?", setting.min_age, setting.max_age)
         + Group.where("min_age <? OR max_age >?", setting.min_age, setting.max_age)

It worked but thing is I have too many other filters and I want cut short number of queries.

Is it possible to do this in single query?

Upvotes: 0

Views: 252

Answers (1)

spickermann
spickermann

Reputation: 106972

You can do that by ordering matching records before records that do not match:

Group.order("CASE WHEN min_age >= #{setting.min_age} AND max_age <= #{settings.max_age} then 1 else 2 end")

See: SQL CASE statement.

Upvotes: 1

Related Questions