Cakephp 2.4.5 - Using Count with Sum in query

Question

I haven't really seen any cakephp examples of getting a Count value as a field in the query results. Here is what I did

    $searchfilter = array(
                        'Booking.bookingdate >=' => $date_period_current_start,
                        'Booking.bookingdate <=' => $date_period_current_end,
                        'Booking.merchant_id =' => $this->Session->read('Auth.ActiveMerchant')
                    );
    $fields       = array(
                    'COUNT(*) as reccount',
                    'SUM(Booking.pax) as totalpax',
                    );


    $bookings_period_current = $this->Booking->find('all', array('conditions' => $searchfilter, 'fields' => $fields)

    var_dump($bookings_period_current);

Now this seems to have worked fine, my var_dump produces:

array (size=1)
  0 => 
    array (size=1)
      0 => 
        array (size=2)
          'reccount' => string '7' (length=1)
          'totalpax' => string '28' (length=2)

I guess my questions are:

1) Is this the right way of doing a Count with cakephp? Note I'm not using the normal find('Count',...) method

2) in my $fields variable I specified COUNT(*) as reccount... Is this very inefficient? The Bookings table has several other fields but all i really want is the total number of records, and the sum of Booking.pax.

Cheers Kevin

As far i know, Yes this is the right way.... The formatting may look odd but you can use afterFind() on you model to reformat your result... — Fazal Rasel
– Fazal Rasel, Commented Apr 17, 2014 at 7:31

Kamil Kosiński · Accepted Answer · 2014-04-23 07:51:01Z

2

Right way for COUNT(*) is find('count'), but if You want to get more fields in the same query Your solution is ok.

You can also use:

a) Virtual fields

$this->Booking->virtualFields = array(
  'reccount' => 'COUNT(*)',
  'totalpax' => 'SUM(Booking.pax)'
); // or put it in Booking model
$this->Booking->find('all', array('fields' => array('reccount', 'totalpax')));

b) $this->Booking->query();

edited Apr 23, 2014 at 7:51

answered Apr 17, 2014 at 7:44

Kamil Kosiński

2962 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Cakephp 2.4.5 - Using Count with Sum in query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related