Why does ecto can't preload query with 'Group by'?

Questions / Help
1

Hello,

I’m developping a chat.
I have many rooms, withs members in these rooms. Users can also join multiples rooms so it’s a many to many relationship.
I want to get a room, preload it’s members and for each member, count the number unread messages he has.

Here are my schemas :

defmodule Core.Schema.Room do
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "rooms" do
    field :room_id, :string

    has_many :messages, Message
    many_to_many :users, User, join_through: "room_users"

    timestamps()
  end
end

defmodule Core.Schema.User do
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "users" do
    field :user_id, :string

    many_to_many :rooms, Room, join_through: "room_users"
    timestamps()
  end
end

defmodule Core.Schema.RoomUser do
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "room_users" do
    field :role, :string, default: "member"
    field :last_read, :naive_datetime_usec

    belongs_to :room, Room, primary_key: :room_id, type: :binary_id
    belongs_to :user, User, primary_key: :user_id, type: :binary_id

    timestamps()
  end
end

defmodule Core.Schema.Message do
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "messages" do
    field :text, :string

    belongs_to :room, Room
    belongs_to :user, User, primary_key: :user_id, type: :binary_id

    timestamps(type: :naive_datetime_usec)
  end
end

and here is the request i’m trying to do in ecto:

room_id = "general"

members_preload = from u in User,
                  join: ru in RoomUser, on: ru.user_id == u.id,
                  join: r in Room, on: r.id == ru.room_id,
                  where: r.room_id == ^room_id,
                  left_join: m in Message, on: m.room_id == ru.room_id,
                  where: m.inserted_at > ru.last_read,
                  group_by: [u.id, ru.id],
                  select_merge: %{role: ru.role, number_unread_messages: count(m.id)}

query = from r in Room,
        where: r.room_id == ^room_id,
        preload: [users: ^members_preload]

And here is the error i get:

** (Postgrex.Error) ERROR 42803 (grouping_error) column "r4.id" must appear in the GROUP BY clause or be used in an aggregate function

query: SELECT u0."id", u0."user_id", u0."inserted_at", u0."updated_at", r1."role", count(m3."id"), r4."id" FROM "users" AS u0 INNER JOIN "room_users" AS r1 ON r1."user_id" = u0."id" INNER JOIN "rooms" AS r2 ON r2."id" = r1."room_id" LEFT OUTER JOIN "messages" AS m3 ON m3."room_id" = r1."room_id" INNER JOIN "rooms" AS r4 ON r4."id" = ANY($1) INNER JOIN "room_users" AS r5 ON r5."room_id" = r4."id" WHERE (r2."room_id" = $2) AND (m3."inserted_at" > r1."last_read") AND (r5."user_id" = u0."id") GROUP BY u0."id", r1."id" ORDER BY r4."id"

    (ecto_sql) lib/ecto/adapters/sql.ex:618: Ecto.Adapters.SQL.raise_sql_call_error/1
    (ecto_sql) lib/ecto/adapters/sql.ex:551: Ecto.Adapters.SQL.execute/5
    (ecto) lib/ecto/repo/queryable.ex:153: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
    (elixir) lib/enum.ex:1327: Enum."-map/2-lists^map/1-0-"/2
    (ecto) lib/ecto/repo/queryable.ex:159: Ecto.Repo.Queryable.execute/4
    (ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3

The things is that when i do the members_request only, it works well:

iex()> Repo.all(members_preload)

[
  %Core.Schema.User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    id: "8d51949b-4f60-4a86-b035-054c6064264d",
    inserted_at: ~N[2019-07-12 15:13:12],
    number_unread_messages: 6,
    role: "member",
    room_id: nil,
    rooms: #Ecto.Association.NotLoaded<association :rooms is not loaded>,
    updated_at: ~N[2019-07-12 15:13:12],
    user_id: "Nils"
  },
  %Core.Schema.User{
    __meta__: #Ecto.Schema.Metadata<:loaded, "users">,
    id: "cefb9fab-b281-4c00-9b98-2d8346576144",
    inserted_at: ~N[2019-07-12 15:13:12],
    number_unread_messages: 6,
    role: "member",
    room_id: nil,
    rooms: #Ecto.Association.NotLoaded<association :rooms is not loaded>,
    updated_at: ~N[2019-07-12 15:13:12],
    user_id: "John"
  }
]

How could i get this to work ?
Thanks by advance for yours answers.

1 Like
3

Welcome to the forum,

This is the error and the problem you are having

"r4.id" must appear in the GROUP BY clause or be used in an aggregate function

From what I read it expects in the group_by: [u.id, ru.id], the r.id to be included also maybe something like this group_by: [u.id, ru.id, r.id]

Also in your preload you already have a where why have one here also?
query = from r in Room,
where: r.room_id == ^room_id,
preload: [users: ^members_preload]

These are my first ideas after seeing the error, also if this doesn’t work they also recommend you to use an aggregation which can be found here https://hexdocs.pm/ecto/Ecto.Repo.html#c:aggregate/4

Hope this helps you to solve your problem.

4

I believe this is the telltale part of that query: if you don’t supply any order Ecto will supply one for you based on id - and in this case, the ID from rooms since it’s the outermost part.

primary_key isn’t an option for belongs_to best I can tell; the same function is accomplished with references (telling the association what field in the other schema the foreign key in the this schema refers to).

BUT more importantly, the values given in the code don’t match the schema suggested by the SQL. This code:

# in a schema named Thing
belongs_to :place, Place, references: :other_id

would mean that lookups through this association would look for a Place record whose other_id matches the place_id on a given Thing.

5

i simplified the members_preload request thanks to you :slight_smile: :

members_preload = from u in User,
                  join: ru in RoomUser, on: ru.user_id == u.id,
                  left_join: m in Message, on: m.room_id == ru.room_id,
                  where: m.inserted_at > ru.last_read,
                  group_by: [u.id, ru.id],
                  select_merge: %{role: ru.role, number_unread_messages: count(m.id)}

But group_by: [u.id, ru.id, r.id] doesn’t solve the problem because the "r4.id" in the error message concerns the room in this query:

query = from r in Room,
        where: r.room_id == ^room_id,
        preload: [users: ^members_preload]

I’ve successfully done this request by modifying the raw SQL and it worked, but i want to do it with ecto.
If you have any idea how i could insert r.id from the first query in the group_by of the preload query, it would help me a lot.

I looked Repo aggregate but it can’t help me solve my problem :confused: .

6

Ok to make everything simpler and automate the project let’s try context’s.
https://hexdocs.pm/phoenix/contexts.html

Also please provide a clear database design with tables and the relationships you want to create between them.

I suspect that you are having some errors(not using correctly belongs_to and references) in your schema and migrations.

So my idea is to rebuild everything using context but i can’t help you until you provide what i asked.

Also others may offer more assistance if they can understand your whole project.