cphalcon icon indicating copy to clipboard operation
cphalcon copied to clipboard

[BUG]: Unexpected LEFT JOIN behavior

Open wurst-hans opened this issue 2 years ago • 0 comments

Using Phalcon 4, I'm doing some database queries using query builder and PHQL. Both methods have an issue on using LEFT JOIN. Image any relation, ex. customers and addresses. Example queries are:

$build = new Builder([
    'models' => ['entity' => Customers::class],
    'joins' => [Addresses::class, 'entity.id=address.customer_id', 'address', 'LEFT'],
    'columns' => ['entity.*', 'address.*'],
]);
$result = $builder
    ->getQuery()
    ->execute();
foreach ($result as $record) {
    // $record->entity
    // $record->address
}

or

$phql = "SELECT entity.*, address.*
    FROM " . Customers::class . " entity
    LEFT JOIN " . Addresses::class . " address ON entity.id=address.customer_id";
$result = $this->modelsManager
    ->createQuery($phql)
    ->execute();
foreach ($result as $record) {
    // $record->entity
    // $record->address
}

When there are customers having addresses, all is fine and I get my data. Now imagine, that there is no address for customer. In real SQL world, I would get NULL for address. But Phalcon returns an freshly instantiated object of type Addresses (without any data) instead of NULL. Is there any ORM option that can be set to return NULL for joined relations that return no data?

wurst-hans avatar Dec 02 '22 15:12 wurst-hans