ActiveRecord Order by Number of Conditions Matched

Question

I have a query in Rails that gets a set of records based upon a series of OR statements.

@properties = policy_scope(Property).withdrawn_query(@hotlist_preference.withdrawn?)
              .or(policy_scope(Property).fallen_through_query(@hotlist_preference.fallen_through?))
              .or(policy_scope(Property).time_on_market_query(@hotlist_preference.time_on_market?))
              .includes(:listings).paginate(page: params[:page], per_page: 20)

Basically there are 3 queries, A simpler version might look like this:

@properties = Property.where(state = withdrawn).or(where(state = fallen_through)).or(where(age = 4.weeks.ago))

What I want to do is order this query by the number of OR conditions each result satisfies. So say there was a property with withdrawn state and its age is 2 weeks ago, it would have an order value of 2. If at all possible I would also like to add the order value to the returned hash of properties, so I can use it in the view.

I've been struggling to find a clean way of doing this, without running the results through an identical set of queries again.

I don't know if this is supported by ActiveRecord, but you *could* consider dropping down to [raw SQL](http://stackoverflow.com/a/3289152/1954610) to achieve this? — Tom Lord, Apr 06 '17 at 09:34

score 0 · Answer 1 · answered Apr 06 '17 at 09:59

this looks very dirty but only way to achieve this

@properties = Property.where(state = withdrawn).or(where(state = fallen_through)).or(where(age = 4.weeks.ago)).order('case when properties.state = 'withdrawn' && properties.state = 'fallen_through' && properties.age = 'your date' then 10 when hen properties.state = 'withdrawn' && properties.state = 'fallen_through' then 9 when properties.state = 'fallen_through' && properties.age = 'your date' then 9 when properties.state = 'withdrawn'  && properties.age = 'your date' then 9 when properties.state = 'withdrawn' && properties.state = 'fallen_through' && properties.age = 'your date' then 10 when properties.state = 'withdrawn' then 8 when properties.state = 'fallen_through' then 8 when properties.age = 'your date' then 8 else 0 end)

I took your order statement a little further and tidied it up. As I think I could do it so that: ( CASE WHEN condition1 THEN 1 ELSE 0 END + CASE WHEN condition2 THEN 1 ELSE 0 END + CASE WHEN condition3 THEN 1 ELSE 0 END) — rmaspero, Apr 06 '17 at 13:24

ActiveRecord Order by Number of Conditions Matched

1 Answers1