Rails - Dec 2005 - wrong id for activerecord object when using :joins in mysql

I''m using a find with a :joins clause, like this:

:joins => "JOIN table_b ON table_b.id=table_b_id"

The records returned are used in a scaffold generated list page. The edit,
show, and destroy links are getting the wrong id. They are getting the id of
the table b record.

When I run the query using mysql, I see that the query returns *2* id
columns, the id of the main table, and the id of the join table. The objects
returned have only one id, and apparently its the last one. It would be
better for it to be the first one.

Any suggestions for me?

This is used with paginate.

I think I''m using the native ruby mysql adapter, as compared to the c
version.

Thanks,

-Kelly


_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Hi,

Remove the id column from your join column and it should work.

On 12/21/05, Kelly Dwight Felkins
<railsinator-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> I''m using a find with a :joins clause, like this:
>
>
> :joins => "JOIN table_b ON table_b.id=table_b_id"
>
>  The records returned are used in a scaffold generated list page. The edit,
> show, and destroy links are getting the wrong id. They are getting the id
of
> the table b record.
>
>  When I run the query using mysql, I see that the query returns *2* id
> columns, the id of the main table, and the id of the join table. The
objects
> returned have only one id, and apparently its the last one. It would be
> better for it to be the first one.
>
>  Any suggestions for me?
>
>  This is used with paginate.
>
>  I think I''m using the native ruby mysql adapter, as compared to
the c
> version.
>
>  Thanks,
>
>  -Kelly
>
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
>
>

--
Cheers,

Peter Donald

Blog: http://www.RealityForge.org

How would I do that? Adding :joins  brings in all the columns from the join
table. Also, the id is the column we are joining on.



On 12/20/05, Peter Donald
<peter.j.donald-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> Hi,
>
> Remove the id column from your join column and it should work.
>
> On 12/21/05, Kelly Dwight Felkins
<railsinator-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > I''m using a find with a :joins clause, like this:
> >
> >
> > :joins => "JOIN table_b ON table_b.id=table_b_id"
> >
> >  The records returned are used in a scaffold generated list page. The
> edit,
> > show, and destroy links are getting the wrong id. They are getting the
> id of
> > the table b record.
> >
> >  When I run the query using mysql, I see that the query returns *2* id
> > columns, the id of the main table, and the id of the join table. The
> objects
> > returned have only one id, and apparently its the last one. It would
be
> > better for it to be the first one.
> >
> >  Any suggestions for me?
> >
> >  This is used with paginate.
> >
> >  I think I''m using the native ruby mysql adapter, as compared
to the c
> > version.
> >
> >  Thanks,
> >
> >  -Kelly
> >
> >
> > _______________________________________________
> > Rails mailing list
> > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > http://lists.rubyonrails.org/mailman/listinfo/rails
> >
> >
> >
>
>
> --
> Cheers,
>
> Peter Donald
>
> Blog: http://www.RealityForge.org
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

This is an active record model in a belongs_to relationship.  The convention
is to have a primary key named ''id''. I''m looking for
a solution that works
without breaking that convention.

-Kelly




On 12/20/05, Peter Donald
<peter.j.donald-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
>
> The join table should NOT have an id column. It should have two
> columns - something like
>
> create table table_as_table_bs
> {
>   table_a_id INT NOT NULL,
>   table_b_id INT NOT NULL
>   ... insert pk fk and unique constraints here..
> }
>
> On 12/21/05, Kelly Dwight Felkins
<railsinator-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > How would I do that? Adding :joins  brings in all the columns from the
> join
> > table. Also, the id is the column we are joining on.
> >
> >
> >
> > On 12/20/05, Peter Donald
<peter.j.donald-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > Hi,
> > >
> > > Remove the id column from your join column and it should work.
> > >
> > > On 12/21/05, Kelly Dwight Felkins
<railsinator-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> > > > I''m using a find with a :joins clause, like this:
> > > >
> > > >
> > > > :joins => "JOIN table_b ON
table_b.id=table_b_id"
> > > >
> > > >  The records returned are used in a scaffold generated list
page.
> The
> > edit,
> > > > show, and destroy links are getting the wrong id. They are
getting
> the
> > id of
> > > > the table b record.
> > > >
> > > >  When I run the query using mysql, I see that the query
returns *2*
> id
> > > > columns, the id of the main table, and the id of the join
table. The
> > objects
> > > > returned have only one id, and apparently its the last one.
It would
> be
> > > > better for it to be the first one.
> > > >
> > > >  Any suggestions for me?
> > > >
> > > >  This is used with paginate.
> > > >
> > > >  I think I''m using the native ruby mysql adapter,
as compared to the
> c
> > > > version.
> > > >
> > > >  Thanks,
> > > >
> > > >  -Kelly
> > > >
> > > >
> > > > _______________________________________________
> > > > Rails mailing list
> > > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > > >
> > > >
> > > >
> > >
> > >
> > > --
> > > Cheers,
> > >
> > > Peter Donald
> > >
> > > Blog: http://www.RealityForge.org
> > > _______________________________________________
> > > Rails mailing list
> > > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> > > http://lists.rubyonrails.org/mailman/listinfo/rails
> > >
> >
> >
>
>
> --
> Cheers,
>
> Peter Donald
>
> Blog: http://www.RealityForge.org
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails

Kelly Dwight Felkins wrote:
> I''m using a find with a :joins clause, like this:
> 
> :joins => "JOIN table_b ON table_b.id=table_b_id"
> 
> When I run the query using mysql, I see that the query returns *2* id 
> columns, the id of the main table, and the id of the join table. The 
> objects returned have only one id, and apparently its the last one. It 
> would be better for it to be the first one.
Either use :select => ''table_a.*, table_b.some_data, ...'',
or instead of the join use a belongs_to association plus
find''s :include option.

-- 
We develop, watch us RoR, in numbers too big to ignore.

On 12/21/05, Kelly Dwight Felkins
<railsinator-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> This is an active record model in a belongs_to relationship.  The
convention
> is to have a primary key named ''id''. I''m looking
for a solution that works
> without breaking that convention.
a join_table in rails terminology is one of the tables that implement
M-M relationship but does not have an AR model. What you want to do is
pass in something like the following. (See API for more details).

:select => ''table_as.*''

--
Cheers,

Peter Donald
Blog: http://www.RealityForge.org

Kelly Dwight Felkins wrote:
> This is an active record model in a belongs_to relationship.  The 
> convention is to have a primary key named ''id''.
I''m looking for a
> solution that works without breaking that convention.
If it''s a belongs_to relationship, then you don''t want a
:joins parameter at
all. What you want to use is :include.

In fact, the only reason to do the :joins method is if you deliberately want to 
exclude columns from the belongs_to table in the returned object/list of 
objects. Is that what you are trying to do?

-Brian

Thanks for the assistance on this. I''ve got it working by using the
:select
as suggested by Mark. I think I am in good shape, but there may be a better
way. And there may be a rails bug.

Let me try to explain the situation.

I have a list of items. These items are owned by users. The users are
organized in groups. What I want to do is list all of the items order by the
group name.

:include might have worked, but this is not a direct relationship. There are
3 models: items, users, and groups. I can see how to bring in users with an
:include, but I don''t see how to bring in groups.

There are lots of items so I want to be able to page through them using
paginator. I was under the impression that you could not pass arbitrary sql
to paginator.

I may submit this as a bug. To me, the logical way to deal with this is to
pass a :join and an :order to the find. But a :join without a :select breaks
because there are *in most cases* two id fields, and the model objects get
assigned the last id which is the wrong id. I think it should do the
opposite: take the first id, and discard later ids. If the connector did
this then the :join would work without a :select.

Any other ideas? Like I said, I''m in good shape with the :select.
Thanks for
your help.

-kdf


On 12/20/05, Brian V. Hughes
<brianvh-ilmOVS5JQ6Xj7r8U7pfrKh2eb7JE58TQ@public.gmane.org>
wrote:
>
> Kelly Dwight Felkins wrote:
> > This is an active record model in a belongs_to relationship.  The
> > convention is to have a primary key named ''id''.
I''m looking for a
> > solution that works without breaking that convention.
>
> If it''s a belongs_to relationship, then you don''t want a
:joins parameter
> at
> all. What you want to use is :include.
>
> In fact, the only reason to do the :joins method is if you deliberately
> want to
> exclude columns from the belongs_to table in the returned object/list of
> objects. Is that what you are trying to do?
>
> -Brian
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails