# Paginate with joins messing with id

**URL:** <https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847>\
**Category:** Rails\
**Created:** [January 2, 2006, 5:15pm UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847 "2006-01-02T17:15:51Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![editkid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/977dab/32.png) [@editkid](https://www.ruby-forum.com/u/editkid)\
**Post date:** [January 2, 2006, 5:15pm UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847/1 "2006-01-02T17:15:51Z")

</div>

Hi all,

Best wishes for the new year! :’)

I’m very new to Ruby and Rails, and I ran into a problem with the  
“paginate” function. When I use the following method to get a number  
of forum topics based on a category name passed via the URI:

```
   @topic_pages, @topics = paginate :topics,
                           :joins => "INNER JOIN categories ON

```

topics.category\_id = categories.id",  
:conditions =\> [“topics.is\_deleted !=  
1 AND categories.safe\_name = ?”, params[:category\_name]],  
:order\_by =\> “topics.is\_sticky DESC,  
topics.id DESC”,  
:per\_page =\> 20

Then, on my topics listing page I try this:

```
for topic in @topics

	link_to h(topic.title), :action => 'show', :id => topic

end

```

This comes up with a number of links that all have the same ID! Not  
the ID of the topic, but it takes the ID from the category table. I’m  
really baffled as to why it does this, as I’m not specifying  
topic.category\_id or anything like that.

Any ideas would be very welcome, indeed.

All the best,  
Peter D.

---

<div class="post-metadata">

**Author:** ![editkid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/977dab/32.png) [@editkid](https://www.ruby-forum.com/u/editkid)\
**Post date:** [January 2, 2006, 7:16pm UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847/2 "2006-01-02T19:16:52Z")

</div>

Peter D. wrote:

> topics.category\_id = categories.id",  
> link\_to h(topic.title), :action =\> ‘show’, :id =\> topic  
> All the best,  
> Peter D.

Hi Peter,

try adding a :select =\> “topics.\*, categories.a\_1, …, categories.a\_n”  
to the paginate call, where a\_1, … , a\_n are the columns you need.

– stefan

---

<div class="post-metadata">

**Author:** ![editkid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/977dab/32.png) [@editkid](https://www.ruby-forum.com/u/editkid)\
**Post date:** [January 2, 2006, 7:23pm UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847/3 "2006-01-02T19:23:00Z")

</div>

Thanks so much, Stefan. That did the trick just fine!  
I hope I’ll be able to return the favor one day.

- Peter

---

<div class="post-metadata">

**Author:** ![editkid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/977dab/32.png) [@editkid](https://www.ruby-forum.com/u/editkid)\
**Post date:** [January 2, 2006, 9:03pm UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847/4 "2006-01-02T21:03:50Z")

</div>

Benjamin S. wrote:

> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

See [Paginate with joins messing with id - Rails - Ruby-Forum](http://www.ruby-forum.com/topic/50378#new)

– stefan

---

<div class="post-metadata">

**Author:** ![editkid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/977dab/32.png) [@editkid](https://www.ruby-forum.com/u/editkid)\
**Post date:** [January 2, 2006, 8:15pm UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847/5 "2006-01-02T20:15:20Z")

</div>

> Thanks so much, Stefan. That did the trick just fine!  
> I hope I’ll be able to return the favor one day.

Peter, I don’t think Stefan’s reply made it to the list–could you  
please post it for future reference? Thanks.

-Ben

---

<div class="post-metadata">

**Author:** ![editkid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/977dab/32.png) [@editkid](https://www.ruby-forum.com/u/editkid)\
**Post date:** [January 31, 2006, 7:48pm UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847/6 "2006-01-31T19:48:54Z")

</div>

Stefan K. wrote:

> Benjamin S. wrote:
> 
> > removed\_email\_address@domain.invalid  
> > [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)
> 
> See [Paginate with joins messing with id - Rails - Ruby-Forum](http://www.ruby-forum.com/topic/50378#new)
> 
> – stefan

I ran into a similar problem where I was and then using two columns  
called name from the joined tables. I had wanted to sort on the  
alphabetic order of a column in the joining (ie has\_many) table.  
Paginate with joins was overwriting columns with the same name from the  
two different tables in this has\_many & belongs\_to relationship.

In my example, I had a table called “collections” with a field  
collections.name, and a table named “properties” with a field  
properties.name. I wanted to sort on collections.name. I ran the  
following paginate:

@property\_pages, @properties = paginate :properties, :per\_page =\> 10,  
:joins =\> “INNER JOIN collections ON properties.collection\_id =  
collections.id”, :order\_by =\> ‘collections.name’

yet still had a problem. So I followed the advice presented here and  
modified the code to add:

:select =\> “properties.\*, collections.name”

But I found it overwriting the properties.name column with  
collections.name! A code loop like the below:

\<% for property in @properties %\>  
\<%=h property.name %\>  
\<%=h property.collection.name %\>  
\<% end %\>

would be in the proper order (the sort was working) but would return:

[value of collections.name column]  
[value of collections.name column]

i.e. it would return the value of the column collections.name, not  
properties.name, when calling property.name in the code. On a lark, I  
modified the :select to read:

:select =\> “properties.\*, collections.name AS collection\_name”

and this solved the problem for me. After carefully reading the replies  
above I realized that if the original messenger was not using  
categories.id from his example, he wouldn’t experience this behavior.  
But if you are using it in your sample code, or are in another situation  
like mine where your joined tables have the same column names, I think  
this will work for you!

---

<div class="post-metadata">

**Author:** ![editkid](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/e/977dab/32.png) [@editkid](https://www.ruby-forum.com/u/editkid)\
**Post date:** [March 6, 2006, 1:19am UTC](https://www.ruby-forum.com/t/paginate-with-joins-messing-with-id/55847/7 "2006-03-06T01:19:22Z")

</div>

Thanks for this hint to use an alias for the column name when the many  
to many tables both have an id column - saved my bacon!

> :select =\> “properties.\*, collections.name AS collection\_name”
> 
> and this solved the problem for me. After carefully reading the replies  
> above I realized that if the original messenger was not using  
> categories.id from his example, he wouldn’t experience this behavior.  
> But if you are using it in your sample code, or are in another situation  
> like mine where your joined tables have the same column names, I think  
> this will work for you!