CODEWITHSUNDEEP
sqlpostgresql
stkvtflw
stkvtflw

Reputation: 13577

Postgres: How do I retrieve data from child table with many-to-many relations?

I am using PostgreSQL 9.4.

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";

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

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. How should I change this query to retrieve user and his friends as JSON object?
  2. Are queries like this one can be used widely, or it's too complicated to use it frequently and may negatively affect the performance?

Upvotes: 1

Views: 545

Answers (2)

Suhas K
Suhas K

Reputation: 380

I have little or no experience with PostgreSQL. But I do have some experience with relational databases and I don't quite follow the design that you have chosen. From what I understand, you have 'Users' who could be friends. Here friends are also users aren't they? So wouldn't a 'user' and 'user_friend' table be enough? 'user' and 'user_friend' would have a one to many relationship. I have tried to replicate that in schema as follows:

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

CREATE TABLE "public"."user_friend" (
    "owner" uuid NOT NULL,
    "friend" uuid NOT NULL,
    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"."user" ("id") ON UPDATE NO ACTION ON DELETE CASCADE NOT DEFERRABLE INITIALLY IMMEDIATE
)
WITH (OIDS=FALSE);

And to the best of my understanding, I was able to get the desired result:

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.user
        where public.user.id in(
            select public.user_friend.friend
            from public.user_friend
            where public.user_friend.owner=public_user.id
          )     
      ) ability_relations
    ) as abilities
    from public.user as public_user
) t

SQL fiddle:http://sqlfiddle.com/#!15/708dd/6/0

Upvotes: 1

Dan Sin
Dan Sin

Reputation: 491

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
...

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
...

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

Explain Query feature in pgAdmin

Upvotes: 3

Related Questions