Nette framework - Nette\Database\ResultSet implements only one way iterator

I came across a problem with foreach. If one result is returned in the first foreach {foreach $children as $child}, then it works. As there are more, an error pops up: Nette\Database\ResultSet implements only one way iterator.

The error is probably caused by the first ($children) and the second ($invoices) foreach being queried in the same table childern. I need to list all children (eg $child->firstName) and assign items to each (eg $invoice->date $invoice->snackName). However, I cannot cancel "JOIN ON children" in the query.

Output:

child 1 name

• item 1
• item 2

child 2 name

• item 1
• item 2

Don't know what's wrong? Thanks

public function actionShow($year, $month)
 {
    $invoices = $this->database->query('
    SELECT
        o.date AS date,
        o.snack AS snack
    FROM
        diet_orders o
    LEFT JOIN children ch ON o.child_id = ch.id
    WHERE
        ch.user_id = ?
        and YEAR (o.date) = ?
        and MONTH (o.date) = ?
            ', $this->getUser()->id, $year, $month,'');
            $children = $this->database->table('children')->where('user_id = ?', $this->getUser()->id);
    
    $this->template->invoices = $invoices;
    $this->template->children = $children;
}

Latte

{block content}
{foreach $children as $child}
{$child->fisrtName}
    {foreach $invoices as $invoice}
      {invoice->snack}
     {/foreach}
{/foreach}

{/block}

Your reasoning is correct. The ResultSet returned by most Nette\Database methods is a one way iterator so you cannot use it in multiple execution of a loop body. It is an optimization – once the loop advances the iterator, the previous row can be potentially garbage collected, which is useful to save memory on large database tables.

If you really need to iterate over a table multiple times, you can either:

• create $invoices multiple times
  • possibly moving the whole assignment into the loop inside the template
  • or creating function returning that result and calling it in the template
• or load the whole ResultSet into memory, for example by converting it into array. This is also what the documentation recommends:

  Over the ResultSet is possible to iterate only once, if we need to iterate multiple times, it is necessary to convert the result to the array via fetchAll() method.

But the best solution would be fixing it up to use JOINs, if possible. If you use FOREIGN KEYS in your database, you can avoid writing raw SQL and use the much nicer interface that will create JOINs for you transparently and efficiently, when you try to access data from other table through a column with a foreign key constraint.