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

SQL (Postgres) how to query many to many relationship – Postgresql

I have company and user as many to many relationship.
A company can have many users and a user can have many companies.

I have the following tables:

Company (id, name)

User (id, name)

User_Company (userId, companyId)

How do I search by company’s name and by a user?

Eg. Search company where name = "Apple" and userId = 1

Example result:

[
    {
        "id": 1,
        "name": "Apple",
        "users": [
            {
                "id": 1,
                "name": "User 1"
            },
            {
                "id": 5,
                "name": "User 5"
            }
            
        ]
    },
    {
        "id": 3,
        "name": "My Apple",
        "users": [
            {
                "id": 1,
                "name": "User 1"
            },
            {
                "id": 2,
                "name": "User 2"
            }
            
        ]
    }
]

Note: I’m using TypeOrm with Postgresql

Tags:

2

Answers


  1. 0

    To achieve this using TypeORM, you can define the entities for Company, User, and User_Company and their relationships as follows:

    import { Entity, Column, PrimaryGeneratedColumn, ManyToMany, JoinTable } from 'typeorm';

@Entity()
export class Company {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToMany(() => User, (user) => user.companies)
    @JoinTable()
    users: User[];
 }

 @Entity()
    export class User {
    @PrimaryGeneratedColumn()
    id: number;

    @Column()
    name: string;

    @ManyToMany(() => Company, (company) => company.users)
        companies: Company[];
    }

 @Entity()
     export class UserCompany {
     @PrimaryGeneratedColumn()
     id: number;

     @Column()
     userId: number;

     @Column()
     companyId: number;
 }

    With these entities defined, you can use TypeORM’s query builder to construct a query that joins the Company, UserCompany, and User entities based on their relationships and applies filters to the result set based on the company name and user ID.

    Here’s an example of how to achieve this:

    import { getRepository } from 'typeorm';
import { Company } from './entities/Company';
import { User } from './entities/User';
import { UserCompany } from './entities/UserCompany';

const companyRepository = getRepository(Company);

const company = await companyRepository
   .createQueryBuilder('company')
   .innerJoin('company.users', 'user')
   .innerJoin(UserCompany, 'user_company', 
'user_company.companyId = company.id')
   .where('company.name = :name AND user.id = 
   :userId', { name: 'Apple', userId: 1 })
   .getOne();

    This code uses TypeORM’s query builder to construct a query that joins the Company, User, and UserCompany entities based on their relationships. It then applies filters to the result set using the where method to limit the results to only those where the company name is "Apple" and the associated user has an ID of 1.

    Login or Signup to reply.
  2. 0 
    create table company (id integer primary key, name varchar(50));
create table users (id integer primary key, name varchar(50));
create table user_company (userid integer not null, companyid integer not null);

insert into company (id, name) values
(1, 'Apple'),
(3, 'My Apple');

insert into users (id, name) values
(1, 'User 1'),
(2, 'User 2'),
(3, 'User 3'),
(4, 'User 4'),
(5, 'User 5');

insert into user_company (userid, companyid) values
(1, 1),
(1, 3),
(5, 1),
(2, 3);

select array_to_json(array_agg(row_to_json(jx)))
from (
  select id, name,
    (
      select array_to_json(array_agg(distinct u))
      from (
        select id, name
        from user_company, users
        where user_company.companyid = company.id
          and users.id = user_company.userid
      ) u
    ) as users
  from company
) jx;

    It gives :

    [{"id":1,"name":"Apple","users":[{"id":1,"name":"User 1"},{"id":5,"name":"User 5"}]},{"id":3,"name":"My Apple","users":[{"id":1,"name":"User 1"},{"id":2,"name":"User 2"}]}]
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search