Question 90807 in PostgreSQL Question posted in
The official documentation can be found here.

sql select column from nested associated tables – Postgresql

I have 4 tables in PostgreSQL:

  1. Projects
  2. Organizations
  3. Organization_membership
  4. User
CREATE TABLE IF NOT EXISTS organization(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4 (),
    CONSTRAINT plan_id_fk FOREIGN KEY (plan_type)
        REFERENCES plan(plan_type) MATCH SIMPLE
        ON UPDATE NO ACTION ON DELETE NO ACTION
);

CREATE TABLE IF NOT EXISTS users(
    id varchar(100) PRIMARY KEY,
    email VARCHAR(50) NOT NULL UNIQUE,
);

CREATE TABLE IF NOT EXISTS organization_membership(
    organization_id uuid not null,
    user_id varchar(100) not null,
    CONSTRAINT organization_id_fk FOREIGN KEY (organization_id)
        REFERENCES organization(id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION,
    CONSTRAINT users_uuid_fk FOREIGN KEY (user_id)
        REFERENCES users(id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION,
    PRIMARY KEY (organization_id, user_id)
);


CREATE TABLE IF NOT EXISTS project(
    id uuid PRIMARY KEY DEFAULT uuid_generate_v4 (),
    owner uuid NOT NULL,
    project_name VARCHAR(100),
    CONSTRAINT project_owner_fk FOREIGN KEY (owner)
        REFERENCES organization(id) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE NO ACTION,
);

I am trying to get projects which belongs to user 1,

so I am trying to get all projects for user 1 from all organizations of this user

I just need raw sql code

I tried this:

     await database.fetch_all(
             query="SELECT organization_membership.*, organization.id FROM organization JOIN organization_membership ON organization.id = organization_membership.organization_id WHERE organization_membership.user_id = :id",
             values={'id': acting_user.id},
         )

but this returns only organizations for this user

also I have tried this:

await database.fetch_all(
            query="SELECT * from project JOIN organization ON project.owner = organization.id JOIN organization_membership ON organization.id = organization_membership.organization_id WHERE organization_membership.user_id = :id",
            values={'id': acting_user.id},
        )

this returns empty data

Tags:

2

Answers


  1. 0 
    select p.* from users u
join organization_membership om on u.id = om.user_id
join organization o on om.organization_id = o.id
join project p on o.id = p.owner
where u.id = '1';

    Edit: Updated as per question

    Login or Signup to reply.
  2. 0

    It depends a lot on how you have your relationship scheme. But what about nesting SQL statements?

    SELECT * FROM projects 
WHERE organization_id in (
                       SELECT organization_id FROM organizations WHERE user_id = 1);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search