Cphalcon: [BUG]: Error: Undefined index in Phalcon\Mvc\Model\Query\Builder

[phptux]

Hi,

$builder = new \Phalcon\Mvc\Model\Query\Builder();
        $builder
            ->from(['u' => 'User\Model\User'])
            ->columns(['u.*', 'r.name'])
            ->leftJoin('User\Model\Role', 'u.role_id = r.id', 'r')
            ->orderBy('u.id DESC');

PHQL from Builder:

SELECT u.*, r.name FROM [User\Model\User] AS [u] LEFT JOIN [User\Model\Role] AS [r] ON u.role_id = r.id ORDER BY u.id

Execute the Builder:

$result = $builder->getQuery()->execute();

Error Message:

Undefined index: name in phalcon/Mvc/Model/Resultset/Complex.zep on line 223

With Phalcon v3.4 is this PHQL not a problem. Only with Phalcon v4.

What can I do?

Details

• Phalcon version: 4.0.0-rc.1
• PHP Version: 7.3.10
• Database: MariaDB v10.4.8
• Operating System: Linux-Mint

[niden]

Silly question but is name a field in the Role model?

[phptux]

Yes. The field name exists in the Role model.

Code for reproduce

Database

CREATE TABLE `role` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `role` (`id`, `name`) VALUES
(1, 'Admin');

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `role_id` int(11) NOT NULL,
  `username` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `user` (`id`, `role_id`, `username`) VALUES
(1, 1, 'phptux');


ALTER TABLE `role`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `user`
  ADD PRIMARY KEY (`id`);

Models

namespace User\Model;

use Phalcon\Mvc\Model;

class User extends Model
{
    public $id;
    public $role_id;
    public $username;
}

---

namespace User\Model;

use Phalcon\Mvc\Model;

class Role extends Model
{
    public $id;
    public $name;
}

Query Builder

$builder = new \Phalcon\Mvc\Model\Query\Builder();
$builder
    ->from(['u' => 'User\Model\User'])
    ->columns(['u.*', 'r.name'])
    ->leftJoin('User\Model\Role', 'u.role_id = r.id', 'r')
    ->orderBy('u.id DESC');

PHQL

SELECT u.*, r.name FROM [User\Model\User] AS [u] LEFT JOIN [User\Model\Role] AS [r] ON u.role_id = r.id ORDER BY u.id DESC

Execute the QueryBuilder

$res = $builder->getQuery()->execute();
foreach ($res as $item) {
    echo $item->u->username, ': ', $item->r->name, "\n";
}

I become the error message from PHP:

Notice: Undefined index: name in phalcon/Mvc/Model/Resultset/Complex.zep on line 223

If I change the columns to:

$builder->columns(['u.*', 'r.*']);

$res = $builder->getQuery()->execute();
foreach ($res as $item) {
    echo $item->u->username, ': ', $item->r->name, "\n";
}

works fine. Output is:

phptux: Admin

I get the error message only when specifying alias and column name (r.name or u.username).

[Jeckerson]

Well, make sense that it doesn't work.

With your example

SELECT u.*, r.name FROM [User\Model\User] AS [u] LEFT JOIN [User\Model\Role] AS [r] ON u.role_id = r.id ORDER BY u.id DESC

You have

$item->u->username

and (r.name)

$item->name

This is Phalcon's approach, when you select * from one of tables, it puts into sub object with alias name. But if you select specific field, it will be located on root of row object.

[phptux]

Hmm, in Phalcon v3.4 it worked; not in Phalcon v4 :-(

I change my QueryBuilder to:

$builder = new \Phalcon\Mvc\Model\Query\Builder();
$builder
    ->from(['u' => 'User\Model\User'])
    ->columns(['u.*', 'name' => 'r.name']) // column alias
    ->leftJoin('User\Model\Role', 'u.role_id = r.id', 'r')
    ->orderBy('u.id DESC');


$res = $builder->getQuery()->execute();
foreach ($res as $item) {
    echo $item->u->username, ': ', $item->name, "\n";
}

or

$builder = new \Phalcon\Mvc\Model\Query\Builder();
$builder
    ->from(['u' => 'User\Model\User'])
    ->columns(['u.*', 'r.*')
    ->leftJoin('User\Model\Role', 'u.role_id = r.id', 'r')
    ->orderBy('u.id DESC');


$res = $builder->getQuery()->execute();
foreach ($res as $item) {
    echo $item->u->username, ': ', $item->r->name, "\n";
}

Both variants work. Only the last one is very greedy ;-)