How to write a query with GROUP BY and ORDER BY using Joomla's query methods?

Question

I have the following query that I need to convert to Joomla using JDatabase. This query executes as intended in the phpMyAdmin.

SELECT `id`, DATE_FORMAT(`estEventDate`, '%b %Y') AS estDate, COUNT(`id`) AS cnt FROM `jdev_events` GROUP BY estDate ORDER BY estDate DESC

I have tried many different ways, but with no luck. The first problem I faced was that I cannot alias the function but only the field in the $db->quoteName().

$query->selcet(array($db->quoteName('id'), 'DATE_FORMAT(' . $db->quoteName('estEventDate') . ', "%b %Y")' . ' AS estDate', 'COUNT(' . $db->quoteName('id') . ') as cnt'));

But even if I remove the aliases...

$query->selcet(array($db->quoteName('id'), 'DATE_FORMAT(' . $db->quoteName('estEventDate') . ', "%b %Y")', 'COUNT(' . $db->quoteName('id') . ')'));

it does not work.

Then I tried to set the SELECT object as string :

$db = JFactory::getDBO();
$query = $db->getQuery(true);
$query->select("`id`, DATE_FORMAT(`estEventDate`, '%b %Y') AS estDate, COUNT(`id`) AS cnt");
$query->from($db->quoteName('#__events'));
$query->group('estDate');
$query->order('estDate DESC');
$db->setQuery($query);
JLog::add('Query : ' . $query->__toString(), JLog::DEBUG, 'custom.field.estDate.getOptions');
$estDates = $db->loadObjectLsit();
JLog::add('EstDates' . print_r($estDates, 1), JLog::DEBUG, 'custom.field.estDate.getOptions');

Here are the log entries:

2018-11-06T00:37:36+00:00       DEBUG 127.0.0.1 custom.field.estdate.getOptions    Query : 
SELECT `id`, DATE_FORMAT(`estEventDate`, '%b %Y') AS estDate, COUNT(`id`) AS cnt
FROM `#__events`
GROUP BY estDate
ORDER BY estDate DESC
2018-11-06T00:37:36+00:00       DEBUG 127.0.0.1 custom.field.estdate.getOptions    EstDates :

I am out of ideas.

Answer 1

I see minor typos in each of your coding attempts (misspelled method names: selcet and loadObjectLsit).

Write the GROUP BY order like so: (Reference Link)

SELECT id, DATE_FORMAT(estEventDate, '%b %Y') AS estDate, COUNT(id) AS cnt
FROM #__events
GROUP BY estDate DESC

Implement like so: (no quoting calls are necessary for your query)

$db = JFactory::getDbo();
try {
    $query = $db->getQuery(true)
        ->select("id, DATE_FORMAT(estEventDate, '%b %Y') AS estDate, COUNT(id) AS cnt")
        ->from("#__events")
        ->group("estDate DESC");
    echo $query->dump();
    $db->setQuery($query);
    $resultset = $db->loadObjectList();
    if (!$resultset) {
        echo "No Rows In Resultset";
    } else {
        foreach ($resultset as $row) {
            echo "<div>";
                echo "<div>ID: {$row->id}</div>";
                echo "<div>EstDate: {$row->estDate}</div>";
                echo "<div>Count: {$row->cnt}</div>";
            echo "</div>";
        }
    }
} catch (Exception $e) {
    JFactory::getApplication()->enqueueMessage("Query Syntax Error: " . $e->getMessage(), 'error');    // never show to public

}