Rails - Mar 2010 - Order by an h_m_t association, pushing a value to the top

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
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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

On 25 March 2010 16:07, Max Williams
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org>
wrote:
> 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
> --
> Posted via http://www.ruby-forum.com/.
>
> --
> You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
> To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
> For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
>
>


-- 
Rob Lacey
contact-+WAvBcCRUVA@public.gmane.org
http://www.robl.me

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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. :)  Ultimately i guess
we''re doing the
same thing though.
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

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). :)

cheers! max
-- 
Posted via http://www.ruby-forum.com/.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.