Order by an h_m_t association, pushing a value to the top

rubyonrails-talk
1

Hi all. Let's say that quizzes have many tags, through taggings.

I'm doing a search on quizzes, including various other associations, and passing in various conditions, such as "quizzes.name like '%english%'" or "quizzes.subject = 'Language'".

If i'm given a parameter :tag => "banana" then i want to order my results so that the quizzes are ordered by their tags, but with the ones with the 'banana' tag pushed up to the top of the results. I'm doing a paginated search (with will_paginate) so i don't want to get all of the results with a regular find(:all) and then re-order them - i want to do it in the sql

Can i do this by passing something to the 'order' option? Something along these pseudo-code lines (the order option is not valid but hopefully gets my requirement across):

@quizzes = Quiz.paginate(:all, :conditions => <conditions>, :order => "tags.name = 'banana' or tags.name", :page => params[:page], :per_page => 25)

grateful for any advice - thanks, max

2

I would do something like this.

SELECT id, name, IF(name = 'banana', 'A','Z') AS preferred FROM quizzes ORDER BY preferred, name

it would return results like, so it would order your results primarily by preferred and then by name second.

id name preferred 1 banana A 2 geoff B 3 adrian B

So if you replace the contents of the IF with something dynamic

@quizzes = Quiz.paginate(:all, :select => "*, IF(name = '#{params[:tag]}','A','Z') AS preferred" :conditions => <conditions>, :order => "preferred, name", :page => params[:page], :per_page=> 25)

Cheers,

RobL

3

Hi rob, thanks. I actually tried my pseudo-code and it worked, with the addition of putting 'desc' at the end. Ie, this:

@quizzes = Quiz.paginate(:all, :conditions => <conditions>, :order => "(tags.name = 'banana' or tags.name) desc", :page => params[:page], :per_page => 25)

The desc is necessary because "tags.name = 'banana'" comes out as 1 or 0 and we want the 1s to be shown before the 0s. But you know that as that's why you set 'A' or 'Z' as the result of the test.

I like your solution though, it's more obvious to the reader what is going on. Although i also like mine as it only involves changing the 'order' option and nothing else. :slight_smile: Ultimately i guess we're doing the same thing though.

4

Amendment - the last thing i posted didn't quite work - the rest of them (ie which didn't have banana) weren't sorted as i had the logic a bit screwed up. This is better: instead of this:

:order => "(tags.name = 'banana' or tags.name) desc"

do this:

:order => "(tags.name = 'banana') desc, tags.name"

Which is *now* effectively the same as yours (it wasn't before). :slight_smile:

cheers! max