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.