Ecto querying another table within the current query

I’m somewhat new to Ecto and was wondering if there is a better way to do this. I have a query:

from p in FileSystem.Schema.Permission, where: p.user_id == ^user_id
                                                                  and not p.is_pending
                                                                  and p.collection_id != ^user_collection_id
                                                                  and (is_nil(p.expiration_time) or p.expiration_time > ^now),
                                                            select: %{default_collection_id: p.collection_id, user_id: p.user_id}

Now I need to make sure that p.collection_id is not present as a field in another table. I can use a fragment like this:

from p in FileSystem.Schema.Permission, where: p.user_id == ^user_id
                                                                  and not p.is_pending
                                                                  and p.collection_id != ^user_collection_id
                                                                  and (is_nil(p.expiration_time) or p.expiration_time > ^now),
                                                            where: fragment("SELECT COUNT(*) FROM user_registry WHERE collection_id = ?", p.collection_id)==0,
                                                            select: %{default_collection_id: p.collection_id, user_id: p.user_id}

Is there a better way other than using a fragment?

This is more of a SQL question… If I understand correctly we need to JOIN “permissions” with “user_registry”. GROUP BY, say, “permission.collection_id”. HAVING count(user_registry) == 0.

see join/5, having/3, group_by/3

Also, I’d suggest to try to write a SQL query first. Then translate to Ecto query =)

1 Like

yes

from p in FileSystem.Schema.Permission,
  as: :permissions,
  ...
  where:
    not exists(
      from u in "user_registry",
      where: u.collection_id == parent_as(:permissions).collection_id
    )
4 Likes

Thank you