Topic: Array Question

I have an array that contains a dynamic number of ints from a checkbox query. I'm still trying to figure out how to take each key and add " AND " between them to use them in an AND query. That is, if there is a list of checkboxes, I can do a

find(:all, :conditions=>{'pid=?', newarray})

and it will chain the checkbox values "19 AND 20 AND 24" and will only return the exact match

Any help would be much appreciated

Re: Array Question

To join em this way you can call newarray.join(' AND '), but it wont work in a query as you expect, there's no such syntax in SQL. But you can do:

  find(:all, :conditions => ['pid in (?)', newarray])

Re: Array Question

bluesman.alex wrote:

To join em this way you can call newarray.join(' AND '), but it wont work in a query as you expect, there's no such syntax in SQL. But you can do:

  find(:all, :conditions => ['pid in (?)', newarray])

The problem with not joining them is this:

If I have 2 users and 2 checkboxes and user 1 has 1 checkbox associated with him and the 2nd has both. If I check the one both are associated with, they both come up, if I check the one that only the 2nd is associated with, the 2nd comes up, if I check both, they both come up and the one that's associated with both comes up twice. I need to make it so it checks for a user that matches the EXACT criteria.

Is this possible?

Re: Array Question

Ohh, I think I got it. You want users for which you provided id multiple times to show up in resultset multiple times, right?

Seems like sql can't do it in one query. And hence activerecord can't do it. You'll have to do a separate find call for each checkbox and then merge results yourself.

update
Actually doing multiple queries is a bad idea because of performance reasons. The better way would be to make one query and then change the results according to the array of ids that you have.

Last edited by bluesman.alex (2010-07-23 01:37:10)

Re: Array Question

This is actually the opposite of what I'm trying to do.

For instance, let's say we have a table with a list of skills. We then have a pivot table with skill_id and user_id. Multiple users maybe have the same skill. Now, I want to do a query for a certain skillset; however, I want it to search for the exact skills and nothing more, that is, let's say Bob has the skill Ruby and Joe has skills Ruby and PHP. If a user searched for Ruby, both would come up. If a user searched for Ruby and PHP, Joe would come up, and if a user search for Ruby, PHP, and Java, no one would come up.

Currently, if a user searches for Ruby and PHP, Bob comes up once and Joe comes up twice.

Does that make sense?

Last edited by OmriShiv (2010-07-23 02:58:18)

Re: Array Question

I have now added a :group parameter and it's getting rid of the duplicates; however, it is still not doing an AND query, rather an IN query. Is there a way to change it?

@query = Process.find_all_by_pid(params[:query], :group=>:user_id)