How do I query for all records with no matching has_one?

Phoenix Forum Questions / Help
, ,
1

If I have two associated Ecto-schemas like this:

schema "foo" do
  field :somefield, :string
  field :someotherfield, :integer
  ...
  has_one :bar, MyApp.Bars.Bar
  timestamps()
end

schema "bar" do
  field :somefield, :string
  field :someotherfield, :integer
  ...
  belongs_to :foo, MyApp.Foos.Foo 
  timestamps() 
end

foo_id is defined on the bar table with a unique index.

What would be the cleanest way to query for all Foos with no related Bar?

2

A left_join will do what you’re looking for:

from(foos in Foo,
  left_join: bars in assoc(foos, :bar)
  where: is_nil(bars.id)
)

This works because if there’s no matching Bar the left join includes a row with all NULLs for Bar’s columns.

2 Likes
3

For more information I recommend to take a look at:

1 Like
4

Alternatively (I haven’t tested if this is more performant or not):

from foos in Foo,
  where: fragment("NOT EXISTS (SELECT 1 FROM bars b WHERE b.foo_id = ?)", foos.id)

Unfortunately AFAIK Ecto still do not support existential queries.

1 Like
5

This does the trick, thanks!

6

Thanks, will check it out.