Many Many, Relations, specify columns to select

Yii 1.1 General Discussion
1

Hi all,

Newbie here.

I have three tables: tbl_facility, tbl_type, tbl_facility_type, you can tell that Facility and Type is many to many relation. tbl_facility_type has only two columns (type_id, facility_id), which makes the composite PK.

In model Facility, relations:

‘types’=>array(self::MANY_MANY, ‘Type’, ‘tbl_facility_type(facility_id, type_id)’),

In model Type, relations:

‘facilities’=>array(self::MANY_MANY, ‘Facility’, ‘tbl_facility_type(type_id,facility_id)’),

Now, I am trying to write an API that query certain[offset: 0, limit=10] facilities: Each facility has (type_id=1) as one of its Types. And, during the query, I only want to select tbl_facility.id, tbl_facility.name.

Here is the code I am working on:

$type=Type::model()->findByPk($_GET[‘type’]);

$models=$type->facilities(array(

							'offset'=>$_GET['offset'],


							'limit'=>$_GET['limit'],


							'select'=>array('id', 'name'),


							


								));

It does not work. Could anyone point me to the right direction? Thanks a lot.

2

SELECT facilities.id AS t1_c0, facilities.name AS t1_c3 FROM tbl_facility facilities INNER JOIN tbl_facility_type facilities_facilities ON (facilities_facilities.type_id=:ypl0) AND (facilities.id=facilities_facilities.facility_id) LIMIT 10

I YiiLoged the sql statement, and run it in phpMyAdmin, it does select the columns I wanted. But the API class has all the columns returned…

Really confusing.

3

Hi CullenSUN, welcome to the forum.

Do you mean that ‘offset’ and ‘limit’ don’t work? Or is the problem only with ‘select’?

And what about the other fields than ‘id’ and ‘name’? Do they have values retrieved from db?

You would be interested in this "tip" from the guide:

http://www.yiiframework.com/doc/guide/1.1/en/database.ar#creating-record

4

Thanks, for your reply.

They yii style code do give me all the rows. Offset and Limit both work ok, but the select is not functioning.

The fetched results has all the columns of the tbl_facility.

While I copy the logged statement to phpmyAdmin, the sql statement do give me the selected columns…

So my question is why yii’s relation’s select is not functioning?

5

Just for clarification, do you mean that you took a sql statement for the following AR action from the log, and it only contains ‘id’ and ‘name’?




$models=$type->facilities(array(

        'offset'=>$_GET['offset'],

        'limit'=>$_GET['limit'],

        'select'=>array('id', 'name'),

));

Or does the sql in the log contain the other fields?

6

//the following are the sql I took from yii log, and run in phpmyAdmin, it works as expected, only listed the columns I wanted.

SELECT facilities.id AS t1_c0, facilities.name AS t1_c3, facilities.full_address AS t1_c10, facilities.status AS t1_c19, facilities.checkins AS t1_c18 FROM tbl_facility facilities INNER JOIN tbl_facility_type facilities_facilities ON (facilities_facilities.type_id=:ypl0) AND (facilities.id=facilities_facilities.facility_id) LIMIT 12. Bound with :ypl0=‘4’

Latest update: the following is the API function. I found that the response I got from ajax calling this api has all the columns for tbl_facility. Well, the selected columns have real values and leave all the rest columns =null.

public function actionList()

{


 





    //$this->_checkAuth();


	 // Get the respective model instance


     if(isset($_GET['type'])){


	$type=Type::model()->findByPk($_GET['type']);


	$models=$type->facilities(array(


							'offset'=>$_GET['offset'],


							'limit'=>$_GET['limit'],


							'select'=>array('id', 'name','full_address','status','checkins'),


							


								));








   }else{





    $this->_sendResponse(500, 


                sprintf('Error: Parameter <b>type</b> is missing') );


   }





           


    // Did we get some results?


    if(empty($models)) {


        // No


        $this->_sendResponse(200, 


                sprintf('No items where found for model Facility') );


    } else {


        // Prepare response


        $rows = array();


        foreach($models as $model)


            $rows[] = $model->attributes;


        // Send the response


        $this->_sendResponse(200, CJSON::encode(array('count'=>count($rows),'objects'=>$rows)));


    }








}
7

So I am wondering why the result did not wipe away all the null columns.

8

Well, it seems to me that all is functioning as expected.

Probably you should note that retrieved relational data are instances of AR object. They should have all the attributes of the class. You may find some of the attributes left with null (or the default value for the field) when you have omitted them from the select clause. But they are still there because they have been created when the object has been instantiated.

9

Thanks.

In this case, the benefit using ‘select (columns)’ over ‘select *’ is still valid here in sense of speed, indexing…