CODEWITHSUNDEEP
phpmysqlsqlcakephp
Zaphod Beeblebrox
Zaphod Beeblebrox

Reputation: 562

cakephp - using group by year, month

I have three tables, branches, children and invoices - children hasmany invoices. And branches hasmany children

I'm trying to allow a user to query the database to get all the invoices for children which are in a given branch, also the user may have entered a date range. Either way, I want to select all the invoices and have them grouped so that they are easy to display in a table per month. ie, user wants all invoices from jan 2013-> jan 2014. Then I display all the invoices but grouped so all jan2013 are together, feb2013 etc... (also, if there was an easy to get the sum total of the invoices for each month somehow that would be cool)

At the minute I have:

$conditions['Child.branch_id'] = $branch_id;
if($from != '') $conditions['Invoice.created >='] = $from;
if($to != '') $conditions['Invoice.created <='] = $to;
$fields = array('Invoice.*', 'Child.*', 'YEAR(Invoice.created) as year', 'MONTH(Invoice.created) as month');
$group = array('YEAR(Invoice.created)', 'MONTH(Invoice.created)');
$order = array('Invoice.created');
$kidsInvoices = $this->Invoice->find('all', array(  'conditions'=>$conditions,
                                            'fields'=>$fields,
                                            'group'=>$group,
                                            'order'=>$order));

But it returns only 2 records, when it should return all which would be 5. I noticed that of my 5 invoices they span only 2 months, so I'm guessing my incorrect code is just getting one invoice per month.

Can anyone tell me how I can get the results I want?

Upvotes: 1

Views: 1790

Answers (1)

Kai
Kai

Reputation: 3823

If I understand what you want to do correctly, you're looking for order, not group. Group by will make all the rows with have the same results for each of the given columns compress down to one row, resulting in the fewer rows you're seeing in your results.

 $order = array('year', 'month');
 $kidsInvoices = $this->Invoice->find('all', array(  'conditions'=>$conditions,
                                        'fields'=>$fields,
                                        'order'=>$order));

Upvotes: 1

Related Questions