Paginate with joins messing with id

Rails
1

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.

2

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

3

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

  • Peter
4

Benjamin S. wrote:

[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

See Paginate with joins messing with id - Rails - Ruby-Forum

– stefan

5

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

6

Stefan K. wrote:

Benjamin S. wrote:

[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

See Paginate with joins messing with id - Rails - Ruby-Forum

– 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!

7

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!