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 -- Posted via http://www.ruby-forum.com/.
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
--
Posted via http://www.ruby-forum.com/.
Julian Gall 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 -- Posted via http://www.ruby-forum.com/.
> From: Julian Gall <julian.gall@gmail.com> > Subject: [Rails] Re: sql question for rails > To: rails@lists.rubyonrails.org > Message-ID: <08d805ec7d66f027dd21d5348318e02f@ruby-forum.com> > Content-Type: text/plain; charset=utf-8 > > Julian Gall 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)The above statement though makes your application vulnerable to sql injection attacks, so be careful about that. Using ''?'' sanitizes the string thus keeping it safe. Bharat
Julian Gall wrote:> Julian Gall 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) > > JulianThanks! 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. -- Posted via http://www.ruby-forum.com/.
((..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) -- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/.
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 -- Posted via http://www.ruby-forum.com/.