Forum: Ruby on Rails Paginate with joins messing with id

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Peter D. (Guest)
on 2006-01-02 18:15
(Received via mailing list)
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.
Stefan K. (Guest)
on 2006-01-02 20:16
(Received via mailing list)
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
Peter D. (Guest)
on 2006-01-02 20:23
Thanks so much, Stefan. That did the trick just fine!
I hope I'll be able to return the favor one day.

- Peter
Benjamin S. (Guest)
on 2006-01-02 21:15
(Received via mailing list)
> 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
Stefan K. (Guest)
on 2006-01-02 22:03
(Received via mailing list)
Benjamin S. wrote:

> removed_email_address@domain.invalid
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
See http://www.ruby-forum.com/topic/50378#new

-- stefan
Nick (Guest)
on 2006-01-31 20:48
Stefan K. wrote:
> Benjamin S. wrote:
>
>> removed_email_address@domain.invalid
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
>>
> See 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!
James Carlyle (Guest)
on 2006-03-06 02:19
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!
This topic is locked and can not be replied to.