I need to write an Ecto Query which returns all schemas NOT having an association with another schema. For example, I have the following schemas connected with a simple one-to-one association:
defmodule Organization do schema "organizations" do has_one :membership, Membership end end defmodule Membership do schema "memberships" do belongs_to :organization, Organization end end
My migration looks like this:
def change do # Could be in a previous migration create table(:organizations) do # adds some fields here end create table(:memberships) do add :organization_id, references(:organizations) end alter table(:organizations) do add :membership_id, references(:memberships) end end
Now, I need to query for all Organizations which DO NOT have a membership. This great article explains that it is much more efficient to use NOT IN or NOT EXISTS where-clauses with subqueries, however I figured why not simply query for e.g.
organization.membership_id |> is_nil()?
Unfortunately, the following query always returns all Organizations, also if I insert a Membership with a connection to a Organization:
from(o in Organization, where: is_nil(o.membership_id))
The problem seems to be that the
membership_id is not set in the
:organizations table whenever I insert a Membership. Do you know a better (and efficient) way of writing this query?