Hi all,
I''m having a problem with a join inside a paginate, the code is like
this:
@ruta_pages, @rutas = paginate :rutas,
:conditions => conds_arr,
:joins => "AS ru LEFT JOIN vehiculos AS ve ON ru.vehiculo_id =
ve.id",
:per_page => 10,
:order => "fecha DESC"
The problem with this, is that the query returns the columns of both
tables (vehiculos and rutas), so the id''s of rutas are messed up (with
the ids of vehiculos)... The ideal query would be to make a SELECT
rutas.* instead of a SELECT *.
I don''t want to build the query myself, because the conditions array
is a complex...
Any idea how can I fix this?
thanks a lot,
--
rolando -- [[ knowledge is empty, fill it ]] --
"Tam pro papa quam pro rege bibunt omnes sine lege."
> @ruta_pages, @rutas = paginate :rutas, > :conditions => conds_arr, > :joins => "AS ru LEFT JOIN vehiculos AS ve ON ru.vehiculo_id = > ve.id", > :per_page => 10, > :order => "fecha DESC"Instead of the above, try: @ruta_pages, @rutas = paginate :vehiculos, :conditions => conds_arr, :joins => "AS ve RIGHT JOIN rutas AS ru ON ru.vehiculo_id ve.id", :per_page => 10, :order => "fecha DESC" It seems, if you change the order of the join (RIGHT will give the same results as LEFT when the tables are the other way around), the Rails code will pick up ruta.id for the id field in the @rutas array. The id from vehiculo is present as the foreign key vehiculo_id. Julian -- Posted via http://www.ruby-forum.com/.
On 6/16/06, Julian Gall <julian.gall@gmail.com> wrote:> Instead of the above, try: > > @ruta_pages, @rutas = paginate :vehiculos,it doesn''t work, because that creates objects of class Vehiculo...> It seems, if you change the order of the join (RIGHT will give the same > results as LEFT when the tables are the other way around), the Rails > code will pick up ruta.id for the id field in the @rutas array. The id > from vehiculo is present as the foreign key vehiculo_id. > > JulianI guess I''ll have to build the query by hand... :-( regards, -- rolando -- [[ knowledge is empty, fill it ]] -- "Tam pro papa quam pro rege bibunt omnes sine lege."
Sorry that didn''t work. I was basing my solution on .find rather than .paginate. I think you can acheive what you want by adding the :select option to the paginate call. e.g. :select => "ru.id as id, name, vehiculo_id" This passes the exact string that will be used in the SELECT statement. In this example, the id from the rutas table will be returned as id. You will need to include all the Ruta model fields that you will be using. Julian -- Posted via http://www.ruby-forum.com/.
On 6/16/06, Julian Gall <julian.gall@gmail.com> wrote:> :select => "ru.id as id, name, vehiculo_id"thanks! :select => "ru.*" did the trick. :-D -- rolando -- [[ knowledge is empty, fill it ]] -- "Tam pro papa quam pro rege bibunt omnes sine lege."
