Sql To Cactivedataprovider

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)';


use falow ,inested sub query

$criteria=new CDbCriteria;

$criteria->select = 'player_id as player_id';

$criteria->condition = 'league_id = 1';


or use

    $ostatuses = Yii::app()->db->createCommand('SELECT option_code FROM options WHERE option_name=\'ISSUE-STATUS\' and sort_order<10')->queryAll();


    foreach ($ostatuses as $or) {




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();


    foreach ($ostatuses as $or) {




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(




             'id', 'username', 'email',







// $dataProvider->getData() will return a list of arrays.