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],
    |> join(:inner, [a], ua in assoc(a, :user_acts))
    |> join(:inner, [a, ua], u in assoc(ua, :user))
    |> where([a, ua, u], == ^
    |> distinct(true)

|> 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 which has an example that is similar to what you are looking for.


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