best way to get related data from models in yii and return json

Question

Hi there just question

im working on a restful application for a project that is using sproutcore on the front end.

My question is really just what is the most effective way to grab data from a model with other related models when needing to return json. I read yesterday that its recommended to work the the DAO layer when working with arrays so for my example this is what i have so far.

I have a list of clients, each client HAS_MANY brands and each brand HAS_MANY projects. Not get a nicely formed array back of clients with thier brands heres what i have

$clients = Yii::app()->db->createCommand('select client.* from client where client.status = 1')->queryAll();

        foreach($clients as $ckey => $client)
        {
            $clients[$ckey] = $client;
            $brand_ids = Yii::app()->db->createCommand('select brand.id as brand_id, brand.client_id as b_client_id from brand where brand.client_id ='.$client['id'])->queryAll();

            foreach($brand_ids as $bkey => $brand_id)
            {
                $clients[$ckey]['brands'][] = $brand_id['brand_id'];
            }

    }

This is returning what i want so far but is it the most effective way to achieve what im after??

Solution 1

Setup Client model

class Client extends CActiveRecord
{    
    //...
    /**
     * @return array relational rules.
     */
    public function relations()
    {
            // NOTE: you may need to adjust the relation name and the related
            // class name for the relations automatically generated below.
            return array(
                    'brands' => array(self::HAS_MANY, 'Brand', 'client_id'),
            );
    }
    //...
    public function defaultScope() {
        return array('select'=>'my, columns, to, select, from, client'); //or just comment this to select all "*"
    }

}

Setup Brand model

class Brand extends CActiveRecord
{
    //...
    /**
     * @return array relational rules.
     */
    public function relations()
    {
            // NOTE: you may need to adjust the relation name and the related
            // class name for the relations automatically generated below.
            return array(
                    'client' => array(self::BELONGS_TO, 'Client', 'client_id'),
            );
    }
    //...
    //...
    public function defaultScope() {
        return array('select'=>'my, columns, to, select, from, brand'); //or just comment this to select all "*"
    }

}

Do client/brand search in your action function

$clients = Client::model()->with('brands')->findAllByAttributes(array('status'=>1));

$clientsArr = array();
if($clients) {
    foreach($clients as $client) {
        $clientsArr[$client->id]['name'] = $client->name; //assign only some columns not entire $client object.
        $clientsArr[$client->id]['brands'] = array();

        if($client->brands) {
            foreach($client->brands as $brand) {
                $clientsArr[$client->id]['brands'][] = $brand->id;
            }
        }

    }
}

print_r($clientsArr);
/*
Array (
    [1] => Array (
        name => Client_A,
        brands => Array (
            0 => Brand_A,
            1 => Brand_B,
            2 => Brand_C
        )
    )
    ...
)

*/

Is this you wanted? I realize, if you want select only brands ID (on no more data else) you could search by sql and GROUP_CONCAT (MySQL) and select all brand ids for client in one row separated with commas. 1,2,3,4,5,20,45,102.

Solution 2

If you don't want to use CActiveRecord using with() functionality, then you should write one SQL query joining brand table.

$rows = Yii::app()->db
    ->createCommand(
        'SELECT c.*, b.id as brand_id 
        FROM client c INNER JOIN brand b 
        WHERE c.status = 1 AND b.client_id = c.id')
    ->queryAll();
$clients = array();
foreach ($rows as row) {
    if (!isset($clients[$row['id']])) {
        $clients[$row['id']] = $row;
        $clients[$row['id']]['brands'] = array();
    }
    $clients[$row['id']]['brands'][] = $row['brand_id'];
}

This is much more efficient than doing one query to retrieve all clients and then doing N queries to fetch their brands (where N is the number of clients). You could also join your third table projects and retrieve all related projects for each brand.

Solution 3

I realize this is old, but I was looking for a solution myself and I thought it was a good one.

In my base Controller class (protected/Components/Controller.php) I added the following functions:

protected function renderJsonDeep($o) {
    header('Content-type: application/json');
        // if it's an array, call getAttributesDeep for each record
    if (is_array($o)) {
        $data = array();
        foreach ($o as $record) {
            array_push($data, $this->getAttributesDeep($record));
        }
        echo CJSON::encode($data);
    } else {
            // otherwise just do it on the passed-in object
        echo CJSON::encode( $this->getAttributesDeep($o) );
    }

        // this just prevents any other Yii code from being output
    foreach (Yii::app()->log->routes as $route) {
        if($route instanceof CWebLogRoute) {
            $route->enabled = false; // disable any weblogroutes
        }
    }
    Yii::app()->end();
}

protected function getAttributesDeep($o) {
        // get the attributes and relations
        $data = $o->attributes;
    $relations = $o->relations();
    foreach (array_keys($relations) as $r) {
            // for each relation, if it has the data and it isn't nul/
        if ($o->hasRelated($r) && $o->getRelated($r) != null) {
                    // add this to the attributes structure, recursively calling
                    // this function to get any of the child's relations
            $data[$r] = $this->getAttributesDeep($o->getRelated($r));
        }
    }
    return $data;
}

Now, calling renderJsonDeep on an object, or array of objects, will encode the object(s) in JSON including any of the relations you have pulled, like by adding them to the 'with' param in the DbCriteria.

If the child object has any relationships, those will be set in the JSON as well since getAttributesDeep is called recursively.

Hope this helps someone.

Comments

[Mike Waites]

Hey again galymzhan, seem to be answering all of my yii related topics!! This does seem a much more sensible approach, however on trying your example i was struggling to get the json returned nicely, php seems to do some funky stuff when converting arrays to json. Ill keep trying with your example to build the array as needed for the client request!