We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Way to standardize results from \Phalcon\Mvc\Model\Manager?

I'm working on an API, where consumers of the API are allowed to adjust which fields they would like returned in the response. I'm finding that \Phalcon\Mvc\Model\Manager::executeQuery() will return something different, depending on the queried fields. Is there any way to set \Phalcon\Mvc\Model\Manager::executeQuery() so that it always returns the same format for a response?

Reproducible Test

<?php
class Role extends \Phalcon\Mvc\Model
{
    /**
     * @var integer
     */
    public $id;

    /**
     * @var string
     */
    public $name;

    /**
     * Set the table
     */
    public function initialize()
    {
        $this->setSource('roles');
    }
}

class User extends \Phalcon\Mvc\Model
{
    /**
     * @var integer
     */
    public $id;

    /**
     * @var integer
     */
    public $roleId;

    /**
     * @var string
     */
    public $email;

    /**
     * Set the table
     */
    public function initialize()
    {
        $this->setSource('users');
    }
}

$di = new Phalcon\DI\FactoryDefault();

$di->setShared('db', function () {
    return new \Phalcon\Db\Adapter\PDO\Mysql(array(
        'host' => '127.0.0.1',
        'username' => 'root',
        'password' => '',
        'dbname'   => 'test',
        'charset'  => 'utf8',
    ));
});

$di->setShared('modelsManager', function() {
    return new \Phalcon\Mvc\Model\Manager();
});

/* Setup a sample DB schema */
$di['db']->execute('DROP TABLE IF EXISTS `roles`');
$di['db']->execute('DROP TABLE IF EXISTS `users`');

$di['db']->execute('CREATE TABLE `roles` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8');

$di['db']->execute('CREATE TABLE `users` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `roleId` int(11) unsigned NOT NULL,
  `email` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8');

/* Insert some role fixtures */
$di['db']->insert(
    'roles',
    array(1, 'Regular User'),
    array('id', 'name')
);

$di['db']->insert(
    'roles',
    array(2, 'Admin'),
    array('id', 'name')
);

/* Insert some user fixtures */
$di['db']->insert(
    'users',
    array(1, 1, '[email protected]'),
    array('id', 'roleId', 'email')
);

$di['db']->insert(
    'users',
    array(2, 1, '[email protected]'),
    array('id', 'roleId', 'email')
);

$di['db']->insert(
    'users',
    array(3, 2, '[email protected]'),
    array('id', 'roleId', 'email')
);

/* Select all from both objects */
$rows = $di['modelsManager']->executeQuery('SELECT user.*, role.* FROM User user LEFT JOIN Role role ON role.id = user.roleId');

echo "\r\n" . get_class($rows) . "\r\n";

$data = array();
foreach ($rows as $key => $value) {
    $data[$key] = $value;
}
echo json_encode($data);

echo "\r\n";

/* Select some from one object, partial from another */
$rows = $di['modelsManager']->executeQuery('SELECT user.*, role.id, role.name FROM User user LEFT JOIN Role role ON role.id = user.roleId');

echo "\r\n" . get_class($rows) . "\r\n";

$data = array();
foreach ($rows as $key => $value) {
    $data[$key] = $value;
}
echo json_encode($data);

echo "\r\n";

/* Select partial from both objects */
$rows = $di['modelsManager']->executeQuery('SELECT user.id, user.email, role.id, role.name FROM User user LEFT JOIN Role role ON role.id = user.roleId');

echo "\r\n" . get_class($rows) . "\r\n";

$data = array();
foreach ($rows as $key => $value) {
    $data[$key] = $value;
}
echo json_encode($data);

echo "\r\n\r\n";

Observed Results

Phalcon\Mvc\Model\Resultset\Complex
[
    {
        "user": {
            "id": "1",
            "roleId": "1",
            "email": "[email protected]"
        },
        "role": {
            "id": "1",
            "name": "Regular User"
        }
    },
    {
        "user": {
            "id": "2",
            "roleId": "1",
            "email": "[email protected]"
        },
        "role": {
            "id": "1",
            "name": "Regular User"
        }
    },
    {
        "user": {
            "id": "3",
            "roleId": "2",
            "email": "[email protected]"
        },
        "role": {
            "id": "2",
            "name": "Admin"
        }
    }
]

I would like everything returned in the same format as above. Fields belonging to "user" are nested in "user" and fields belonging to "role" are nested in "role".

Phalcon\Mvc\Model\Resultset\Complex
[
    {
        "user": {
            "id": "1",
            "roleId": "1",
            "email": "[email protected]"
        },
        "id": "1",
        "name": "Regular User"
    },
    {
        "user": {
            "id": "2",
            "roleId": "1",
            "email": "[email protected]"
        },
        "id": "1",
        "name": "Regular User"
    },
    {
        "user": {
            "id": "3",
            "roleId": "2",
            "email": "[email protected]"
        },
        "id": "2",
        "name": "Admin"
    }
]

Above, I would like "id" and "name" to be nested in a "role" .

Phalcon\Mvc\Model\Resultset\Simple
[
    {
        "id": "1",
        "email": "[email protected]",
        "name": "Regular User"
    },
    {
        "id": "1",
        "email": "[email protected]",
        "name": "Regular User"
    },
    {
        "id": "2",
        "email": "[email protected]",
        "name": "Admin"
    }
]

Above, I would like "id" and "name" nested in "role". I would also like "id" and "email" nested in "user". Note that I selected two id's in the query but one overrode the other.

edited Mar '16

You have to name second id another, example userId or roleId or something like this. Also is you select full objects then resultset is returned as complex, if you select only columns, then as simple.

Also nesting only works when you selecting full objects.