Uncategorized

Sometimes you may need to use count with the index column rather than *, because of some dark corners of sql driven databases. Though this is somehow rare, this is how you could do it with Doctrine … this example is part of a symfony 1.4 project(place the file in any of the /lib dirs … app ones or global one):

<?php

class Custom_Doctrine_Query extends Doctrine_Query {

public static function create($conn = null, $class = null)
{
if ( ! $class) {
$class = 'Custom_Doctrine_Query';
}
return new $class($conn);

}

public function getCustomCountSqlQuery()
{
// triggers dql parsing/processing
$this->getSqlQuery(array(), false); // this is ugly

// initialize temporary variables
$where   = $this->_sqlParts['where'];
$having  = $this->_sqlParts['having'];
$groupby = $this->_sqlParts['groupby'];

$rootAlias = $this->getRootAlias();
$tableAlias = $this->getSqlTableAlias($rootAlias);
$primaryKey = $tableAlias . '.' .  $this->_queryComponents[$rootAlias]['table']->getColumnName( $this->_queryComponents[$rootAlias]['table']->getIdentifier());

// Build the query base
$q = 'SELECT COUNT(' .  $primaryKey . ') AS ' . $this->_conn->quoteIdentifier('num_results') . ' FROM ';

// Build the from clause
$from = $this->_buildSqlFromPart(true);

// Build the where clause
$where = ( ! empty($where)) ? ' WHERE ' . implode(' ', $where) : '';

// Build the group by clause
$groupby = ( ! empty($groupby)) ? ' GROUP BY ' . implode(', ', $groupby) : '';

// Build the having clause
$having = ( ! empty($having)) ? ' HAVING ' . implode(' AND ', $having) : '';

// Building the from clause and finishing query
if (count($this->_queryComponents) == 1 && empty($having)) {
$q .= $from . $where . $groupby . $having;
} else {
// Subselect fields will contain only the pk of root entity
$ta = $this->_conn->quoteIdentifier($tableAlias);

$map = $this->getRootDeclaration();
$idColumnNames = $map['table']->getIdentifierColumnNames();

$pkFields = $ta . '.' . implode(', ' . $ta . '.', $this->_conn->quoteMultipleIdentifier($idColumnNames));

// We need to do some magic in select fields if the query contain anything in having clause
$selectFields = $pkFields;

if ( ! empty($having)) {
// For each field defined in select clause
foreach ($this->_sqlParts['select'] as $field) {
// We only include aggregate expressions to count query
// This is needed because HAVING clause will use field aliases
if (strpos($field, '(') !== false) {
$selectFields .= ', ' . $field;
}
}
// Add having fields that got stripped out of select
preg_match_all('/`[a-z0-9_]+`\.`[a-z0-9_]+`/i', $having, $matches, PREG_PATTERN_ORDER);
if (count($matches[0]) > 0) {
$selectFields .= ', ' . implode(', ', array_unique($matches[0]));
}
}

// If we do not have a custom group by, apply the default one
if (empty($groupby)) {
$groupby = ' GROUP BY ' . $pkFields;
}

$q .= '(SELECT ' . $selectFields . ' FROM ' . $from . $where . $groupby . $having . ') '
. $this->_conn->quoteIdentifier('dctrn_count_query');
}

return $q;
}

public function count($params = array())
{
$q = $this->getCustomCountSqlQuery();
$params = $this->getCountQueryParams($params);
$params = $this->_conn->convertBooleans($params);

if ($this->_resultCache) {
$conn = $this->getConnection();
$cacheDriver = $this->getResultCacheDriver();
$hash = $this->getResultCacheHash($params).'_count';
$cached = ($this->_expireResultCache) ? false : $cacheDriver->fetch($hash);

if ($cached === false) {
// cache miss
$results = $this->getConnection()->fetchAll($q, $params);
$cacheDriver->save($hash, serialize($results), $this->getResultCacheLifeSpan());
} else {
$results = unserialize($cached);
}
} else {
$results = $this->getConnection()->fetchAll($q, $params);
}

if (count($results) > 1) {
$count = count($results);
} else {
if (isset($results[0])) {
$results[0] = array_change_key_case($results[0], CASE_LOWER);
$count = $results[0]['num_results'];
} else {
$count = 0;
}
}

return (int) $count;
}

}

The usage of the above class should be something like this:

$query = Custom_Doctrine_Query::create()->from('TargetTable t');
$count = $query->count();

or the bulky way omitting the static create method:

$query = Doctrine_Query::create(null, 'Custom_Doctrine_Query')->from('TargetTable t');
$count = $query->count();

The result of the above queries should be something like “SELECT COUNT(index_field) from target_table”.