Sql question for rails

Hi,

i was hoping someone could help me out with a small yet quite
time-boggling task…i have a two tables users, groups joined in as a
habtam relashinship (has and belongs to many) and i need to select from
the mydb (mysql if it matters) users out of the User model, in
accordance with TWO groups in one rails opperation to improve
performance…basicly, say i have ten groups and ten users, and i wanted
to take out seven(or whatever) of the ten users that are associated with
two groups(1 and 2) in one select operation.
i don’t know if this is possible but this is kind of what i was
thinking;

@users_associated_with_group1_and_group2 = User.find(:all, :include =>
‘groups’, :group => ‘1 and 2’, :conditions => :group)

or something of this sort…i actually need to do this with a couple of
different groups that will dynamicly change with the application(not
just two static groups)

is this possible?
much thanks ahead of time,

jeff

Without knowing any more details, it seems you need something like this:

groups = [1, 3].join(",")
User.find(:all, :conditions => [“FIND_IN_SET(group_id, ?) > 0”, groups],
:include => :groups)

This will work with any number of group_ids in an array. The .join puts
them into a string, e.g. “1,3”. The SQL FIND_IN_SET returns 0 if the
record’s group_id is not in the string.

Julian

Julian G. wrote:

User.find(:all, :conditions => [“FIND_IN_SET(group_id, ?) > 0”, groups],
:include => :groups)

Or, better still, code the statement like this:

User.find(:all, :conditions => “group_id IN (#{groups})”, :include =>
:groups)

Julian

Julian G. wrote:

Julian G. wrote:

User.find(:all, :conditions => [“FIND_IN_SET(group_id, ?) > 0”, groups],
:include => :groups)

Or, better still, code the statement like this:

User.find(:all, :conditions => “group_id IN (#{groups})”, :include =>
:groups)

Julian

Thanks! i was burning hours trying silly syntax things, but to no avail
thing is, i now have a different problem, trying to select all of the
users that aren’t associated to any groups at all, and i keep getting an
error becuase the sql that is passed via conditions returns some
sql-syntax error such as

…thanks.

jeff wrote:

how do i manage to select the users that aren’t associated to any group?
if i do :conditions => “group_id = null” the app crashes…

Try “group_id IS NULL”.

Also, regarding Bharat’s comment about the safety of the #{groups}
expression, he is right in general but in this case, I assumed the
variable ‘groups’ would be constructed programmatically and not input by
a user. Bharat is right that you should be careful.

Julian

((…continuation, sorry, :))

SELECT users.`… ON groups.id = groups_users.group_id WHERE ###
(group_id IN ())
how do i manage to select the users that aren’t associated to any group?
if i do :conditions => “group_id = null” the app crashes…

anyway, thanks for your tremendous help (i’ve been burnin some good time
now)

You are the greatest. . .
i was having a little trouble trying to understand how the rails-sql
:condition command works and how it differientiates between an
‘inner/left join’ command and a regular command (i kept getting errors
for “no group_id in user table” etc) until it hit me (i don’t know why
it took so long to hit me, but whatever) the :include did the
trick…obviously i don’t really know enough about all the rails-sql
:include, :conditions, :symbols there are out there…is there a good
place to reference these specific issues? (aside from
api.rubyonrails.com?)

anyway, thank you so much.
you’ve been real helpful,

jeff