# Order by an h\_m\_t association, pushing a value to the top **URL:** \ **Category:** Rails\ **Created:** [March 25, 2010, 5:07pm UTC](https://www.ruby-forum.com/t/order-by-an-h-m-t-association-pushing-a-value-to-the-top/185755 "2010-03-25T17:07:48Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [March 25, 2010, 5:07pm UTC](https://www.ruby-forum.com/t/order-by-an-h-m-t-association-pushing-a-value-to-the-top/185755/1 "2010-03-25T17:07:48Z")
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 ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [March 25, 2010, 5:31pm UTC](https://www.ruby-forum.com/t/order-by-an-h-m-t-association-pushing-a-value-to-the-top/185755/2 "2010-03-25T17:31:17Z")
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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) 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/](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 removed\_email\_address@domain.invalid. > To unsubscribe from this group, send email to removed\_email\_address@domain.invalid. > For more options, visit this group at [http://groups.google.com/group/rubyonrails-talk?hl=en](http://groups.google.com/group/rubyonrails-talk?hl=en). – Rob L. removed\_email\_address@domain.invalid > **[Posts](https://www.robl.me/)** > > I write thems codes ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [March 25, 2010, 5:48pm UTC](https://www.ruby-forum.com/t/order-by-an-h-m-t-association-pushing-a-value-to-the-top/185755/3 "2010-03-25T17:48:30Z")
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. 🙂 Ultimately i guess we’re doing the same thing though. ---
**Author:** ![cmaxvv](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/c/85f322/32.png) [@cmaxvv](https://www.ruby-forum.com/u/cmaxvv)\ **Post date:** [March 25, 2010, 5:55pm UTC](https://www.ruby-forum.com/t/order-by-an-h-m-t-association-pushing-a-value-to-the-top/185755/4 "2010-03-25T17:55:10Z")
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