select all records by array of parameters

Question

I receive an array of parameters from POST request, they look like the following:

    Parameters: 
     {"search"=>
      { "minor"=>["1", "1"], "major"=>["1", "2"], "proximity_uuid"=>
        ["12345453453", "12345453453"]
      }
    }

which means I am requesting two items:

One with params:

minor: 1

major: 1

prox_uuid: 12345453453

and the second with:

minor: 1

major: 2

prox_uuid: 12345453453

As you may guess, I need to select items from DB that match exact these conditions.

I use the following query:

selectbeacon = Beacon.includes(:ads, :venue).where('minor IN (?) 
and major IN (?) and proximity_uuid IN (?)', params[:search][:minor], 
params[:search][:major], params[:search][:proximity_uuid])

However, the issue is that it selects all records that match at least one condition, but I need to match all three.

As the end result, I am generating complex JSON response.

How should I tweak my query?

thank you in advance,

Roman

Recent related question: http://stackoverflow.com/questions/28589773/left-outer-join-on-array-column-with-multiple-values/28598658 — Erwin Brandstetter, Feb 22 '15 at 01:37

Arup Rakshit · Answer 1 · 2015-02-21T11:19:22.847

set1 = [:minor, :major, :proximity_uuid].map { |i| params[:search][i][0] }
set2 = [:minor, :major, :proximity_uuid].map { |i| params[:search][i][-1] }

query

Beacon.includes(:ads, :venue)
      .where("(minor = ? and major = ? and proximity_uuid = ?) or
              (minor = ? and major = ? and proximity_uuid = ?)",
               *set1, *set2       
             )

After playing sometime in my console, I found another way to write the above query:

Beacon.includes(:ads, :venue)
      .where("(minor, major, proximity_uuid) 
             IN (values(?,?,?),(?,?,?))", *set1, *set2
            )

Tested in console :

Loading development environment (Rails 4.1.1)
[1] pry(main)> Pet.all
  Pet Load (0.7ms)  SELECT "pets".* FROM "pets"
=> [#<Pet id: 6, animals: 1, created_at: "2015-02-21 10:40:19", updated_at: "2015-02-21 10:40:19", name: "Tilly">,
 #<Pet id: 7, animals: 0, created_at: "2015-02-21 10:40:31", updated_at: "2015-02-21 10:40:54", name: "Xav">,
 #<Pet id: 5, animals: 1, created_at: "2015-02-19 18:27:28", updated_at: "2015-02-21 10:41:06", name: "Mua">]
[2] pry(main)> Pet.where("(id, animals) IN (values(?,?),(?,?))",6,1,5,1)
  Pet Load (1.3ms)  SELECT "pets".* FROM "pets"  WHERE ((id, animals) IN (values(6,1),(5,1)))
=> [#<Pet id: 6, animals: 1, created_at: "2015-02-21 10:40:19", updated_at: "2015-02-21 10:40:19", name: "Tilly">,
 #<Pet id: 5, animals: 1, created_at: "2015-02-19 18:27:28", updated_at: "2015-02-21 10:41:06", name: "Mua">]

thanks Arup! I may receive 1..N sets from POST request. I understand how to map them to corresponding sets, but how should I build query then? — Roman Krayovskyy, Feb 21 '15 at 10:11
@RomanKrayovskyy Look at my modified code.. and let me know if you didn't understand. — Arup Rakshit, Feb 21 '15 at 11:21
Hey Arup, yeah it looks great! How can I make it work at unknown amount of sets? It can be 1 or 10, I do not know, they are generated dynamically by third party. Any ideas? Somehow process them in the loop? — Roman Krayovskyy, Feb 21 '15 at 11:40
No. That decision you have to make.. What I wrote is what I can think of. Anything better I don't know. — Arup Rakshit, Feb 21 '15 at 11:46

score 0 · Accepted Answer · answered Feb 24 '15 at 19:29

After playing with it for a while and with great help of my friend, here is the solution:

POST parameters should look like the following:

search[0][minor] = 1
search[0][major] = 1
search[0][proximity_uuid] = 1111

...

and query should be:

result = params["search"].inject([]) { | result, (key, item) | result | Beacon.includes(:ads, :venue).where('minor=? and major=? and proximity_uuid=?', item["minor"], item["major"], item["proximity_uuid"]) }

select all records by array of parameters

2 Answers2

query