Reputation: 472
This is a hardcoded example of what I'm trying to achieve:
SELECT id FROM places
WHERE metadata->'route'='Route 23'
OR metadata->'route'='Route 22'
OR metadata->'region'='Northwest'
OR metadata->'territory'='Territory A';
Metadata column is an hstore column and I'm wanting to build up the WHERE clause dynamically based on another query from a different table. The table could either be:
id | metadata
---------+----------------------------
1647 | "region"=>"Northwest"
1648 | "route"=>"Route 23"
1649 | "route"=>"Route 22"
1650 | "territory"=>"Territory A"
or
id | key | value
----+-------------+-------+---
1 | route | Route 23
2 | route | Route 22
3 | region | Northwest
4 | territory | Territory A
Doesnt really matter, just whatever works to build up that where clause. It could potentially have 1 to n number of OR's in it based on the other query.
Upvotes: 2
Views: 56
Reputation: 472
Ended up with a solution using the 2nd table (distribution table):
id | metadata
---------+----------------------------
1647 | "region"=>"Northwest"
1648 | "route"=>"Route 23"
1649 | "route"=>"Route 22"
1650 | "territory"=>"Territory A"
Used the following join, which the @> sees if the places.metadata contains the distributions.metadata
SELECT places.id, places.metadata
FROM places INNER JOIN distributions
ON places.metadata @> distributions.metadata
WHERE distributions.some_other_column = something;
Upvotes: 1