Rails - Apr 2010 - SQL/Paginate help needed for has_many :through relationship

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

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
<lists-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> 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
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
> To unsubscribe from this group, send email to
>
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org<rubyonrails-talk%2Bunsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org>
> .
> 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
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.

Anthony Crumley wrote:
> How about something like...
> 
> :order=>"(CASE WHEN tags.name = ''piano'' THEN 1
ELSE 0 END) desc,
> tags.name",
> 
Thanks Anthony but that''s just a different way of getting the same 
result.  I think my ordering needs to take the join into account 
somehow, maybe with a ''group by'' or something but i
can''t get my head
around it :/
-- 
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.

How about creating a method in your model that
will do two finds.  The first will return all questions with
that tag.  The second will return those without that
tag.

Have this model return the sum of the two finds.

Using two named_scopes called with_tag and without_tag
might be a nice way of doing this, and then in your class
method

def self.with_tag_on_top(tag)
  self.with_tag(tag) + self.without_tag(tag)
end

so in your controller you can have something like

@questions = Question.with_tag_on_top(params[:tag]).paginate(:page =>
params[:page])

Franz

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

Anthony Crumley wrote:
> Max,
> 
> How about something like...
> 
> :order=>"(CASE WHEN tags.name = ''piano'' THEN 1
ELSE 0 END) desc,
> tags.name",
> 
> Anthony Crumley
> http://commonthread.com
Hi Max!

I have the same problem of this post 
http://www.ruby-forum.com/topic/207831

How did you solve that?

Thanks in advance,
Raphael Costa
-- 
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.