Elixir: Ecto — find records on empty associations

Minh Reigen
2 min readFeb 16, 2021

In my experience and the apps I worked on, there were a few times I needed to find records based on empty associations.

Let’s imagine a silly example where you were writing a software to manage your franchise of electronic stores. You wanted to find store locations that run out of both Sega Genesis consoles and VCR players (I know, I’m old.) Being a programming nerd and recently picked up on Elixir, you would want to write your own Ecto query to find those. How would you do this?

NOT EXISTS with fragment function is the answer! (Well, you can technically do this with LEFT JOIN and NULL check too, but let’s go with NOT EXISTS in this post)

You can also combine another condition where the stores also have no delivery truck.

I know this is not in the scope of this short post, but I can’t help. You can use defmacro to actually clean this up a bit:

I hope you get the idea of using NOT EXISTS and apply it in your scenarios when you need to use it.

Please check out my other Elixir posts if you are interested. Thank you!