2

I used to get multiple COUNT in one query using the SUM(CASE WHEN

$db =& JFactory::getDBO();
$query = "SELECT
        SUM(CASE WHEN last_name LIKE 'A%' THEN 1 ELSE 0 END) as alpha,
        SUM(CASE WHEN last_name LIKE 'B%' THEN 1 ELSE 0 END) as beta
        FROM '#__mytable'"; 
$db->setQuery($query);
$results = $db->query();
while($row = mysqli_fetch_array($results)){
    $alpha = $row['alpha'];
    $beta = $row['beta'];
}
echo "ALPHA: ".$alpha;
echo "<br />BETA: ".$beta;

Is there a way to do it in one query with JDatabase and avoid multiple single queries:

$db =& JFactory::getDBO();
$query = $db->getQuery(true);
$query->select( array('COUNT(id)'))->from($db->quoteName('#__mytable'))->where($db->quoteName('last_name') . ' LIKE ' .$db->quote('A%'));
$db->setQuery($query);
$alpha = $db->loadResult();

$query = $db->getQuery(true);
$query->select( array('COUNT(id)'))->from($db->quoteName('#__mytable'))->where($db->quoteName('last_name') . ' LIKE ' .$db->quote('B%'));
$db->setQuery($query);
$beta = $db->loadResult();

echo "ALPHA: ".$alpha;
echo "<br />BETA: ".$beta;

If not what would be the best approach for multiple COUNT in one query?

1

You can shorten, and reuse the query:

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select(array('COUNT(id)'))
    ->from($db->qn('#__mytable'))
    ->where($db->qn('last_name') . ' LIKE ' . $db->q('A%'));
$db->setQuery($query);
$alpha = $db->loadResult();
// you can reuse the object, but not the where-clause:
$query->clear('where')
    ->where($db->qn('last_name') . ' LIKE ' . $db->q('B%'));
$db->setQuery($query);
$beta = $db->loadResult();
echo 'ALPHA: '.$alpha;
echo '<br />BETA: '.$beta;

I think this is a good compromise. And your single query IMHO is not really a cheap procedure compared to this.

Also some hints:

  • In the first line, you should not use the & any more at JFactory::getDbo().

  • Use simply ' instead of " for any string operations, then there is no extra parsing through the string if possible.

  • You may use $db->qn() instead of $db->quoteName() and $db->q() for $db->Quote()

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.