Returning multiple rows per row (in Zend Framework)

Question

I have a MySQL database containing these tables:

sessions
--------
sessionid (INT)
[courseid (INT)]
[locationid (INT)]
[comment (TEXT)]

dates
-----
dateid (INT)
sessionid (INT)
date (DATE)

courses
-------
...

locations
---------
...

Each session has a unique sessionid, and each date has a unique dateid. But dates don't necessarily have a unique sessionid, as a session can span over a variable number of dates (not necessarily consecutive).

Selecting each full row is simply a matter of joining the tables on the sessionid. However, I'm looking for a way to return a rowset for a particular courseid, where each row in that rowset represents a location, and contains another rowset, each containing single session, which in turn contains another rowset, which contains all of the dates for that session:

course
    location
        sesssion
            date
            date
        session
            date
            date
            date
    location
        ...

This is because I'm using querying this database from PHP using Zend Framework, which has a great interface for manipulating rows and rowsets in an object-oriented manner.

Ultimately, I'm trying to output a 'schedule' to the view, organized first by course, then location, then date. Ideally, I'd be able iterate over each row as a location, and then for each location, iterate over each session, and then for each session, iterate over each date.

I'm thinking of doing this by querying for all the locations, sessions, and dates separately. Then, I'd convert each rowset into an array, and add each sessions array as a member of a locations array, and add each dates array as a member of a sessions array.

This, however, feels very kludgy, and doesn't provide me with the ability to handle the rows in an object-oriented manner.

I was wondering if there was either:

a) a better table schema for representing this data;

b) an sql query which i'm not aware of;

c) a method in Zend_Db that allows me to assign a rowset to a rowset

Please let me know if I haven't been clear anywhere, and thanks in advance.

(Crossing my fingers that this doesn't end up on the daily wtf...)

Answer 1

You could handle this scenario using the relationship features of Zend_ Db_ Table. You'd need to create table wrapper classes for sessions, dates, courses, etc. if you're using Zend_ Db_ Aadpter for your queries currently.

http://framework.zend.com/manual/en/zend.db.table.relationships.html

It's not too different from the approach you described of querying for each dataset separately, but it gives you a straight forward OO interface for retrieving the appropriate related data for a given record.

You'll want to do some benchmarking if you go this route, as it could potentially execute a lot of queries.

Answer 2

I've run into lots of issues with using Zend Frameworks database abstraction classes when I have to deal with data from multiple tables. The number of queries that run and the overhead of all of the objects generated has brought my hosting server to it's knees. I've since reverted back to writing queries to gather all of my data and then walking the data to build my display. It's not a pretty or OO as using the abstraction layers but it's also not making my PHP scripts page to disk just to display a table full of data.

As Steve mentions benchmark whatever solution you end up with, I'd also profile your memory usage.