skip to Main Content

I’ll give an example to better clarify what I want:

Suppose I have the following classes in my programming language:

Class Person(
  int id,
  string name,
  List<Car> cars
);
Class Car(
  int id,
  string name,
  string brand
)

I want to save that in a PostgreSQL database, so I’ll have the following tables:

CREATE TABLE person(
  id SERIAL,
  name TEXT
);
CREATE TABLE car(
  id SERIAL,
  name TEXT,
  brand TEXT
)
CREATE TABLE person_car(
  person_id int,
  car_id int,
  
  CONSTRAINT fk_person
    FOREIGN KEY (person_id)
    REFERENCES person(id),

  CONSTRAINT fk_car
    FOREIGN KEY (car_id)
    REFERENCES car(id)
)
  

Then, I want to select all people with their cars from DB. I can select all people, then for each person, select their cars. But supposing I have 1000 people, I will have to query the DB 1001 times (one to select all people, and one for each person, to get their cars).

Is there an efficient way to bring all people, each with all their cars in a single query, so that I can fill my classes with the correct data without querying the DB a lot of times?

2

Answers


  1. You are joining person and car to person_car based on their respective ID’s.

    SELECT
        person.name,
        person.id as person_id,
        car.name,
        car.brand,
        car.id as car_id
    FROM
        person 
    JOIN 
        person_car 
    ON
        person.id = person_car.person_id
    JOIN 
        car
    ON
        car.id = person_car.car_id
    
    Login or Signup to reply.
  2. If you want to return a hierarchical dataset, you can use subqueries with COALESCE, for example :

    SELECT 
      p.id
      p.name,
      COALESCE((SELECT 
          json_agg(json_build_object(
             'id', c.id,
             'name', c.name,
             'brand', c.brand
          ))
       FROM car AS c
       JOIN person_car pc ON c.id = pc.car_id
       WHERE pc.person_id = p.id), '[]'::json) AS cars
    FROM person AS p;
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search