Returning all results with relations in a single query

<?php

use Phalcon\Mvc\Query\Builder;

$results = (new Builder())
    ->columns(
        [
            'manufacturerId' => 'man.manufacturer_id',
            'productId'      => 'prd.product_id',
        ]
    )
    ->addFrom(Manufacturer::class, 'man')
    ->innerJoin(Products::class, 'prd.manufacturer_id = man.manufacturer_id', 'prd')
    ->innerJoin(Models::class, 'mod.product_id = prd.product_id', 'mod')
    ->getQuery()
    ->execute()
;

Then you will have in your results a collection where you can do echo $record->manufacturerId etc. Note the keys in the columns array are the aliases and the values the actual fields. The last parameter on addFrom as well as innerJoin is the alias. You can of course use join, outerJoin etc. depending on your needs.

Thank you Nikolaos for the detailed reply.

Am i right in saying that will return “flat” rows (i.e all columns together).

How would I retrieve them in a nested object (models as an array inside products, and products as an array inside manufacturers)

How would i retrieve all columns in each table ?

Phalcon does not support eager loading but it does have the ability to cache related data. So in your example assuming that you have set relationships and you have supplied the reusable flag to true like so:

$this->hasMany(
    'manufacturer_id', 
    Products::class,
    'manufacturer_id,
    [
        'reusable' => true,
        'alias'       => 'products'
    ]

Then you find your manufacturers

$manufacturers = Manufacturers::find()`

and then use the relationships in a loop:

foreach ($manufacturers in $manufacturer) {
    $products = $manufacturer->getRelated('products');
    .....
}

Now the above will give you a collection of objects and call the database only when you call getRelated. That result will be cached so if there is a second call to getRelated for the same relationship with the same conditions, the cached results will be returned.

As you can see the above implementation suffers from the N+1 query, meaning that you will have a ton of queries just for displaying a list of manufacturers.

You can always create an array yourself with the data from the loop(s) and send it back to the caller. This way you will achieve what you are looking for.

I would suggest against eager loading though since you do get a lot more data than you really need. A flat query such as the one in the previous answer will definitely solve the N+1 problem and give you the data you need. In the vast majority of cases, in applications that I have worked on, there is no need to use eager loading since all you need is to show joined data vs getting the whole record. So really a flat join just works. Now I don't know your application needs but if you cannot or do not want to use the flat query with joins, you will have to either fall in the N+1 trap, or create a multidimentional array of objects that will have the eager loading you require.