Fragment using dynamic table/column names in exists(...)

Questions / Help
1

We have a table of photos that links to more than 30 other tables. We’re trying to build a filter against these based on the associations. For example, if we’re given address ids 1, 2, and 3, we want to list all photos with an association with any one of those addresses:

ids = [1, 2, 3]
Photo
|> where([p], fragment("exists (select 1 from addresses_photos where photo_id = ? and address_id = any(?))", p.id, ^ids)

However, we need similar logic for lots of other join tables and I’m trying to avoid copying/pasting similar code 30 times.

I tried interpolating the table/column names (which gives a “to prevent SQL injection attacks…” error even though I’m using my own sanitized strings and not user input) and passing in the table/column names as arguments in fragment/1 (which quotes all of the names creating a broken query). I’ve also tried every type of join I could think of but they always end up returning duplicate photo rows (e.g. if a photo was linked to two addresses, it would be returned twice).

unsafe_fragment/1 looked promising, but it got pulled from Ecto. Is there something I’m missing that would let me do this?

2

From both a SQL efficiency perspective and a brain-meat perspective, this seems rather inefficient? Why not just conditionally join to the rows and then then just group by the full selection?

This is the right way to do it as I hinted above, but you need to use group_by to have it ignore the multiple joins and just return the data you need. The most simple way (since it sounds like you don’t actually need the association data at all) is just put the same return fields for both select and group_by. :slight_smile: