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: stackoverflow.com/questions/28589773/… — Erwin Brandstetter
– Erwin Brandstetter, Commented Feb 22, 2015 at 1:37

Arup Rakshit · Accepted Answer · 2015-02-21 11:19:22Z

1

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">]

edited Feb 21, 2015 at 11:19

answered Feb 21, 2015 at 9:11

Arup Rakshit

119k30 gold badges270 silver badges328 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Roman Krayovskyy Over a year ago

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?

Arup Rakshit Over a year ago

@RomanKrayovskyy Look at my modified code.. and let me know if you didn't understand.

Roman Krayovskyy Over a year ago

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?

Arup Rakshit Over a year ago

No. That decision you have to make.. What I wrote is what I can think of. Anything better I don't know.

Roman Krayovskyy · Accepted Answer · 2015-02-24 19:29:52Z

0

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"]) }

answered Feb 24, 2015 at 19:29

Roman Krayovskyy

11112 bronze badges

Collectives™ on Stack Overflow

select all records by array of parameters

2 Answers 2

query

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

query

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related