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
?