CODEWITHSUNDEEP
phpmysqlcakephpgroup-by
user1416247
user1416247

Reputation: 11

CakePHP Won't Apply Group By Condition

I am trying to do a find on one of my tables, and I'm dynamically adding conditions to the hasMany relationship from one model to the other. Everything works fine, except, Cake will not apply the group condition to the query. If I copy the query that is generated and run it in MySQL and add my Group By condition, it works beautifully. I have tried a number of things, to no avail, and the way that I have it now is setup just like the Find page in the Cake docs tell me to setup a group by. You can find how I'm doing it below:

$this->Project->hasMany['ProjectTime']['conditions'] = array('user_id'=>$this->Auth->user('id'), 'date_entry >= '.$firstDay.' AND date_entry <= '.$lastDay);
    $this->Project->hasMany['ProjectTime']['order'] = array('date_entry ASC');
    $this->Project->hasMany['ProjectTime']['group'] = 'ProjectTime.date_entry';
    $this->Project->hasMany['ProjectTime']['fields'] = array('ProjectTime.date_entry, ProjectTime.user_id, ProjectTime.project_id, SUM(ProjectTime.duration) AS durationTotal');        
    $result = $this->Project->find('all', array('conditions'=>array('Project.id IN (' . implode(",", $projectTimeArray) . ')')));

I have tried putting it directly in the hasMany array in the Model - nothing. I have tried putting an array around the group - nothing. I'm truly stumped, so if anyone can help I'd greatly appreciate it.

Upvotes: 1

Views: 887

Answers (2)

Kevin Weber
Kevin Weber

Reputation: 198

I know this answer is late, but I modified the core to allow group in hasMany. I am unsure why the CakePHP team has made the decision to do so and if anyone can provide insight to why they have, I would greatly appreciate it.

Line: 1730 of /lib/Cake/Model/Datasource/DboSource.php

        case 'hasMany':
            $assocData['fields'] = $this->fields($LinkModel, $association, $assocData['fields']);
            if (!empty($assocData['foreignKey'])) {
                $assocData['fields'] = array_merge($assocData['fields'], $this->fields($LinkModel, $association, array("{$association}.{$assocData['foreignKey']}")));
            }

            $query = array(
                'conditions' => $this->_mergeConditions($this->getConstraint('hasMany', $Model, $LinkModel, $association, $assocData), $assocData['conditions']),
                'fields' => array_unique($assocData['fields']),
                'table' => $this->fullTableName($LinkModel),
                'alias' => $association,
                'order' => $assocData['order'],
                'limit' => $assocData['limit'],
                'offset' => $assocData['offset'],
                'group' => $assocData['group'],
            );

The Value $assocData['group'] was added to the group key.

Upvotes: 1

RichardAtHome
RichardAtHome

Reputation: 4313

That's a very strange way to build your query :-S

It can be written this way (Assuming Project hasMany ProjectTime): 

$result = $this->Project->find('all', array(
    'conditions'=>array(
        'Project.id'=>implode(",", $projectTimeArray)
    ),
    'joins'=>array(
        array(
            'table'=>'project_times',
            'alias'=>'ProjectTime',
            'type'=>'INNER',
            'conditions'=>array(
                'ProjectTime.project_id = Project.id',
                'ProjectTime.user_id'=>$this->Auth->user('id'),
                'ProjectTime.date_entry >='=>$firstDay
                'ProjectTime.date_entry <=' => $lastDay
            ),
            'order'=>array('ProjectTime.date_entry'=>'ASC'),
            'group'=>array('ProjectTime.date_entry')
        )
    )
));

(Typed into editor, untested ;-)

Upvotes: 1

Related Questions