Effiecient CActiveRecord to JSON

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" : [





        "rows" : [






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’



Thanks for sharing