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