SQL Join vs Code aggregation

Question

I'm trying to develop the most optimal way to retrieve aggregate information from my relational database (mysql). I have two tables: vendor and model. The relationship between the two is one to several, that is, a vendor can have several models and a model can only be associated with one vendor.

What is the best way to retrieve the information from both tables so that each vendor has its associated models? My idea is to make an REST API so that it can have the following output:

{
    name: "one",
    description: "one description",
    models: [
        {
            name: "one_model_1",
            price: 100
        },
        {
            name: "one_model_2",
            price: 200
        }
    ]
    ...

1. Retrieve by making a join between both tables. In this way the following output would be obtained:

   select v.name as vendorName, v.description as vendorDescription, m.name 
   as modelName, m.price as modelPrice from vendor v inner join model m on 
   v.id = m.vendor_id;
   

   

   The problem is that it would be necessary to perform a code processing to associate each vendor element with a list of models. In this case the information that comes from the database would bring repeated information from the vendor.

2. Perform a search of all vendors first and then loop them to retrieve the associated models. Here the problem N + 1 would be presented, since it would be necessary a query to get the N vendors and then N queries to retrieve the models.

3. Perform a search of the vendors first. Then, from the ids of the vendors, all the models would be retrieved and the vendor - model association would be made through code.

I wonder what would be the best solution.

Answer 1

SELECT v.Name, v.Description,
       GROUP_CONCAT(CONCAT(m.Name, ':', m.Price))
    FROM Vendors AS v
    JOIN Models AS m ON v.id = m.vendor_id
    GROUP BY v.Name, v.Description

One call, the text manipulation is done before returning the resultset.