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,
@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
On 6/16/06, Julian G. [email protected] 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.
Julian
I guess I’ll have to build the query by hand… 
regards,
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
On 6/16/06, Julian G. [email protected] wrote:
:select => “ru.id as id, name, vehiculo_id”
thanks!
:select => “ru.*” did the trick.
