I’m trying to use table and field names, which are retrieved from the database and can change, in a fragment and can’t seem to figure out how to do it without resorting to writing the raw sql.
I’m trying to form a query like
select * from <query> as q
where not exists (select true from <table> where <table>.<field> = q.id);
Is this possible with Ecto? What I think I need is something along the lines of
from q in query,
where: fragment("not exists (select true from ? where ? = ?)", ^table, field(^table, ^key), q.id)
but can’t figure out a way to get things to work out.
Note: I can make an Ecto query that returns the same results using a left join, but I need to use it for a delete so I can’t left join directly on the query. I can do something like join the query to itself on its primary id and then left join on that, but at that point dropping to the raw sql and doing it directly seems the better option! I’d also like to avoid left joins if possible, so the query planner can better work its magic.