# SQL/Paginate help needed for has\_many :through relationship

**URL:** <https://www.ruby-forum.com/t/sql-paginate-help-needed-for-has-many-through-relationship/186807>\
**Category:** Rails\
**Created:** [April 12, 2010, 5:02pm UTC](https://www.ruby-forum.com/t/sql-paginate-help-needed-for-has-many-through-relationship/186807 "2010-04-12T17:02:45Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**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:** [April 12, 2010, 5:02pm UTC](https://www.ruby-forum.com/t/sql-paginate-help-needed-for-has-many-through-relationship/186807/1 "2010-04-12T17:02:45Z")

</div>

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

---

<div class="post-metadata">

**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:** [April 13, 2010, 12:39am UTC](https://www.ruby-forum.com/t/sql-paginate-help-needed-for-has-many-through-relationship/186807/2 "2010-04-13T00:39:52Z")

</div>

Max,

How about something like…

:order=\>“(CASE WHEN tags.name = ‘piano’ THEN 1 ELSE 0 END) desc,  
tags.name”,

Anthony C.

> **[Commonthread](https://commonthread.com)**
>
> Instantly connect and build rapport with anyone you meet, in person or online.

---

<div class="post-metadata">

**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:** [April 13, 2010, 10:22am UTC](https://www.ruby-forum.com/t/sql-paginate-help-needed-for-has-many-through-relationship/186807/3 "2010-04-13T10:22:57Z")

</div>

Anthony C. 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 😕

---

<div class="post-metadata">

**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:** [April 13, 2010, 11:17am UTC](https://www.ruby-forum.com/t/sql-paginate-help-needed-for-has-many-through-relationship/186807/4 "2010-04-13T11:17:19Z")

</div>

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

---

<div class="post-metadata">

**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:** [August 13, 2010, 5:19pm UTC](https://www.ruby-forum.com/t/sql-paginate-help-needed-for-has-many-through-relationship/186807/5 "2010-08-13T17:19:28Z")

</div>

Anthony C. wrote:

> Max,
> 
> How about something like…
> 
> :order=\>“(CASE WHEN tags.name = ‘piano’ THEN 1 ELSE 0 END) desc,  
> tags.name”,
> 
> Anthony C.  
> [http://commonthread.com](http://commonthread.com)

Hi Max!

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

How did you solve that?

Thanks in advance,  
Raphael Costa