Ecto Query by Association

Questions / Help
1

Hey Folks!
I’m building out an Ecto query and have run into a block.
I need to get all Evenings that contain certain Event types.
I’m trying to find evenings that have events with type 1 AND 2

Here’s my schema:

schema "evenings" do
    has_many :events, Event
    ...
end

schema "events" do
    type, :integer
    belongs_to :evening, Evening, type: :binary_id
    ...
end

When I query like so I get Evenings with type 1 or 2:

types = [1,2]
from ev in Evening,
  join: e in assoc(ev, :events),
  preload: [events: e],
  where: e.type in ^types
2

Hi!

Even more, you’ll get duplicated Evenings when multiple events are found.
As I understand, you need Evenings which have more than 1 event with type 1 and more than 1 event with type 2. Is it correct?

types = [1, 2]
evening_id_with_event_types_query =
  from events in Event,
    group_by: events.evening_id,
    select: %{types: fragment("ARRAY_AGG(DISTINCT(?))", events.type), evening_id: events.evening_id}

from evenings in Evening,
  inner_join: evening_id_with_event_types in subquery(evening_id_with_event_types_query),
  on: evening_id_with_event_types.evening_id == evenings.id,
  where: evening_id_with_event_types.types == ^types

AFAIK distinct automatically applies ordering, so evening_id_with_event_types.types is sorted alphabetically and types should be sorted too.

3

Not sure you can do it in 1 join.

I would do:

Evening
|> join (:left, [ev], e1 in Event, on: e1.id == ev.id and e1.type == 1)
|> join(:left, [ev], E2 in Event, on: e2.id == ev.id and e2.type == 2)
|> having ([ev, e1, e2], count (e1) >= 1 and count (ev2) >= 2)

You might need a group in there but ecto will tell you.

I’m on mobile so mind spelling mistake etc

4

Thanks for the answer! I wasn’t clear about it so I had to do a little more digging to get the expected behavior. I wanted to get Evenings with at least the event types listed; IE if I ask for Event types [1, 2], it’s okay if the Evening has Events with types [1, 2, 3]

event_query = from event in Event,
  group_by: event.evening_id,
  select: %{types: fragment("ARRAY_AGG(DISTINCT(?))", event.type), evening_id: event.evening_id}

from evening in query,
  inner_join: event in subquery(event_query),
  on: event.evening_id == evening.id,
  where: fragment("? @> ?", event.types, ^types)