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

Rails
1

I’m having trouble discovering the correct syntax for passing an array
of data-record-ids to a find (or paginate – same thing, right? :wink:
: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!

2

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.

3

William P. wrote:

Try this:

:conditions => [‘WHERE bug_id IN (?)’, :bug_id_list],

Ok, that’s ONE syntax error… :slight_smile:

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
4

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.

5

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.

6

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!

7

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.

8

On 9/28/07, William P. [email protected] 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]],

9

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 :slight_smile:

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.

10

On 9/28/07, Olie D. [email protected] 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)

11

Bob S. wrote:

On 9/28/07, William P. [email protected] 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!

12

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.