CGridView not showing some rows

Yii 1.1 General Discussion
1

My app is for a tech support shop and we need to do reports of the hours for each tech. I’m using CGridView to display this information.

CActiveDataProvider:




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

 'criteria'=>array(

  'alias'=>'j',

  'select'=>'concat(tracker.first_name, " ", tracker.last_name) as tech_name, ifnull(sum(tech_time),0) as tech_time, ifnull(sum(over_time),0) as over_time, ifnull(sum(travel_time),0) as travel_time, ifnull(sum(itinerant_time),0) as itinerant_time, ifnull((sum(tech_time) + sum(over_time) + sum(travel_time) + sum(itinerant_time)),0) as total',

  'join'=>'right outer join tracker on tracker.tracker_id = j.tech_tracker_id',

  'condition'=>$condition,

  'group'=>'tracker.login_id',

 ),

 'sort'=>$sort,

 'pagination'=>array(

  'pageSize'=>20,

 ),

));

I would use the ‘with’ criteria if I knew how to specify a right join in this case, so if anyone can show me how to do that in the ‘with’=>array(‘tracker’) part of CDbCriteria, I’d be interested in seeing that.

In any case, this query appears to work in MySQL, but it won’t show up in the CGridView. I thought maybe the null values were causing a problem, so I added the ifnulls to see if that would help… it doesn’t.

The interesting thing is that the gridview shows this at the top: “Displaying 1-7 of 18 result(s).” The page size is set to 20, so there’s no excuse I can come up with.

The way this works is that I’m pulling up all the journals that were entered during a specific time period and grouping them by the tracker id. But this leaves out anyone who didn’t enter any journals on any jobs during the period, which shouldn’t really happen much, but ya gotta account for it. This is why I am doing a right outer join to ensure that every technician is represented.

Does anyone have any ideas why the CGridView is ignoring some of these rows?

Thanks,

Jaz

2

Bumping this back up… I could still use a solution.

Let me see if I can simplify the problem.

I’m dealing with two database tables:

Tracker

tracker_id

tracker_name

Journal

journal_id

job_id

tracker_id

hours

entry_date

I’m trying to create a DataProvider based on the Journals.




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

 'criteria'=>array(

  'alias'=>'j',

  'select'=>'tracker.name, sum(j.hours) as hours',

  'join'=>'right outer join tracker on tracker.tracker_id = j.tracker_id',

  'group'=>'tracker.name',

 ),

));

//in mysql it should look like this

select tracker.name, sum(j.hours)

from journal j right outer join tracker on tracker.tracker_id = j.tracker_id

group by tracker.name

The mysql query gives me one result for each tracker and the sum of their hours.

When I pass this dataProvider to CGridView, I only get the records where there were journals for the given tracker. In mysql the hours field would be NULL, which I want to show as 0. CGridView ignores these rows.

What gives?

3

it is may be problem with Yii couse canot $criteria->select=‘max(), concat(), min()’;