How to create Rails query that (I think) requires PostgreSQL subquery?

Question

Not sure how to do this so title may not be correct.

Each User has a field country of type String.

Given an array of user_id, country tuples for the query, find all the records that match. Each User must be found with it's own country.

For example, here is the array of tuples.

[1, 'us'],
[2, 'mexico'],
[3, 'us']

This would return User 1 if it exists and its country is 'us'.
It should also return User 2 if it exists and its country is 'mexico'.

The query should return all matching results.

Rails 4.2

max · Accepted Answer · 2020-01-07 10:54:43Z

class User < ApplicationRecord
  def self.query_from_tuples(array_of_tuples)
    array_of_tuples.inject(nil) do |scope, (id, country)|
      if scope
        scope.or(where(id: id, country: country))
      else
        where(id: id, country: country) # this handles the initial iteration
      end
    end
  end
end

The resulting query is:

SELECT "users".* FROM "users" 
WHERE (("users"."id" = $1 AND "users"."country" = $2 OR "users"."id" = $3 AND "users"."country" = $4) OR "users"."id" = $5 AND "users"."country" = $6) 
LIMIT $7

You could also adapt kamakazis WHERE (columns) IN (values) query by:

class User < ApplicationRecord
  def self.query_from_tuples_2(array_of_tuples)
    # just a string of (?,?) SQL placeholders for the tuple values
    placeholders = Array.new(array_of_tuples.length, '(?,?)').join(',')
    # * is the splat operator and turns the tuples (flattened) into
    # a list of arguments used to fill the placeholders
    self.where("(id, country) IN (#{placeholders})", *array_of_tuples.flatten)
  end
end

Which results in the following query which is a lot less verbose:

SELECT "users".* FROM "users" 
WHERE ((id, country) IN ((1,'us'),(2,'mexico'),(3,'us'))) LIMIT $1

And can also perform much better if you have a compound index on [id, country].

karmakaze · Accepted Answer · 2019-12-30 23:23:25Z

1

I know this would work in pure SQL: e.g.

SELECT * FROM user
WHERE (id, country) IN ((1, 'us'), (2, 'mexico'), (3, 'us'))

Now I don't know how Rails would handle the bind parameter if it was a list of pairs (list of two elements each). Perhaps that would work.

answered Dec 30, 2019 at 23:23

karmakaze

36.3k1 gold badge34 silver badges33 bronze badges

Comments

Akshay Goyal · Accepted Answer · 2019-12-31 05:23:14Z

1

You can construct a raw sql and use active record. Something like this:

def self.for_multiple_lp(arr=[])
  # Handle case when arr is not in the expected format.
  condition = arr.collect{|a| "(user_id = #{a[0]} AND country = #{a[1]})"}.join(" OR ")
  where(condition)
end

Edit: Improved Solution

def self.for_multiple_lp(arr=[])
  # Handle case when arr is not in the expected format.
  condition = arr.collect{|a| "(user_id = ? AND country = ?)"}.join(" OR ")
  where(condition, *(arr.flatten))
end

This should work.

answered Dec 31, 2019 at 5:23

Akshay Goyal

9255 silver badges14 bronze badges

1 Comment

B Seven Over a year ago

I think this should actually work. Thank you! You may want to remove the first answer because it allows for SQL injection.

Collectives™ on Stack Overflow

How to create Rails query that (I think) requires PostgreSQL subquery?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related