algorithm optimization: returning object and sub-objects from single SQL statement in PHP

Question

I have an object oriented PHP application. I have a simple hierarchy stored in an SQL table (Chapters and Authors that can be assigned to a chapter).

I wrote the following method to fetch the chapters and the authors in a single query and then loop through the result, figuring out which rows belong to the same chapter and creating both Chapter objects and arrays of Author objects.

However, I feel like this can be made a lot neater. Can someone help?

function getChaptersWithAuthors($monographId, $rangeInfo = null) {
    $result =& $this->retrieveRange(
        'SELECT mc.chapter_id, mc.monograph_id, mc.chapter_seq, ma.author_id, ma.monograph_id, mca.primary_contact, mca.seq, ma.first_name, ma.middle_name, ma.last_name, ma.affiliation, ma.country, ma.email, ma.url
        FROM monograph_chapters mc
        LEFT JOIN monograph_chapter_authors mca ON mc.chapter_id = mca.chapter_id
        LEFT JOIN monograph_authors ma ON ma.author_id = mca.author_id
        WHERE mc.monograph_id = ?
        ORDER BY mc.chapter_seq, mca.seq',
        $monographId, $rangeInfo
    );

    $chapterAuthorDao =& DAORegistry::getDAO('ChapterAuthorDAO');
    $chapters = array();
    $authors = array();
    while (!$result->EOF) {
        $row = $result->GetRowAssoc(false);
        // initialize $currentChapterId for the first row
        if ( !isset($currentChapterId) ) $currentChapterId = $row['chapter_id'];

        if ( $row['chapter_id'] != $currentChapterId) {
            // we're on a new row. create a chapter from the previous one
            $chapter =& $this->_returnFromRow($prevRow);
            // set the authors with all the authors found so far
            $chapter->setAuthors($authors);
            // clear the authors array
            unset($authors);
            $authors = array();
            // add the chapters to the returner
            $chapters[$currentChapterId] =& $chapter;

            // set the current id for this row
            $currentChapterId = $row['chapter_id'];
        }

        // add every author to the authors array
        if ( $row['author_id'] )
            $authors[$row['author_id']] =& $chapterAuthorDao->_returnFromRow($row);

        // keep a copy of the previous row for creating the chapter once we're on a new chapter row
        $prevRow = $row;
        $result->MoveNext();

        if ( $result->EOF ) {
            // The result set is at the end
            $chapter =& $this->_returnFromRow($row);
            // set the authors with all the authors found so far
            $chapter->setAuthors($authors);
            unset($authors);
            // add the chapters to the returner
            $chapters[$currentChapterId] =& $chapter;
        }
    }

    $result->Close();
    unset($result);
    return $chapters;
}

PS: the _returnFromRow methods simply construct an Chapter or Author object given the SQL row. If needed, I can post those methods here.

EDIT: i cannot append items one author at a time to chapter. I need to add them all at once because of the way the Chapter object is structured.

Answer 1

I guess the goal is to make this simpler and easier to understand. How about something like this pseudocode? :

execute query
chapters = array()
for each result:
    chapter = result['chapter']
    author = result['author']
    if (!isset(chapters[chapter]))
        chapters[chapter] = new Chapter
    chapters[chapter].authors.append(author)

Rather than creating an authors array, populating it, storing it, then starting over for each chapter, we can make the code more readable and shorter (albeit a little slower, probably). In short, for each result, it looks to see if the chapter is in the chapters array. If not, it creates it. Then it adds the author to the chapter.