Dynamically created SQL queries in Rails 2.3.11

[elias....@gmail.com]

Hi all,

I'm building an admin interface for a web app, the client wants to be
able to search the users table by some quite advanced queries, like:

"all the users that participated in activity #3 or #4"
, or
"all the users who have connections to user #15"
, and the like,

you know, some of these queries query the `users` table with a
condition on the joint table, and there might be a couple of joins,
as an example of the query for users participating in activities:

SELECT * FROM users
LEFT OUTER JOIN `users_activities` ON (`users`.`id` =
`users_activities`.`user_id`)
LEFT OUTER JOIN `activities` ON (`activities`.`id` =
`users_activities`.`activity_id`)
WHERE (activities.id IN (3,4) )

at the moment I'm doing this by first gathering which joins do I need,
then gathering which conditions need to be met, and then using
ActiveRecord with something like:
joins = []
conditions = []
joins << [:activities] # if activities join is needed for query
conditions << "activities.id IN (3,4)"

then User.find(:all,:join => joins,:conditions => conditions)

I use this to query the database for 4 or 5 different types of queries
(users in activities, users with connections, etc)...

I don't like very much this invention, and I'm wondering if there
would be some nicer/more elegant way to achieve this.

I know that rails 3 has this nice AR SQL algebra library (arel its
called?) that lets you chain conditions, joins and the like (which I
seem to be replicating in my method), but unfortunately I'm using
rails 2.

then I know I could use User.find_all_by_sql and then just use plain
SQL in the query, but I'd like to do it using AR macros...

do anyone have an idea of some plugin to have this done in an elegant
and efficient way ? some gem ? some trick ?

for example, Redmine has this advanced query construction logic that
lets you show Issues selected by (complex)query. I guess that generic
Admin interface plugins like typus might do something similar...

thank you !

Elias

[Victor Castell]

This question reminds me one app I had that did this types of queries/filters.

I used searchlogic https://github.com/binarylogic/searchlogic, it's a nice query plugin, it's not like arel but it helped to improve readability and power for complex queries.

Hope it helps

> --
> Has recibido este mensaje porque estás suscrito al grupo "Barcelona on Rails" de Grupos de Google.
> Para publicar una entrada en este grupo, envía un correo electrónico a barcelon...@googlegroups.com.
> Para anular tu suscripción a este grupo, envía un correo electrónico a barcelonaonrai...@googlegroups.com
> Para tener acceso a más opciones, visita el grupo en http://groups.google.com/group/barcelonaonrails?hl=es.
>

[elias....@gmail.com]

Cojonudo, es lo que buscaba :D

merci !

Elias

On Jul 17, 6:32 pm, Victor Castell <victorco...@gmail.com> wrote:
> This question reminds me one app I had that did this types of queries/filters.  
>

> I used searchlogichttps://github.com/binarylogic/searchlogic, it's a nice query plugin, it's not like arel but it helped to improve readability and power for complex queries.
>
> Hope it helps