php - 算法优化: returning object and sub-objects from single SQL statement in PHP

Question

我有一个面向对象的 PHP 应用程序。我有一个存储在 SQL 表中的简单层次结构(可以分配给章节的章节和作者)。

我编写了以下方法来在单个查询中获取章节和作者，然后遍历结果，找出哪些行属于同一章节，并创建 Chapter 对象和 Author 对象数组。

不过，我觉得这可以做得更整洁。有人可以帮忙吗？

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:_returnFromRow 方法只是在给定 SQL 行的情况下构造一个 Chapter 或 Author 对象。如果需要，我可以在此处发布这些方法。

编辑:我不能一次将一位作者附加到章节中。由于 Chapter 对象的结构，我需要一次添加它们。

Answer 1

我想目标是让这个更简单、更容易理解。像这样的伪代码怎么样？ :

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)

与其创建 authors 数组，填充它，存储它，然后重新开始每一章，我们可以使代码更具可读性和更短(尽管可能会慢一点)。简而言之，对于每个结果，它都会查看章节是否在章节数组中。如果没有，它会创建它。然后它将作者添加到章节中。