Hey y’all,
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?
Have you included the :membership_id in your Organization.changeset() function?
...
|> cast(params, [<all_previous_fields_without_membership_id_maybe?>])
Hmm, I’m using ex_machina to insert the structs. However what I noticed is that the Membership structs have a organization_id, but the Organization structs don’t have an membership_id. Maybe I have to add that field manually next to the has_one relationship in the Organization schema?
Very likely you will have to. The proper belongs_to, has_many, has_one etc. assocs are only put in the code when the models are generated from the command line (via mix phx.gen.schema).
From then on you have to manually add those assocs when you change the schema through migrations.
1 Like
Can you elaborate on why you’re trying to do the foreign key key on both sides? has_one is built on the idea that the other table has the foreign key. I think you only need memberships to have an organization_id. Plus, it seems like organizations would have many memberships right?
1 Like
Of course. I’d like to join Organizations with Memberships in such a way that all Organizations HAVING a Membership are excluded. So, that’s a left outer join if I am not mistaken. I’ve read about a couple solutions, and I was just exploring the possibility to simply select Organizations which don’t have a Membership association yet, therefore have a membership_id of nil.
Don’t worry about the Domain, this is only an example, but I agree with you that in a real world case Organizations could possibly have multiple Memberships.
Left joins work without the organization needing a membership column.
from o in Organization,
left_join: m in assoc(o, :membership),
where: is_nil(m.id)
That ^ works just fine with a has_one :membership, Membership, and has_one does NOT use a column on the organizations table, rather it expects an organization_id column on the memberships table.
1 Like
Thank you, that’s the solution that I currently have. However, this requires me to join the 2 tables, which is fine for the scale at which we are operating. I was just spitballing about how this query could be done without a left_join.
I guess one solution could be to turn around the relationship, thus putting the belongs_to on the Organization. This way I could still use the query: from(o in Organization, where: is_nil(o.membership_id)), which does not require a join.
However, @benwilson512, your solution solves my question, thus I’ll accept it as the answer 
If you wanted to do it without a left join you could use a sub query and NOT IN, which would still work with the join style columns. However per the blog post you listed, sub query + NOT IN and left join produce the same actual query plan in Postgres.
1 Like
