Count and Right join

Hey guys

I’m facing a problem and I hope someone can help me.

I have 2 tables :

• tbl_prono (id, game_id, …) : there are 2 rows of data

• tbl_game (id, date, team_id,…) : there are 3 rows of data

In my model Prono I did the following relation

return array(

'game'=>array(self::BELONGS_TO, 'Game', 'game_id'),

);

I create the following dataprovider in an action (PronoController) :

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

   'criteria'=>array(

       'with'=>array('game'=>array('joinType'=>'RIGHT JOIN')),   // please note the RIGHT JOIN

 )));


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

The sql query generated is correct and return 3 rows (because there are 3 rows in table tbl_game) if I execute this query in phpmyadmin.

But in my view I can only see 2 rows!!

Why?

Well if I look at the log, I can see this query (automatically generated by system.db.CDbCommand) :

Querying SQL: SELECT COUNT(DISTINCT `t`.`id`) FROM `tbl_prono` `t`  RIGHT

JOIN `tbl_game` `game` ON (`t`.`game_id`=`game`.`id`) 

This COUNT return 2 ! Which is correct, because I have only 2 rows in tbl_prono.

So instead of "COUNT (DISTINCT t.id)" I want "COUNT (DISTINCT *)" because I want to see 3 rows in my view! But how can I do this ?

Thanks

Did you try ‘together’ => true ?

/Tommy

Yes, I tried that but no difference

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

   'criteria'=>array(

     'with'=>array('game'=>array('joinType'=>'RIGHT JOIN')),

     'together' => true,

)));

Any other suggestion?

I tried to find a similar problem on google, but nothing

Additional information :

For testing only, I modified CaActiveFinder.php at line 751, function count:

//before

$query->selects=array("COUNT(DISTINCT $column)");

//after

$query->selects=array("COUNT( *)");

Now I can see in the log file the following query

Querying SQL: SELECT COUNT( *) FROM `tbl_prono` `t`  RIGHT JOIN `tbl_game`

`game` ON (`t`.`game_id`=`game`.`id`) 

This query is good because the result is 3.

But I still see only 2 rows in my CGridView.

Is it possible that the 3rd row is not diplayed because the column "tbl_prono.id" is null?

Maybe CGridView doesn’t like null primary key?

no one has a solution ? Is it a bug from the framework ?

I may have to change all my code. Have to think about it

At least an outer join of game with prono should work (instead of prono with game). That is you’ll have to base the dataprovider on Game instead of Prono.

Edit:

Would this be a HAS_ONE relationship? That should work.

(For a HAS_MANY relationship using CGridView, one solution might be to display related records in a one column partial.)

/Tommy

Yep I’ll use the GameController instead of PronoController

Thanks you.

But if anyone has a solution, it will still be interesting to know how to do that

I didn’t give up yet.

I check again this problem and here is another piece of information:

Maybe the problem come from ActiveDataProvider because $dataProvider.getTotalItemCount() returns 2! Why not 3 ?

Perhaps the problem is how to mount the structure DataProvider objects of CArchiveRecord.

In your case creates a Promo which relates to the respective objects of Game CArchiveRecord

That is:

Promo-> Game.

In objets:

promo1->game1

promo2->game2

And that’s why this will return 2, because, the first Model only has 2.

I guess that does not create a 3ro CArchiveRecord Promo with all attributes to empty but with its corresponding relations to Game (game3)

promo3(empty)->game3

One solution is use CSqlDataProvider and with the

totalITemCount = $model->countBySql("select count(*) etc…",$params);