时间戳

首页 > 解决方案 > 如何在 jsonb 字段属性上使用 group_by 查询最新条目

postgresql - 如何在 jsonb 字段属性上使用 group_by 查询最新条目

问题描述

给定一个大致如下所示的模式（我正在使用 Ecto）：

我想创建一个查询，该查询将返回用户所有节点的最新版本，按版本插入时间排序。

Ecto和关联之间的关系User和使用。不幸的是，模式很奇怪，与节点的关系是在 jsonb 字段中的嵌套属性上建立的（我无法更改）。Nodehas_manybelongs_toVersion

到目前为止我最好的是：

grouping = Version
|> select([g], %{node_id: fragment("data->'node'->>'id'"), inserted_at: max(g.inserted_at)})
|> group_by([g], g.node_id)

Version
|> join(:inner, [v], g in subquery(grouping), on: fragment("data->'node'->>'id'") == g.node_id)
|> join(:inner, [v], n in Node, on: fragment("data->'node'->>'id'") == n.id)
|> where([_v, _g, n], n.user_id == ^user_id)
|> order_by([v], desc: :inserted_at)
|> Repo.all

当我运行查询时，我收到此错误：

** (Postgrex.Error) ERROR 42703 (undefined_column) column st0.node_id does not exist

query:
  SELECT t0."guid", t0."state", t0."channel", t0."tags", t0."data", t0."meta", t0."inserted_at", t0."updated_at" FROM "versions" AS t0
  INNER JOIN (
    SELECT data->'node'->>'node_id' AS "node_id", max(st0."inserted_at") AS "inserted_at" FROM "versions" AS st0 GROUP BY st0."node_id") AS s1
    ON data->'node'->>'node_id' = s1."node_id"
  INNER JOIN "nodes" AS n2 ON data->'node'->>'node_id' = n2."id"
  WHERE (n2."user_id" = $1)
  ORDER BY t0."inserted_at" DESC

我觉得我在这里不是一百万英里，但很明显我做错了什么。如果有人能够指出我应该用普通 SQL 做什么，我就能将其翻译成 Ecto。

标签： postgresqlgroup-byelixirecto

解决方案

已经解决了我自己的问题，将在这里分享。

首先，@PeacefulJames 的评论揭示了 Ecto 组成GROUP BY查询部分的方式中的 SQL 错误。我可以通过使用来解决这个问题group_by(fragment("node_id"))。

无论如何，这让我看到查询是错误的，这篇文章帮助我指出了正确的方向。

以下查询为我提供了我想要的结果。

SELECT t1."guid", t1."state", t1."channel", t1."tags", t1."data", t1."meta", t1."inserted_at", t1."updated_at"
  FROM (
    SELECT st0.data->'node'->>'node_id' AS "node_id", max(st0."inserted_at") AS "inserted_at"
    FROM "versions" AS st0
    GROUP BY node_id
  ) AS s0
  INNER JOIN "versions" AS t1
    ON (t1.data->'node'->>'node_id' = s0."node_id")
    AND (t1."inserted_at" = s0."inserted_at")
  INNER JOIN "nodes" AS n2
    ON t1.data->'node'->>'node_id' = n2."id"
  WHERE (n2."user_id" = $1)
  ORDER BY t1."inserted_at" DESC

使用以下代码在 Ecto 中完成：

grouping = Version
|> select([v], %{address: fragment("?.data->'node'->>'node_id'", v), inserted_at: max(v.inserted_at)})
|> group_by(fragment("node_id"))

subquery(grouping)
|> join(:inner, [g], v in Version, on: fragment("?.data->'node'->>'node_id'", v) == g.node_id and v.inserted_at == g.inserted_at)
|> join(:inner, [_g, v], n in Node, on: fragment("?.data->'node'->>'node_id'", v) == n.id)
|> where([_g, _v, n], n.user_id == ^user_id)
|> order_by([_g, v], desc: v.inserted_at)
|> select([_g, v], v)
|> Repo.all

推荐阅读