Yii dataProvider returns row data when relational table data is empty

Yii 1.1 General Discussion
1

Yii dataProvider returns row data when relational table data is empty in WITH joinType = INNER JOIN

I am doing relational search in Yii Gridview search function … I’m using this in Yii code… It is very dynamic so iam stripping all if condition … i’m just showing what type of query executed…

here base table is users as t for refrence… $this->venues = 2 …







$condition .= ($condition != "" ? "AND" : "" ).' (t.status = 0 OR t.status = 1) ' ;


$conditionVenues =  " venueUsers.status = 1 AND venueUsers.venue_id = '$this->venues'  AND  venueUsers.user_id = t.user_id "  ;


$criteria->mergeWith(array(

        'with'=>array('venueUsers'=>array('joinType'=>'INNER JOIN', 'select'=>"venue_user_id", "condition"=> $conditionVenues , 

        'with'=>array('venue'=>array('joinType'=>'INNER JOIN',   'select'=>"venue.name"))   )),

          

        'condition'=>$condition,


            ));

I’m getting results… But it is for LEFT JOIN may be… cause if it is INNER JOIN … then there should be no Rows who have empty venueUsers/… but i’m getting a row where is venue_user is empty…

What i’m trying to do is this type of query in GENERIC my sql i can get it… But when i go YII way… it do not works for me… Any suggestion … what i can do…

"My SQL GENERIC QUERY"








SELECT `users`.`user_id`, `vu`.`venue_user_id`

  FROM `users`

       LEFT JOIN `venue_users` `vu`

          ON     `vu`.`user_id` = `users`.`user_id`

             AND `vu`.`venue_id` = "2"

             AND `vu`.`status` = "1"

       INNER JOIN `venues` `v`

          ON `v`.`venue_id` = `vu`.`venue_id` AND `v`.`status` = 1

 WHERE `users`.`status` = 1

SEE ATTCHMENTS FOR MORE INFO