Search code examples
subqueryelixirectoexists

Ecto: subquery & exists

I would like to add a flag to my model when an association exists in the table. In my User model I have the following field: field :is_employed, :boolean, virtual: true, default: false

Can I do it by combining select_merge and exists?

User
|> ...
|> select_merge([u], %{is_employed: Repo.exists(from t in Task, where: u.id == t.user_id and t.status == "in_progress")})
|> Repo.all()
Solution 
  • User
|> join(:left, [u], t in Task, on: u.id == t.user_id and t.status == "in_progress")
|> select_merge([u, t], %{is_employed: not is_nil(t) })
|> Repo.all()

    One way to solve this is by having join of data that you could use directly further to query appropriate data.