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:
- 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
ResultSetinto memory, for example by converting it into array. This is also what the documentation recommends:
ResultSetis possible to iterate only once, if we need to iterate multiple times, it is necessary to convert the result to the array via
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.
CLICK HERE to find out more related problems solutions.