Returning a nested resultset

[eluser]Unknown[/eluser]
Hi guys,

My scenario is the following:
- I have news items, which can have many assets. News are stored in the "content" table and their assets are linked back from the "assets" table.

I'd like to create a list_all function that returns all the news items with their associated assets, so that I can loop through and display a news listing in my view.

I'm not quite sure how to achieve this. In my view it would be something like:

Code:

foreach news as item
   echo news->date_created
   echo news->title
   foreach news->assets as asset
      echo asset->title
   end foreach
end foreach

Currently I've got a list of all news, but not an asset association. Would appreciate some pointers.