DataProvider with Count on Relationship

Yii 1.1 General Discussion
1

I have a table of countries (Country) and a table of clubs (Club) that belong to a country. Each club also belongs to a user(User). The club table has a Country_ID and User_ID. I want to display a form with the list of countries and the number of clubs that are not assigned to a user (User_Id>1) in a country. I hope that I have explained that correctly.

selectCountry.php -the view file




<?php 

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

        'id'=>'some-grid',

        'dataProvider'=>$dataProvider,

        'columns'=>array(

        'id',

        'name',

        'time',

	'clubCount',

        array(

         'class'=>'CCheckBoxColumn',

         'selectableRows' => '1',

        ),

	)));

How do I define the dataprovider in SiteController.php so that I can get a count of clubs where the User_Id is > 1




   public function actionSelect()

	{

		$model=new SelectCountryForm;

		$countryModel = new Country;

          ..........

               $dataProvider = new CActiveDataProvider($countryModel, array(

                    'criteria'=>array(                 

                        'order'=>'t.name ASC',

                         'with'=>array('club'),

                         'together'=>true,

			 'group'=>'t.id',

                        ),

                    )

                );

		// display the select country form

		$this->render('selectCountry',array('model'=>$model,'dataProvider'=>$dataProvider));

For reference, the SQL query that works looks like this:




SELECT t.id, t.name, t.time, COUNT( c.User_Id ) 

FROM tbl_country AS t

JOIN tbl_club AS c

WHERE c.Country_Id = t.id
2

Did you try something like that ?




   public function actionSelect()

	{

		$model=new SelectCountryForm;

		$countryModel = new Country;

          ..........

               $dataProvider = new CActiveDataProvider($countryModel, array(

                    'criteria'=>array(

                         'select' => 't.id, t.name, t.time, COUNT( c.User_Id ) as cpt',

                         'order'=>'t.name ASC',

                         'with'=>array('club'),

                         'together'=>true,

			 'group'=>'t.id',

                        ),

                    )

                );

		// display the select country form

		$this->render('selectCountry',array('model'=>$model,'dataProvider'=>$dataProvider));
3

Thanks. That did work.

Modified the criteria to join the tables on Country_Id


 'criteria'=>array(

                         'select' => 't.id, t.name, t.time, COUNT( uid ) as clubCount',

                         'order'=>'t.name ASC',

                         'join'=>'LEFT JOIN Club ON t.id=Club.cid',

                         'group'=>'cid',

                        ),

And I get this error…

EDIT: Fixed the error by adding the ‘tbl’ prefix.

However the query does not yield the expected result.

4

what the new result looks like ?

5

The result set returns a 0 for the club count instead of 8.

I need to figure out how to translate the query to Yii.

6

Got the query to work by removing the group criteria.

Now the issue I have is to represent the "clubCount" field. This is defined in the criteria of the data provider, but if try to use it as a column in the cgridview i get the following error message.

Property "Country.clubCount" is not defined.

I have tried to prefix the clubCount parameter with the table name, but it does not work. :(

7

To rephrase my issue.

I need to display a result of a SQL function (count) on a field/column from a related table in a cgridview.

This is my query:




SELECT `t`.`idCountry` AS `t0_c0`, `t`.`nameCountry` AS `t0_c1`, `t`.`time` AS `t0_c3`, COUNT(userID) as clFree, `clubs`.`idClub` AS `t1_c0`, `clubs`.`nameClub` AS `t1_c1`, `clubs`.`shortNameClub` AS `t1_c2`, `clubs`.`userID` AS `t1_c3`, `clubs`.`leagueID` AS `t1_c4`, `clubs`.`countryID` AS `t1_c5` FROM `tbl_country` `t` LEFT OUTER JOIN `tbl_club` `clubs` ON (`clubs`.`countryID`=`t`.`idCountry`) WHERE (userID=1) LIMIT 20

which is generated by the following code in the controller:




$dataProvider=new CActiveDataProvider('Country', array(

			'criteria'=>array(

				'select' => 't.idCountry, t.nameCountry, t.time, COUNT(userID) as clFree',

				'with'=>array('clubs'),

				'together'=>true,

				'condition'=>'userID=1',

			),

			'pagination'=>array(

				'pageSize'=>20,

			),

		));

Now, all I need to do is represent the clFree value in the Cgridview.




<?php $form=$this->widget('zii.widgets.grid.CGridView', array(

			'dataProvider'=>$dataProvider,

			'columns'=>array(

				'nameCountry',          // display the 'name' of the country

				'time',  				// display the 'time' 

				array(

                        'name'=>'clubsFree', 

                        'value'=>'$data->clFree',

                ),

			),

		));

However, the column called clubsFree does not show any value, when it should ideally show a number, as the SQL query does provide me with a valid result. In this case, I get an error message stating that Country.clFree does not exist.

Please help.

8

Because Country model doesn’t have an attribute named “clFree”.

CActiveRecord doesn’t create attributes for calculated columns automatically. You should either add a public property called “clFree” to Country model or define a STAT relation for it.