algorithm optimization: returning object and sub-objects from single SQL statement in PHP
- by pocketfullofcheese
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.
