New way to GROUP BY in CakePHP

Just recently ( end of May, 2008 ) there was an addition to CakePHP’s find method to easily build a GROUP BY in your SQL.

If you’ve been using something like $this->Product->find(‘all’… etc., etc. Now you can add a new key ‘group’ to your find method, like so:

$this->Product->find(‘all’,array(‘fields’=>array(‘Product.type’,’MIN(Product.price) as price’), ‘group’=> ‘Product.type’));

Of course you will have to upgrade to a nightly SVN core for this to work.

m4s0n501
  • http://www.personal.psu.edu/rja171 ragrawal

    hi,

    Thanks for the tip. I have been using CakePHP for almost more than a year but never found a clean solution to “group by” problem, until today.

  • teknoid

    @ragrawal

    Good to hear. You’re welcome.

  • Olamma

    Hi i tried the new group by as you have it above but for some reason it doesnt seem to work for me.

  • teknoid

    @Olamma

    It’s possible that you are using an older version of CakePHP, where this option is not supported.

  • SimonJPA

    I prefer to do it this way. Much more easy to do and to understand:

    $picos = $this->Pico->query(“SELECT * FROM picos WHERE ativo_id =3 AND praia_id = “. $id .” GROUP BY DATE”);
    $this->set(‘picos’, $picos);

  • teknoid

    @SimonJPA

    And there are a few reasons NOT to do it this way.
    1. Your SQL should never be in the controller
    2. You are not leveraging the framework
    3. You query is not escaped properly and is prone to SQL injection

    … for more details check on IRC channel or even search here, why query() should be avoided for the vast majority of cases.

  • sk8terboi

    Thanks a lot!!!!!!!!!
    This above simple thingy helped me a lot when i needed it ^_^