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.

up vote 4 down vote accepted

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

}
  • 1
    Well the problem was those typos. But after 16hrs on the monitor I just couldn't see them ...Thank you for your answer ! – mixahlos Nov 6 at 8:06

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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