How to select records one by one using JDatabaseQuery

Question

In programming languages there are 2 patterns for selecting records from the database:

//prepare SELECT statement
while($record = $query->fetchRow())
{
    //do the job
}

And the second:

//prepare SELECT statement
$recordsArray = $query->fetchAllRows();
//do the job with all the records in a 2-dimensional array

To my surprise only the second option is available in JDatabaseQuery: loadRowList, loadAssocList etc.

When I tried:

$query = $db->getQuery(true);
$query->select(array('id', 'name'))
    ->from($db->quoteName('#__my_table'));

$db->setQuery($query);
while($row = $db->loadAssoc())
{
    var_dump($row);
}

It hanged. Can I fetch the records one by one rather then get a 2-dimensional array?

Answer 1

There are quite a few methods of Joomla's db driver that are not documented on the doc page. The API summary page for 3.8 is here

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

What you're looking for is the iterator.

$query = $db->getQuery(true);
$query->select('id,name')->from($db->qn('#__my_table'));
$db->setQuery($query);
$iterator = $db->getIterator();
foreach($iterator as $row){
    var_dump($row);
}

It will return one stdClass at a time and you can 'break' out of the loop to stop it running.