# \[N00b\] find/paginate with :condition values in an array

**URL:** <https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825>\
**Category:** Rails\
**Created:** [September 28, 2007, 8:49pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825 "2007-09-28T20:49:34Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 8:49pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/1 "2007-09-28T20:49:34Z")

</div>

I’m having trouble discovering the correct syntax for passing an array  
of data-record-ids to a find (or paginate – same thing, right? 😉  
:condition clause in order to create an array of all the records with  
the IDs in the passed-in array.

In my .rhtml, I have:

\<%= link\_to @pa\_bugs.length.to\_s, { :action =\> ‘list\_by\_ids’,  
:bug\_id\_list =\> @bug\_nums }, :title =\> @bug\_nums.join(", ") %\>

This works good, gives me a link like _3_, calls my list\_by\_ids method,  
and even has a cool URL-title (mouseover tool-tip) with a list of the  
bugs, vis: “2017, 2023, 2104”. So far, so good.

In my controller.rb, I have

```
def list_by_ids
    @bug_pages, @bugs = paginate :bugs,
        :conditions => ['WHERE bug_id IN ?', :bug_id_list],
        :per_page => 25,
        :order_by => 'priority, bug_severity, bug_id'

    render :action => 'list'
end

```

It seems that, in the conditions line, my use of bug\_id\_list isn’t doing  
what I think it should. I’ve tried a few variations (like  
:param[“bug\_id\_list”], etc.) but, clearly, I don’t understand the  
correct syntax here.

Can I get a hint?

For extra-credit, can you explain the concepts behind how data is passed  
from .rhtml to controller.rb, so I an figure out the similar problem,  
next time?

Thanks!

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 9:00pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/2 "2007-09-28T21:00:53Z")

</div>

Try this:

:conditions =\> [‘WHERE bug\_id IN (?)’, :bug\_id\_list],

Olie D. wrote:

> This works good, gives me a link like _3_, calls my list\_by\_ids method,
> 
> For extra-credit, can you explain the concepts behind how data is passed  
> from .rhtml to controller.rb, so I an figure out the similar problem,  
> next time?
> 
> Thanks!

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 9:29pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/3 "2007-09-28T21:29:04Z")

</div>

William P. wrote:

> Try this:
> 
> :conditions =\> [‘WHERE bug\_id IN (?)’, :bug\_id\_list],

Ok, that’s ONE syntax error… 🙂

Mysql::Error: #42000You have an error in your SQL syntax; check the  
manual that corresponds to your MySQL server version for the right  
syntax to use near ‘WHERE bug\_id IN (’— :bug\_id\_list’))’ at line 1:  
SELECT count(\*) AS count\_all FROM bugs WHERE (WHERE bug\_id IN (’—  
:bug\_id\_list’))

It looks like… hmmm… I’m not sure WHAT it looks like! NOTE: my use  
of :title =\> @bug\_nums.join(", ") results in a title similar to “2019,  
2103, 2239”, which leads me to believe that @bug\_nums is a well-formed  
array of numbers.

Ooo! Do I have to do something fancy to make :bug\_id\_list convert to  
string? Am I getting close?

Thanks again!

For reference, foo.rhtml (exerpt):

\<%= link\_to @pa\_bugs.length.to\_s, { :action =\> ‘list\_by\_ids’,  
:bug\_id\_list =\> @bug\_nums }, :title =\> @bug\_nums.join(", ") %\>

[new, improvied] foo\_controller.rb (exerpt):

```
def list_by_ids
    @bug_pages, @bugs = paginate :bugs,
        :conditions => ['WHERE bug_id IN (?)', :bug_id_list],
        :per_page => 25,
        :order_by => 'priority, bug_severity, bug_id'

    render :action => 'list'
end
```

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 9:58pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/4 "2007-09-28T21:58:20Z")

</div>

Btw, you can look at your params array at the top of the request in  
development.log. You could also look at the particular key in question  
like this:

logger.debug "params[:bug\_id\_list] = " + params[:bug\_id\_list].inspect

William P. wrote:

> > William P. wrote:  
> > manual that corresponds to your MySQL server version for the right  
> > string? Am I getting close?
> 
> –  
> Sincerely,
> 
> William P.
> 
> >

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 9:45pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/5 "2007-09-28T21:45:27Z")

</div>

The problem is that you are passing a symbol to conditions not the  
array. The problem is with your link\_to and the array. You need to look  
at params and see what params[:bug\_id\_list] is. I should have looked at  
the code I sent you more carefully. If params[:bug\_id\_list] is indeed an  
array, this will work:

:conditions =\> [‘WHERE bug\_id IN (?)’, params[:bug\_id\_list]],

Olie D. wrote:

> Mysql::Error: #42000You have an error in your SQL syntax; check the  
> Ooo! Do I have to do something fancy to make :bug\_id\_list convert to  
> [new, improvied] foo\_controller.rb (exerpt):
> 
> ```
> def list_by_ids
> @bug_pages, @bugs = paginate :bugs,
> :conditions => ['WHERE bug_id IN (?)', :bug_id_list],
> :per_page => 25,
> :order_by => 'priority, bug_severity, bug_id'
> 
> render :action => 'list'
> end
> 
> ```

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 10:21pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/6 "2007-09-28T22:21:40Z")

</div>

William P. wrote:

> The problem is that you are passing a symbol to conditions not the  
> array. The problem is with your link\_to and the array. You need to look  
> at params and see what params[:bug\_id\_list] is. I should have looked at  
> the code I sent you more carefully. If params[:bug\_id\_list] is indeed an  
> array, this will work:
> 
> :conditions =\> [‘WHERE bug\_id IN (?)’, params[:bug\_id\_list]],

Well, there’s good news & less-good news…

Good news:  
params[:bug\_id\_list] does, indeed, contain my list of bug\_ids.

Less-good news:  
Mysql::Error: #42000You have an error in your SQL syntax; check the  
manual that corresponds to your MySQL server version for the right  
syntax to use near ‘WHERE bug\_id IN (‘20021/20147/20270’))’ at line 1:  
SELECT count(\*) AS count\_all FROM bugs WHERE (WHERE bug\_id IN  
(‘20021/20147/20270’))

So, it seems that my conditions to mysql aren’t quite what mysql is  
wanting. \<Google…\> Oh dear – it seems a common problem that mysql’s  
where-condition is not syntactically defined, so I guess I’ll have to  
experiment with it, some.

I tried variations on @bug\_id\_list.join() to give ‘20021, 20147, 20270’  
and ‘20021 20147 20270’, but same error. I guess I’ll fiddle with it,  
some.

Thanks for your help!

> logger.debug "params[:bug\_id\_list] = " + params[:bug\_id\_list].inspect

Btw, this was a VERY helpful tidbit!

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 10:27pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/7 "2007-09-28T22:27:46Z")

</div>

Something is not right here, because this does work. What do you see in  
your logs if you try this?

logger.debug "params[:bug\_id\_list].class = " +  
params[:bug\_id\_list].class

I’m guessing that you are not getting an array because if were getting  
an array of numbers, it would output them as a comma delimited string.  
If this is a String separated by /'s, you could do:

:conditions =\> [‘WHERE bug\_id IN (?)’, params[:bug\_id\_list].split(’/’]),

I have never tried to pass an array in a link in link\_to like you are  
doing so I’m not sure what the result is. Maybe you should rethink the  
way you are sending your array?

Olie D. wrote:

> (‘20021/20147/20270’))  
> Thanks for your help!
> 
> > logger.debug "params[:bug\_id\_list] = " + params[:bug\_id\_list].inspect
> 
> Btw, this was a VERY helpful tidbit!

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 10:34pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/8 "2007-09-28T22:34:53Z")

</div>

On 9/28/07, William P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> The problem is that you are passing a symbol to conditions not the array.  
> The problem is with your link\_to and the array. You need to look at params  
> and see what params[:bug\_id\_list] is. I should have looked at the code I  
> sent you more carefully. If params[:bug\_id\_list] is indeed an array, this  
> will work:
> 
> :conditions =\> [‘WHERE bug\_id IN (?)’, params[:bug\_id\_list]],

You don’t put “WHERE” in the conditions. AR takes care of that for you.

:conditions =\> [‘bug\_id IN (?)’, params[:bug\_id\_list]],

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 10:37pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/9 "2007-09-28T22:37:14Z")

</div>

wow, I completely missed that…that would definitely do it. thats what  
I get for answering posts and trying to work at the same time 🙂

Bob S. wrote:

> You don’t put “WHERE” in the conditions. AR takes care of that for you.
> 
> :conditions =\> [‘bug\_id IN (?)’, params[:bug\_id\_list]],
> 
> >

–  
Sincerely,

William P.

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 10:40pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/10 "2007-09-28T22:40:09Z")

</div>

On 9/28/07, Olie D. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> For extra-credit, can you explain the concepts behind how data is passed  
> from .rhtml to controller.rb, so I an figure out the similar problem,  
> next time?

They are passed in the params hash (params is a method of the  
controller instance; see the API docs for ActionController::Base)

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 11:58pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/11 "2007-09-28T23:58:33Z")

</div>

Bob S. wrote:

> On 9/28/07, William P. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:
> 
> > The problem is that you are passing a symbol to conditions not the array.  
> > The problem is with your link\_to and the array. You need to look at params  
> > and see what params[:bug\_id\_list] is. I should have looked at the code I  
> > sent you more carefully. If params[:bug\_id\_list] is indeed an array, this  
> > will work:
> > 
> > :conditions =\> [‘WHERE bug\_id IN (?)’, params[:bug\_id\_list]],

> You don’t put “WHERE” in the conditions. AR takes care of that for you.
> 
> :conditions =\> [‘bug\_id IN (?)’, params[:bug\_id\_list]],

Combining these two, it now all works.

Thanks very much to both! And I’ll go read-up on  
ActionController::Base. One of the difficulties for Rails-n00bs is that  
there’s SO MUCH, it’s hard to know what all to read first!

---

<div class="post-metadata">

**Author:** ![olie](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/c4cdca/32.png) [@olie](https://www.ruby-forum.com/u/olie)\
**Post date:** [September 28, 2007, 10:32pm UTC](https://www.ruby-forum.com/t/n00b-find-paginate-with-condition-values-in-an-array/115825/12 "2007-09-28T22:32:30Z")

</div>

I just wrote a simple test to emulate what you are doing and I got the  
correct result. I’m not sure whats going on in your case. Can you show  
the output of the inspect on the params[:bug\_id\_list].inspect ?

William P. wrote:

> :conditions =\> [‘WHERE bug\_id IN (?)’, params[:bug\_id\_list].split(’/’]),
> 
> > > the code I sent you more carefully. If params[:bug\_id\_list] is indeed an  
> > > Less-good news:
> > 
> > Btw, this was a VERY helpful tidbit!
> 
> –  
> Sincerely,
> 
> William P.
> 
> >

–  
Sincerely,

William P.
