Return everything from many-to-many relationship with only one query-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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;