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?