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

Question

I'm 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 it's 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, where each client HAS_MANY brands and each brand HAS_MANY projects. I'm not getting a nicely formed array back of clients with their brands. Here's 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 I'm after?

Answer 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.