Loud Baking

Even though CakePHP’s model already includes many of the database query functions, I found that the SELECT DISTINCT was missing. Ok, I know that you can always do it using either Model->query('SELECT DISTINCT c1, c2) or Model->findAll(null, 'DISTINCT c1, c2‘) but that would be like saying use Model->query() instead of Model->findAll().

The cool thing in CakePHP is that you can add your own functions to use in your app on top of the ones that come bundled in the core. For the Model, you just create an ‘app_model.php’ file that you place in our app’s main folder. The empty file should look like this:

/**
 * Custom AppModel that adds functionality to the core Model
 */
class AppModel extends Model
{
   //empty
}

Now inside your new AppModel class, add the following function:

   /**
    * Returns a resultset array with DISTINCT fields from database matching given conditions.
    *
    * @param   mixed    $conditions SQL conditions as a string or as an array('field' =>'value',...)
    * @param   mixed    $fields Either a single string of a field name, or an array of field names
    * @return  array    Array of records
    */
   function findDistinct($conditions = null, $fields = null)
   {
      $db =& ConnectionManager::getDataSource($this->useDbConfig);


  $str = 'DISTINCT ';
  if (!is_array($fields))
  {
     $str .= '`' . $fields . '`';
  }
  else
  {
     foreach ($fields as $field)
     {
        $str .= '`' . $field . '`, ';
     }
     $str = substr($str, 0, -2);   
  }

  $queryData = array(
                 'conditions'   => $conditions,
                 'fields'       => $str,
                 );
  $data = $db->read($this, $queryData, false);

  return $data;      


}

You can now use Model->findDistinct('c1') or Model->findDistinct(array('c1', 'c2', 'c3')) to retrieve DISTINCT columns values.

Hoping you find it useful.

This entry was posted on Friday, August 31st, 2007 at 4:30 am and is filed under Snippets, CakePHP, Coding, MySQL. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.