dalf
(Dalfgan77)
June 9, 2011, 1:24pm
1
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
tri
(tri - Tommy Riboe)
June 9, 2011, 2:29pm
2
Did you try ‘together’ => true ?
/Tommy
dalf
(Dalfgan77)
June 9, 2011, 2:56pm
3
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
dalf
(Dalfgan77)
June 9, 2011, 5:40pm
4
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?
dalf
(Dalfgan77)
June 11, 2011, 1:55pm
5
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
tri
(tri - Tommy Riboe)
June 11, 2011, 2:40pm
6
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
dalf
(Dalfgan77)
June 13, 2011, 1:50pm
7
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
dalf
(Dalfgan77)
June 14, 2011, 5:54pm
8
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 ?
ferrancr
(Ferran)
October 23, 2011, 7:21pm
9
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);