I need to transform a simple php query into a query that JDATABASE will understand.

Here is the example:

$resDays        = 7;                // On how many days to build the top?
$resLimit       = 20;               // How many results to display?

$query = "SELECT `artist`, title, count(*) AS tracks FROM `history
          WHERE TIMESTAMPDIFF(DAY, `date_played`, NOW()) <= " . $resDays . "
          AND `song_type`=0 GROUP BY title ORDER BY tracks DESC LIMIT 0," . $resLimit;

And this is what I managed to do:

// Create a new query object.
$query = $db->getQuery(true);
$query->select($db->quoteName(array('artist', 'title')));

$result = $db->loadObjectList();

The problem is that I don't know how to include in my query the missing parts like ORDER tracks or count(*) AS tracks.

2 Answers 2

$query = $db->getQuery(true);
$query->select($db->quoteName(array('artist', 'title')));
$query->select('COUNT(*) AS tracks');
$query->where('TIMESTAMPDIFF(DAY, date_played, NOW()) <= ' . $db->quote($resDays));
$query->where('song_type = 0');
$query->order('tracks DESC');

// Set the query + limit
$db->setQuery($query, 0, $resLimit);

You can test how your query looks like with $query->dump();

API: https://api.joomla.org/cms-3/classes/JDatabaseQuery.html

  • Just a note. If you're using Joomla 3.x, you use ->setLimit(20); rather than defining it in setQuery
    – Lodder
    Jul 28, 2015 at 10:05
  • Setting limit (and offset) with setQuery saves one method call plus ->setLimit() is nowhere used by the core (only in a test case). I don't see an advantage by using that method though it's surely an alternative
    – fruppel
    Jul 28, 2015 at 10:23

The GROUP BY can be achieved using ->group(...) and the ORDER BY can be achieved using ->order(...).

So your full query would look like this:

$query = $db->getQuery(true)
            'COUNT(*) AS ' . $db->quoteName('tracks')
      ->where('TIMESTAMPDIFF(DAY, ' . $db->quoteName('date_played') . ', NOW()) <= ' . $db->quote($resDays))
      ->where($db->quoteName('song_type') . ' = 0')
      ->order($db->quoteName('tracks') . ' DESC')


$result = $db->loadObjectList();

I am not entirely 100% sure about the where clause that I've written for you. Have a test and let me know if it works.

  • Fatal error: Call to a member function group() on a non-object in ->group($db->quoteName('title'))
    – Victor
    Jul 28, 2015 at 10:00
  • But, thank's for explaining me how to use GROUP BY and ORDER BY, and you code that youve writen to me i will use in next query that i need.
    – Victor
    Jul 28, 2015 at 10:04
  • How about where() with glue or andWhere() ? Apr 22, 2018 at 8:37
  • @mickmackusa - Good shout. Probably answered before I knew about the default and glue. Updated the answer
    – Lodder
    Apr 22, 2018 at 9:18
  • 1
    @mickmackusa - Updated again. Keeping the small disclaimer as I hadn't actually tested the code for this specific scenario. It was the first where clause I was unsure about. As for the prepared statements, I'd suggest asking on the Joomla Github Issue Tracker and tagging mbabker, as he maintains the database framework package ;)
    – Lodder
    Apr 22, 2018 at 9:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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