Delete orphaned records with Ecto

Question

I have 3 models:

user.ex

  schema "users" do 
    ...
    many_to_many(:acts, Act, join_through: UserAct)
  end

act.ex

  schema "acts" do
    ...
    many_to_many(:users, User, join_through: UserAct)
  end

user_act.ex

  schema "users_acts" do
    belongs_to :user, User
    belongs_to :act, Act
  end

Each time I delete UserAct I want to check if there is orphaned Act models and delete them, in the transaction.

In SQL it looks like this

DELETE FROM acts WHERE NOT EXISTS (
  SELECT 1 FROM users_acts ua WHERE ua.act_id = acts.id
);

or 

DELETE FROM acts WHERE id NOT IN (SELECT act_id FROM users_acts);

My question is how to write a similar query with Ecto?

Please show all the methods you know: joins, fragments, etc...

Answer 1

One solution is to use fragments.

Repo.delete_all(
  from a in Act,
  where: fragment("? NOT IN (SELECT act_id FROM users_acts)", a.id)
)