Transforming a Query for JDATABASE

Question

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')));
$query->from($db->quoteName('history'));

$db->setQuery($query);
$query->setLimit(20);
$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.

Answer 1

$query = $db->getQuery(true);
$query->select($db->quoteName(array('artist', 'title')));
$query->select('COUNT(*) AS tracks');
$query->from($db->quoteName('history'));
$query->where('TIMESTAMPDIFF(DAY, date_played, NOW()) <= ' . $db->quote($resDays));
$query->where('song_type = 0');
$query->group('title');
$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

Answer 2

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)
      ->select(
          array(
            $db->quoteName('artist'),
            $db->quoteName('title'),
            'COUNT(*) AS ' . $db->quoteName('tracks')
          )
      )
      ->from($db->quoteName('history'))
      ->where('TIMESTAMPDIFF(DAY, ' . $db->quoteName('date_played') . ', NOW()) <= ' . $db->quote($resDays))
      ->where($db->quoteName('song_type') . ' = 0')
      ->group($db->quoteName('title'))
      ->order($db->quoteName('tracks') . ' DESC')
      ->setLimit(20);

$db->setQuery($query);

$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.