how would I enter this into a CActiveDataProvider?
The search is in league.php (league model)
$sql='SELECT * FROM player
WHERE player.id IN (SELECT player_id
FROM league_has_player
WHERE league_id = 1) AND player.id NOT IN (SELECT player_id
FROM team_has_player
INNER JOIN team ON team.id = team_has_player.team_id
WHERE team.league_id = 1)';
hi
use falow ,inested sub query
$criteria=new CDbCriteria;
$criteria->select = 'player_id as player_id';
$criteria->condition = 'league_id = 1';
$subQuery=$this->getCommandBuilder()->createFindCommand($this->getTableSchema(),$criteria)->getText();
or use
$ostatuses = Yii::app()->db->createCommand('SELECT option_code FROM options WHERE option_name=\'ISSUE-STATUS\' and sort_order<10')->queryAll();
$ost=array();
foreach ($ostatuses as $or) {
$ost[]=$or['option_code'];
}
criteria->compare('status',$ost);
I don’t understand how this maps to my SQL statement. Can you explain how it does?
for your code :
$ostatuses = Yii::app()->db->createCommand('SELECT player_id
FROM league_has_player
WHERE league_id = 1')->queryAll();
$ost=array();
foreach ($ostatuses as $or) {
$ost[]=$or['option_code'];
}
criteria->compare('player.id',$ost);
I hope this is helpful
Hello Peter,
you could use CSqlDataProvider.
$count=Yii::app()->db->createCommand('SELECT COUNT(*) FROM tbl_user')->queryScalar();
$sql='SELECT * FROM tbl_user';
$dataProvider=new CSqlDataProvider($sql, array(
'totalItemCount'=>$count,
'sort'=>array(
'attributes'=>array(
'id', 'username', 'email',
),
),
'pagination'=>array(
'pageSize'=>10,
),
));
// $dataProvider->getData() will return a list of arrays.