Hey

I have a users - teams many to many setup through a users_teams table.

In the team schema I have the following fields mapping to the users:

    has_many :active_users_teams, UserTeam
    has_many :active_users, through: [:active_users_teams, :user]
    field :active_member_count, :integer, virtual: true, default: nil

In the users table I have these fields that I fill in queries based on the given team:

    field :role_in_selected_team, :string, virtual: true
    field :status_in_selected_team, :string, virtual: true

These work great separately.

Now I am trying to combine the two, so I can preload the users under a team and have these fields on the user loaded as well.

This code is just from messing around:

    user_with_role =
      from(
        ut in UserTeam,
        join: u in assoc(ut, :user),
        select: %User{u | role_in_selected_team: ut.role, status_in_selected_team: ut.status}
      )

    team_with_member_count =
      from(
        t in Team,
        join: ut in assoc(t, :active_users_teams),
        group_by: t.id,
        preload: [:owner, active_users: ^user_with_role],
        select: %Team{t | active_member_count: count(ut.id)}
      )

    teams = Repo.all(team_with_member_count)
    team = Enum.find(teams, fn team -> team.id == 3 end)

    IO.inspect(team.active_users |> length())
    IO.inspect(team.active_users_teams |> length())
    members = Repo.all(user_with_role)
    IO.inspect(members |> length())

I observed this strange thing, that as you see I print out 3 numbers.

The team in question has 25 members.

The output is somehow this:

5
25
25

As you see the latter 2 numbers are 25, the last is the query executed by itself that is used in the active_users field preload.

The middle one is the active_users_teams field form the team schema that gets loaded when the active_users automatically.

Now as you se the first number is not 25, when I use the user_with_role query in the preload of the active_users field.
Note: For running the first query by itself I supplied a static team_id(that matches the incoming one) in the query, else it returns all of them.

Just selecting u in the first query does not change the result.

If I don’t do the custom preload query for active_users it returns 25 users as well.

What am I messing up here?

Any ideas?