CODEWITHSUNDEEP
sqlruby-on-railspostgresqlactiverecordwhere-in
rurabe
rurabe

Reputation: 447

Make an IN statement using two attributes in Activerecord

I've been trying this for a while, and can't seem to get it right in Activerecord.

Given an array of asset_id and asset_type pairs, query a class that has both those attributes, only where both asset_id and asset_type match.

So given the array

[[4,"Logo"],[1,"Image"]]

I want to generate the SQL

SELECT "asset_attachments".* FROM "asset_attachments" WHERE ((asset_id,asset_type) IN ((4,'Logo'),(1,'Image')))

I can do this by manually entering a string using where like this:

AssetAttachment.where("(asset_id,asset_type) IN ((4,'Logo'),(1,'Image'))")

But I'm trying to use it with an array of any length and asset type/id.

So far I've tried

AssetAttachment.where([:asset_id, :asset_type] => [[4,"Logo"],[1,"Image"]])

NoMethodError: undefined method `to_sym' for [:asset_id, :asset_type]:Array

and

AssetAttachment.where("(asset_id,asset_type)" => [[4,"Logo"],[1,"Image"]])

ActiveRecord::StatementInvalid: PG::Error: ERROR: column asset_attachments.(asset_id,asset_type) does not exist

and

AssetAttachment.where("(asset_id,asset_type) IN (?,?)",[[4,"Logo"],[1,"Image"]])

ActiveRecord::PreparedStatementInvalid: wrong number of bind variables (1 for 2) in: (asset_id,asset_type) IN (?,?)

Does anyone know how to do this? Thanks in advance

Upvotes: 1

Views: 208

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656331

set vs. array

The core of the problem is: you are mixing sets and arrays in an impossible way.

elem IN (...) .. expects a set.
elem = ANY(...) .. expects an array.

You can use unnest() to transform an array to a set.
You can use the aggregate function array_agg() to transform a set to an array.

Errors

Here, you are trying to form an array from (asset_id, asset_type):

AssetAttachment.where([:asset_id, :asset_type] => [[4,"Logo"],[1,"Image"]])

.. which is impossible, since arrays have to consist of identical types, while we obviously deal with a numeric and a string constant (you kept the actual types a secret).

Here, you force "(asset_id, asset_type)" as single column name by double-quoting it:

AssetAttachment.where("(asset_id,asset_type)" => [[4,"Logo"],[1,"Image"]])

And finally, here you try provide a single bind variable for two ?:

AssetAttachment.where("(asset_id,asset_type) IN (?,?)",[[4,"Logo"],[1,"Image"]])

Valid SQL

In pure SQL, either of these work:

SELECT * FROM asset_attachments
WHERE  (asset_id, asset_type) IN ((4, 'Logo'), (1, 'Image'));

SELECT * FROM asset_attachments
WHERE  (asset_id, asset_type) IN (VALUES(4, 'Logo'), (1, 'Image'));

SELECT * FROM asset_attachments
WHERE  (asset_id, asset_type) = ANY (ARRAY[(4, 'Logo'), (1, 'Image')]);

If you have a long list of possible matches, an explicit JOIN would prove faster:

SELECT *
FROM   asset_attachments
JOIN   (VALUES(4, 'Logo'), (1, 'Image')) AS v(asset_id, asset_type)
                                       USING (asset_id, asset_type)

Valid syntax for AR

I am an expert with Postgres, with AR not so much. This simple form might work:

AssetAttachment.where("(asset_id,asset_type) IN ((?,?),(?,?))", 4,"Logo",1,"Image")

Not sure if this could work, not sure about single or double quotes either:

AssetAttachment.where((:asset_id, :asset_type) => [(4,'Logo'),(1,'Image')])

Upvotes: 1

Related Questions