Select by fields of has_many relationship

Questions / Help
,
1

How can I select records based on the fields in a child record with a has_many relationship?

For example, say you have Artist and Album schemas, and you want to get all artists who have released at least one “rock” album. Expressed in Elixir-like pseudocode:

from artist in Artist where Enum.any?(artist.albums, fn album -> album.genre == :rock end)

It seems I need to use subqueries, but I can’t figure out how to to join the query on the albums table with a query on the artists table.

2

preload takes queries, which is often the place for subqueries on associations … however in this case you should be able to do something like:

from(artist in Artist, join: album in assoc(artist.album), where: album.genre == :rock, distinct: true, select: artist)

That’s an untested ecto call, so typos/syntax erorrs/etc YMMV, but I use similar constructs in my projects … the select may need tweaking, but try it out :slight_smile:

1 Like
3

Thanks! But this gives me

(Ecto.Query.CompileError) malformed join `assoc(artist.album)` in query expression

I’m not sure what to try changing there, since I’m not sure what that bit is meant to do.

You mentioned Repo.preload taking queries. Is it possible to do the kind of narrowing I’m trying to do with a query there, or is that only useful when you want to narrow down just the child records?

4

You can have more information on the Ecto.Query join page.

BTW it looks it should be assoc(artist, :album)

1 Like
5

I finally figured it out:

from artist in Artist,
  distinct: artist.id,
  join: album in Album,
  on: album.lot_id == artist.id,
  where: album.genre == :rock