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].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
