Development

HowtoSelectDistinct

You must first sign up to be able to contribute.

i needed for one of my projects a function to SELECT DISTINCT types form the database, so i added following function

  public function getDistinctTypes()
  {
    $con = Propel::getConnection(self::DATABASE_NAME);
    $sql = "SELECT DISTINCT type FROM ".self::TABLE_NAME." ORDER BY type ASC";
    $rs = $con->executeQuery($sql, ResultSet::FETCHMODE_NUM);

    $result = array();
    while ($rs->next())
      {
        $result[] = $rs->getString(1);
      }

    return $result;
  }

afaik the correct way with propel is

<?php
  public function getDistinctTypes()
  {
    $crit=new Criteria();
    $crit->addAscendingOrderByColumn(TablenamePeer::TYPE);
    $crit->setDistinct();
    return TablenamePeer::doSelect($crit);
  }

autant pour moi

DISTINCT results with Propel

You cannot hydrate objects from your models with just one column, but you can use the doSelectRS() function to return a resultset:

<?php

function getDistinctImageTypes() 
{
  $c = new Criteria();
  $c->clearSelectColumns();                      // remove all columns
  $c->addSelectColumn(ImagesPeer::TYPE);         // use your own peer + column here, obviously ;-)
  $c->setDistinct();                             // add DISTINCT to the query
  $res = ImagesPeer::doSelectRS($c);             // the doSelectRS function from your peer
  $data = array();
  while ($res->next()) // loop through the records
  {
    $data[] = $res->getString(1);                // fill up an array with the 1st column values
  }
  return $data;                                  // send it back
}

?>

The SQL generated:

SELECT DISTINCT images.TYPE FROM images

The value returned by the function:

Array
(
   [0] => 1
   [1] => 2
)

Another example, with a join to get the type of image rather than the id of the type:

<?php

function getDistinctImageTypes() 
{
  $c = new Criteria();
  $c->clearSelectColumns();
  $c->addSelectColumn(ImagesPeer::TYPE);
  $c->addSelectColumn(ImagetypesPeer::TYPE);     // the table column that we want from the join
  $c->addJoin(ImagesPeer::TYPE, ImagetypesPeer::ID, Criteria::LEFT_JOIN);
  $c->setDistinct();
  $res = ImagesPeer::doSelectRS($c);
  $data = array();
  while ($res->next())
  {
    $data[] = $res->getString(2);                // fill up an array with the 2nd column values
  }
  return $data;
}

?>

The SQL generated:

SELECT DISTINCT images.TYPE, imagetypes.TYPE FROM images LEFT JOIN imagetypes ON (images.TYPE=imagetypes.ID)

The value returned by the function:

Array
(
   [0] => site
   [1] => plot
)

Hope it helps :-)