Max, How about something like...
:order=>"(CASE WHEN tags.name = 'piano' THEN 1 ELSE 0 END) desc, tags.name", Anthony Crumley http://commonthread.com On Mon, Apr 12, 2010 at 10:02 AM, Max Williams <[email protected]> wrote: > Hey all. I'm doing a paginated find with will_paginate, with a fairly > complex set of associations and ordering clauses. > > Here's the associations relevant to this example: > > Question > belongs_to :subject > #standard acts_as_taggable setup > has_many :taggings, :conditions => ["taggable_type => ?", "Question"] > has_many :tags, :through_taggings > > What i'm trying to do, is, for a given tag, order the results so that > the ones with that tag are at the top, and then the other ones in > alphabetical order. > > Here's an example of one of the generated find calls: > > Question.find(:all, {:per_page=>30, > :conditions=>["questions.subject_id = ?", "9"], > :page=>1, > :order=>"(tags.name = 'piano') desc, tags.name", > :include=>[:subject, {:taggings=>:tag}]}) > > For the resultant sql, rails splits it into two sql calls. The first > uses just the associations referred to in the order list, and gets the > ids of the questions. This is where the problem is occurring: here's > the first sql which rails generates: > > SELECT DISTINCT `questions`.id FROM `questions` LEFT OUTER JOIN > `taggings` ON `taggings`.taggable_id = `questions`.id AND > `taggings`.taggable_type = 'Question' LEFT OUTER JOIN `tags` ON > `tags`.id = `taggings`.tag_id WHERE (questions.subject_id = '9') ORDER > BY (tags.name = 'piano') desc, tags.name LIMIT 0, 30 > > I can copy and run this myself in mysql and see that it's not having the > desired result: the 30 question ids that it brings back don't belong to > questions which have the 'piano' keyword. > > I think that this is a grouping issue, or something similar: i think the > 'distinct questions.id' part is interacting with the joined table of > questions and taggings in such a way as to cut out the right ids. I > think that if i had a working version of the above sql query i could > work backwards and set up my find options appropriately. > > Any advice, anyone? 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 [email protected]. > To unsubscribe from this group, send email to > [email protected]<rubyonrails-talk%[email protected]> > . > For more options, visit this group at > http://groups.google.com/group/rubyonrails-talk?hl=en. > > -- 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 [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.
