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.
7f02371ed3e5b7d8b7240697c6734bd4?d=identicon&s=25 Peter Dekkers (Guest)
on 2006-01-02 17: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 Dekkers
E555e7c34196967444a47a96395a23ab?d=identicon&s=25 Stefan Kaes (Guest)
on 2006-01-02 19:16
(Received via mailing list)
Peter Dekkers wrote:

> topics.category_id = categories.id",
>         link_to h(topic.title), :action => 'show', :id => topic
> All the best,
> Peter Dekkers
>
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
26977cf26556ab1a59cb5a7cb53bbf2c?d=identicon&s=25 Peter Dekkers (editkid)
on 2006-01-02 19:23
Thanks so much, Stefan. That did the trick just fine!
I hope I'll be able to return the favor one day.

- Peter
B554bcde846e4497eeffffc1399d3b09?d=identicon&s=25 Benjamin Stiglitz (Guest)
on 2006-01-02 20: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
E555e7c34196967444a47a96395a23ab?d=identicon&s=25 Stefan Kaes (Guest)
on 2006-01-02 21:03
(Received via mailing list)
Benjamin Stiglitz wrote:

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

-- stefan
99c89698aa4661ec1750c0fb9b798eab?d=identicon&s=25 Nick (Guest)
on 2006-01-31 19:48
Stefan Kaes wrote:
> Benjamin Stiglitz wrote:
>
>> Rails@lists.rubyonrails.org
>> 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!
2c0dd8177a8516b0299d6d436f91a190?d=identicon&s=25 James Carlyle (Guest)
on 2006-03-06 01: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.