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

Rails
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 => , :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 => ,
:order => “preferred, name”, :page => params[:page], :per_page=> 25)

Cheers,

RobL

On 25 March 2010 16:07, Max W. [email protected] wrote:

results with a regular find(:all) and then re-order them - i want to do
grateful for any advice - thanks, max

Posted via http://www.ruby-forum.com/.

–
You received this message because you are subscribed to the Google G. “Ruby on Rails: Talk” group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to [email protected].
For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.

–
Rob L.
[email protected]

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 => , :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