I want to pass CActiveRecord queries directly to JSON (via CJSON) for transmission to the client. I want to do it efficiently using a format something like this:
{
"table" : {
"fields" : [
"id",
"first_name",
"last_name",
],
"rows" : [
["1","John","Smith"],
["2","Allen","Trovoski"]
]
}
}
However, the standard results that are returned are much more verbose, with column names against every row.
I don’t want to have to pass the data through a conversion function as it is a waste of CPU resources (There is a lot of data).
Any ideas how I can take advantage of the ease of use of Active Record to do this? (Writing the query with the CDbCommand class provides results in the same format anyway).
If I have to extend one of the base classes, what would be the best way to go about it? (Extend CActiveRecord or CJSON)
Edit: Typo
Thanks for the link Antonio, this does what I was asking, I’ll have to do a bit of profiling to see how efficient it is. I can’t help but think it would be a lot quicker if I extended CModel to create a json string directly from the db results, rather than copying it all into an array, then converting the array into a new format, and then iterating through the array to make a string.
I’ve discovered that if using a command rather than an activerecord I can set the results to be returned as as an indexed array rather than an associative one by using queryAll(false). This should allow for a more efficient conversion to the JSON format I specified above. I’ll do some testing tomorrow to see if it is worth it.
$connection = Yii::app()->db;
$sql = "SELECT * from my_table";
$command = $connection->createCommand($sql);
$results = $command->queryAll(false);
I’ve done some tests and I thought I would share the results.
Using a command object over a model to create the data structure I need for JSON is almost 30 times faster.
Demonstrating the importance of not using a model if you just want to push the data out of the door.
My test results:
1000 rows of data are selected from a table and converted into a json string with the format of:
{"table_name": {
"columns": [
"column name 1",
"column name 2",
"column name 3"
],
"rows": [
[
"contents of row 1 : column 1",
"contents of row 1 : column 2",
"contents of row 1 : column 3",
],
[
"contents of row 2 : column 1",
"contents of row 2 : column 2",
"contents of row 2 : column 3",
],
...
]
}
Total number of cycles results are averaged over : 100
Total time for PDO: 0.025728225708008
Total time for Command: 0.025343799591064
Total time for Model: 0.75804703235626
Total time for Command with Query Builder: 0.025374293327332
As you can see the results for using PDO directly, a command object or a command object with the query builder are all pretty similar ( For some reason the command object is always slightly faster than PDO - I assume the Yii team knows something I don’t about it.)
I’ve attached the Yii project with the tests for anyone who wishes to have a look or try for themselves.
NB I used a custom function to build the JSON to speed things up even further, it is slightly different for the model version, but not in a way that should make a significant difference to the speed. See the attached project for the code.
Edit: Accidentally wrote ‘Model with Query Builder’ instead of ‘Command with Query Builder’
1780
speedtest.zip
