Snoeren Development Logo

Joomla! has its own database query object in which you can build queries in. This is a very useful object as it is not prone to query changes in the future. However, the database object and functions to get the result are a bit strange!

 

 

Image this base code:
<?php
$db = JFactory::getDbo();
$query = $db->getQuery(true);

$query
->select($db->quoteName('column'))
->from($db->quoteName('#__table'));

The way I think it is logical is by defining the query, setting the query in the database object. Then executing it and retrieve its found rows to return a null value. If it did find any rows then return the rows. However, this is not how Joomla!'s system works. This makes my queries run double which is bad, very bad! The following code is what I always used to get results from the database:

<?php
$db = JFactory:getDbo();
$query = $db->getQuery(true);

$query
->select(...);

/* Set the query in the database object */
$db->setQuery($query);

/* If the query failed or no results were found, return a null value. In this case an empty array */
if (!$db->execute() || !$db->getNumRows()) { return array(); }

/* Return the found rows as array containing stdClass objects */
return $db->loadObjectList();

Seems logical, right? For someone like me who has used PDO for quite some time it is. You will follow the following procedure;

  1. You prepare the query for execution
  2. You execute the query
  3. You retrieve the rows and return a null-value if it did not find anything
  4. You'll fetch the resultset and return the value

The code above makes the query run twice as the loadObjectList or any other result fetching function runs the query for you. In my opinion this is very unlogical. The correct code I've to run now to make sure the query doesn't run twice is;

<?php
$db = JFactory:getDbo();
$query = $db->getQuery(true);
$query
->select(...);

/* Set the query in the database object*/
$db->setQuery($query);

/* Execute the query and get the result */
$result = $db->loadObjectList();

/* Return a null-value based on the retrieved rows */
return $db->getNumRows() > 0 ? $result : array();

This code runs the query once and gets the result of the query at the same time. The function execute is nowhere to be found and should only be used to execute queries that do not return any rows such as a delete query. It took me a long time to find out as there is no sign of an execution in the Joomla! documentation whatsoever. I recently found out by reviewing why my queries ran twice in the Joomla! debug console (which is very useful!).

I hope none of you are making this mistake too as it can be quite a performance killing code. On small software it isn't even noticable, but on a large query, it really is.

Ads keep the free extensions free!