Postgres：如何从具有多对多关系的子表中检索数据？ [英] Postgres: How do I retrieve data from child table with many-to-many relations?

问题描述

我正在使用PostgreSQL 9.4。

I am using PostgreSQL 9.4.

我正在尝试在两个表：用户和朋友之间建立多对多关系。据我所知，唯一的外键只能建立一对一的关系，因此，我使用调解员-附加表 user_friend来建立多对多的关系。

I'm trying to establish many-to-many relation between two tables: "user" and "friend". As far as I know, the only foreign key can establish only one-to-one relation, so, I'm using "mediator" - additional table "user_friend" to establish many-to-many relations.

CREATE TABLE "public"."user" (
    "email" varchar(36) NOT NULL COLLATE "default",
    "password" varchar(16) NOT NULL COLLATE "default",
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    CONSTRAINT "User_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."user" OWNER TO "postgres";
CREATE UNIQUE INDEX  "users_id_key" ON "public"."user" USING btree("id" ASC NULLS LAST);


CREATE TABLE "public"."friend" (
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "name" varchar(36) NOT NULL COLLATE "default",
    CONSTRAINT "ability_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."friend" OWNER TO "postgres";
CREATE UNIQUE INDEX  "ability_id_key" ON "public"."friend" USING btree("id" ASC NULLS LAST);


CREATE TABLE "public"."user_friend" (
    "id" uuid NOT NULL DEFAULT uuid_generate_v4(),
    "owner" uuid NOT NULL,
    "friend" uuid NOT NULL,
    CONSTRAINT "ability_relation_pkey" PRIMARY KEY ("id") NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT "owner" FOREIGN KEY ("owner") REFERENCES "public"."user" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE,
    CONSTRAINT "friend" FOREIGN KEY ("friend") REFERENCES "public"."friend" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);
ALTER TABLE "public"."user_friend" OWNER TO "postgres";

工作正常。现在，我需要一个用户及其所有朋友。此查询将返回所有用户朋友的ID：

It works fine. Now I need to get a user and all his friends. This query will return ids of all user's friends:

select row_to_json(t)
from (
    select public.user.email, (
        select array_to_json(array_agg(row_to_json(ability_relations)))
      from (
        select public.user_friend.friend
        from public.user_friend
        where public.user_friend.owner=public.user.id 
      ) ability_relations
    ) as abilities
    from public.user
) t

因此，我正在尝试修改此查询，以从 friend 表中检索所有数据从 user-friend 表中仅检索ID：

So, I'm trying to modify this query to retrieve all the data from friend table instead of retrieving only ids from user-friend table:

select row_to_json(t)
from (
    select public.user.email, (
        select array_to_json(array_agg(row_to_json(ability_relations)))
      from (
        select * 
        from public.friend
        where public.friend.id=(
            select public.user_friend.friend
            from public.user_friend
            where public.user_friend.owner=public.user.id
          )     
      ) ability_relations
    ) as abilities
    from public.user
) t

但是在这里我收到错误：错误：由子查询返回的多于一行用作表达式。

But here I'm receiving the error: ERROR: more than one row returned by a subquery used as an expression.

1. 如何更改此设置查询检索用户和他的朋友作为JSON对象？


2. 像这样的查询是否可以被广泛使用，或者它过于复杂而无法频繁使用，并且可能会对性能产生负面影响？

推荐答案

当以下where子句比较返回多行时发生错误

There is an error when the following where clause comparison returns more than one row"

select row_to_json(t)
from (
...
   where public.friend.id=(
                select public.user_friend.friend
                from public.user_friend
                where public.user_friend.owner=public.user.id
...

将'='运算符更改为'in'，并确保在好友列上没有空值：

change '=' operator to 'in' and mark sure no null values on the friend column:

select row_to_json(t)
from (
...
   where public.friend.id in(
                select public.user_friend.friend
                from public.user_friend
                where public.user_friend.owner=public.user.id
...

前pgAdmin中的普通查询功能可以帮助弄清楚每个联接和子查询需要多长时间才能完成整个语句

Explain Query feature in pgAdmin can help figure out how long does each join and sub-query takes to completer the whole statement

这篇关于Postgres：如何从具有多对多关系的子表中检索数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！