时间戳

首页 > 解决方案 > 为什么 ecto 无法使用“Group by”预加载查询？

elixir - 为什么 ecto 无法使用“Group by”预加载查询？

问题描述

我正在开发一个聊天。我有很多房间，这些房间里有会员。用户还可以加入多个房间，因此这是多对多的关系。我想找一个房间，预加载它的成员，并为每个成员计算他拥有的未读消息的数量。

这是我在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]

但这会给我这个错误：

** (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

事情是，当我只做 members_request 时，它运作良好：

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"
  }
]

我怎样才能让它工作？提前感谢您的回答。

这是我使用的模式：

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

标签： elixirecto

解决方案

尝试改变这个：

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

对此：

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

注意对subquery函数的调用。我相信这就是Aleksei Matiushkin在他的评论中提到的。

此代码将使关联预加载作为第二个查询运行。如果您确实需要保留单个查询，请先尝试提出纯 SQL 查询，然后看看是否可以将其转换为 Ecto。

推荐阅读