# Sql question for rails **URL:** \ **Category:** Rails\ **Created:** [June 29, 2006, 11:30am UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529 "2006-06-29T11:30:36Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\ **Post date:** [June 29, 2006, 11:30am UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529/1 "2006-06-29T11:30:36Z")
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 ---
**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\ **Post date:** [June 29, 2006, 12:55pm UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529/2 "2006-06-29T12:55:57Z")
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 ---
**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\ **Post date:** [June 29, 2006, 1:20pm UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529/3 "2006-06-29T13:20:33Z")
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 ---
**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\ **Post date:** [June 29, 2006, 6:25pm UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529/4 "2006-06-29T18:25:14Z")
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. ---
**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\ **Post date:** [June 30, 2006, 10:03am UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529/5 "2006-06-30T10:03:44Z")
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 ---
**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\ **Post date:** [June 29, 2006, 6:28pm UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529/6 "2006-06-29T18:28:49Z")
((…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) ---
**Author:** ![jeff](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/e19b73/32.png) [@jeff](https://www.ruby-forum.com/u/jeff)\ **Post date:** [June 30, 2006, 1:17pm UTC](https://www.ruby-forum.com/t/sql-question-for-rails/96529/7 "2006-06-30T13:17:20Z")
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](http://api.rubyonrails.com)?) anyway, thank you so much. you’ve been real helpful, jeff