1

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.

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

  • Just a note. If you're using Joomla 3.x, you use ->setLimit(20); rather than defining it in setQuery – Lodder Jul 28 '15 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 '15 at 10:23
1

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.

  • Fatal error: Call to a member function group() on a non-object in ->group($db->quoteName('title')) – Victor Jul 28 '15 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 '15 at 10:04
  • How about where() with glue or andWhere() ? – mickmackusa Apr 22 '18 at 8:37
  • @mickmackusa - Good shout. Probably answered before I knew about the default and glue. Updated the answer – Lodder Apr 22 '18 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 '18 at 9:46

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.