Dynamic table and field names in fragments?

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.

1 Like

I realize this is likely not the answer you are looking for but if I was you I would just create a stored procedure.

But maybe an Ecto master can help. I haven’t had to use techniques like these so far.