# Ruby sql question

**URL:** <https://www.ruby-forum.com/t/ruby-sql-question/177291>\
**Category:** Ruby\
**Created:** [October 14, 2009, 4:24pm UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291 "2009-10-14T16:24:40Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 14, 2009, 4:24pm UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/1 "2009-10-14T16:24:40Z")

</div>

Hi,

Im using ActiveRecord to find something.

\<% @new = User.find\_by\_sql(“SELECT \* FROM users WHERE users.id NOT IN  
(array)”) %\>

Now, i can’t seem to make it look through the array. I had a look for  
this problem in other languages and in php its simply $array.

---

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 14, 2009, 4:34pm UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/2 "2009-10-14T16:34:37Z")

</div>

On Wed, Oct 14, 2009 at 4:24 PM, Manish S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> Hi,
> 
> Im using ActiveRecord to find something.
> 
> \<% @new = User.find\_by\_sql(“SELECT \* FROM users WHERE users.id NOT IN  
> (array)”) %\>
> 
> Now, i can’t seem to make it look through the array. I had a look for  
> this problem in other languages and in php its simply $array.

Perhaps:  
“SELECT \* FROM users WHERE users.id NOT IN (#{array.join(', ')})”

---

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 14, 2009, 4:44pm UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/3 "2009-10-14T16:44:18Z")

</div>

Leslie V. wrote:

> On Wed, Oct 14, 2009 at 4:24 PM, Manish S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
> wrote:
> 
> > Hi,
> > 
> > Im using ActiveRecord to find something.
> > 
> > \<% @new = User.find\_by\_sql(“SELECT \* FROM users WHERE users.id NOT IN  
> > (array)”) %\>
> > 
> > Now, i can’t seem to make it look through the array. I had a look for  
> > this problem in other languages and in php its simply $array.
> 
> Perhaps:  
> “SELECT \* FROM users WHERE users.id NOT IN (#{array.join(', ')})”

Thank you Leslie. That worked fine.

---

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 14, 2009, 5:10pm UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/4 "2009-10-14T17:10:56Z")

</div>

On Wed, Oct 14, 2009 at 9:47 AM, Manish S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
wrote:

> > > Now, i can’t seem to make it look through the array. I had a look for  
> > > this problem in other languages and in php its simply $array.
> > 
> > Perhaps:  
> > “SELECT \* FROM users WHERE users.id NOT IN (#{array.join(', ')})”
> 
> ## Just an extra question though. what does the array.join(‘,’) do?
> 
> Posted via [http://www.ruby-forum.com/](http://www.ruby-forum.com/).

[http://www.ruby-doc.org/core/classes/Array.html#M002182](http://www.ruby-doc.org/core/classes/Array.html#M002182)

Returns a string created by converting  
each[http://www.ruby-doc.org/core/classes/Array.html#M002173](http://www.ruby-doc.org/core/classes/Array.html#M002173)element  
of the array to a string, separated by  
_sep_.

[“a”, “b”, “c”].join #=\> “abc”  
[“a”, “b”, “c”].join(“-”) #=\> “a-b-c”

Also, your use of erb and ActiveRecord implies a MVC structure. If this  
is  
the case, you should be assigning instance variables in your controller  
and  
not your view. You should not have find statements or assignments in  
your  
view, it should only be for formatting output.

---

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 14, 2009, 7:31pm UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/5 "2009-10-14T19:31:28Z")

</div>

Just play around with it in IRB:

irb(main):002:0\> [‘a’, ‘b’, ‘c’].join(’,’)  
=\> “a,b,c”

I think that when PHP converts $array to a string, it puts commas in  
between each element, which is just lucky for the purposes of  
producing a list that the SQL statement would like.

This is what happens when Ruby converts an array to a string:  
irb(main):003:0\> [‘a’, ‘b’, ‘c’].to\_s  
=\> “abc”

So to put in the commas like PHP does it (and SQL likes it), use join.

---

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 20, 2009, 7:58am UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/6 "2009-10-20T07:58:14Z")

</div>

On Wed, 14 Oct 2009, Manish S. wrote:

> Hi,
> 
> Im using ActiveRecord to find something.
> 
> \<% @new = User.find\_by\_sql(“SELECT \* FROM users WHERE users.id NOT IN  
> (array)”) %\>

With ActiveRecord you can use:

```
 User.all(:conditions => ["users.id NOT IN (?)", array])
```

---

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 20, 2009, 9:02am UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/7 "2009-10-20T09:02:03Z")

</div>

Matt Haley wrote:

> With ActiveRecord you can use:
> 
> ```
> User.all(:conditions => ["users.id NOT IN (?)", array])
> 
> ```

Sweet, just learning ActiveRecords, but was using .find( :all … )  
I like this!

> 

–  
Kind Regards,  
Rajinder Y.

[http://DevMentor.org](http://DevMentor.org)  
Do Good ~ Share Freely

---

<div class="post-metadata">

**Author:** ![mshah](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/m/ee7513/32.png) [@mshah](https://www.ruby-forum.com/u/mshah)\
**Post date:** [October 14, 2009, 4:47pm UTC](https://www.ruby-forum.com/t/ruby-sql-question/177291/8 "2009-10-14T16:47:48Z")

</div>

Leslie V. wrote:

> On Wed, Oct 14, 2009 at 4:24 PM, Manish S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)  
> wrote:
> 
> > Hi,
> > 
> > Im using ActiveRecord to find something.
> > 
> > \<% @new = User.find\_by\_sql(“SELECT \* FROM users WHERE users.id NOT IN  
> > (array)”) %\>
> > 
> > Now, i can’t seem to make it look through the array. I had a look for  
> > this problem in other languages and in php its simply $array.
> 
> Perhaps:  
> “SELECT \* FROM users WHERE users.id NOT IN (#{array.join(', ')})”

Just an extra question though. what does the array.join(‘,’) do?