Cactivedataprovider Join Problem

Hello guys,

I have a problem with extracting data from a joined table using CActiveDataProvider and I couldn’t find the fix for it even after hours of internet search, so finally I am here asking for help.

The scenario goes this way:

My page have to display all the existing users(User table) along with the friendship invitations(Invitation table) sent by me to each user.

So the relations for UserModel is here:


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(

			'invitation' => array(self::HAS_MANY, 'Invitation', 'friend_id'),

		);

	}

Up above in the relations function I try to relate Invitation table to user that got an invitation sent by me.

In the Controller I have:


$criteria = new CDbCriteria();

			$criteria->select = '*, invitation.id as invitid';

			$criteria->with = array(

			  'invitation' =>array(

				'joinType' => 'LEFT JOIN',

				'on' => 'invitation.id_user = :userId',

				'params' => array(':userId' => Yii::app()->user->id),

			   )

			);

			$users = new CActiveDataProvider('Users', array(

			                                          'criteria' => $criteria,

			                                          'pagination' => array(

				                                               'pageSize' => 15,

			                                               ),

			                                          ));

In my View I have :


$this->widget('zii.widgets.CListView', array(

			                         'dataProvider' => $users,

			                         'itemView' => '_usersView',

			                         'template' => '{items} {pager}',

			                     ));

Now I get all the users along with the Invitation columns, but my problem is that I get no data for Invitation Columns. When I try to access the invitation id($data->invitation->id) I get nothing and my database has this information:

My account information from User table:

id: 80

fullname: adrian holobut

email: adrian.holobut@gmail.com

data_created: 2013-01-23 13:20:33

My friend information row from User table

id: 1

fullname: Andrei Nartea

email: duca14036@gmail.com

data_created: 2011-10-11 00:00:00

Invitation table:

id: 40

user_id: 80

friend_id: 1

data: 2013-01-24 07:38:01

code: a6dcea5f6fa917ebce77519aed193b50

So I expect to get along with my friend information, the invitation table row from above.

Thanks,

Adrian.

Since this is HAS_MANY relation you need to add ‘together’=>true option so Yii AR will try to fetch all data in single query instead of two separate queries (one for main objects and second for related data)

I have tried using together = true also, but it doesn’t return anything from the joined table columns, though there is related information. This is so awkward because it’s such a simple join and I get stuck. I am pretty familiar with Yii, but it’s not cool when I stumble across these problems. Here is the query got from the logged message, maybe it helps:


Querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `users` `t`  LEFT JOIN

`invitation` `invitation` ON (`invitation`.`id_friend`=`t`.`id`) AND

(invitation.id_user = :userId) . Bound with :userId='80'

Thanks for the answer.

By the way, can I use CListView Widget with the information returned from CArrayDataProvider? I might use DAO or AR to get the information from database

Did you tried to run this query on database (with phpMyAdmin or something like this)? try to figure out why it is not working and this should lead you to solution.

as for carraydataprovider and clistview - sure you can use them together.

Yes, redguy. I’ve tested this query with the AR and i got the expected values, so the query works fine using another method. I am already tired to search for clues to this problem and the chances are that I’ve made some mistakes because I’ve been using CActiveDataProvider for a while and I didn’t get any unexpected problems. I will use The CArrayDataProvider for now.

Thanks anyway.