How to select redords which have at least `n` associations matching condition

My User schema has the following associations

    has_many(:user_acts, UserAct)
    many_to_many(:acts, Act, join_through: UserAct)

I want to select users which have at least 3 user_acts matching condition(user_acts.act_id in current_user_act_ids.

My current query:

    current_user_act_ids = Act
    |> select([a], a.id)
    |> join(:inner, [a], ua in assoc(a, :user_acts))
    |> join(:inner, [a, ua], u in assoc(ua, :user))
    |> where([a, ua, u], u.id == ^current_user.id)
    |> distinct(true)

User
|> join(:inner, [u], ua in assoc(u, :user_acts))
|> join(:inner, [u, ua], a in assoc(ua, :act))
# something_must_be_here
|> distinct(true)

You’ll likely want to create a subquery that you join to. The subquery would do the reduction on user_accts with a count >= 3. See https://hexdocs.pm/ecto/Ecto.Query.html#subquery/2 which has an example that is similar to what you are looking for.

2 Likes

Instead of joining the assoc, you can also join explicitly with more than one on. Once you get the correct join in sql, it’s mainly a matter of the right combination of group by and having.

Ecto queries are very similar to the target SQL statements, so you will be able to translate them easily.

1 Like