Zend Select 与 self join 覆盖字段 [英] Zend Select with self join overwriting fields

问题描述

帖子和评论存储在同一个表中.因此，为了获取每个帖子及其评论，我们这样做:

Posts and comments are stored in the same table. So to get each post and its comments we do this:

    $posts = $this->select()->setIntegrityCheck(false)
                        ->from(array('post' => 'Posts'), array('*'))
                        ->where('post.idGroup = ' . $idGroup)
                        ->where('post.idTopic IS NULL')
                        ->order('post.date DESC')
                        ->limit($resultsPerPage, $resultsPerPage * ($page - 1))
                        ->joinLeft(array('user' => 'Users'), 'post.idUser = user.idUser', 
                            array('idUser', 'fname', 'lname', 'profileUrl', 'photoUrl'))
                        ->joinLeft(array('comment' => 'Posts'), 'comment.idTopic = post.idPost')
                        ->query()->fetchAll();

问题是结果数组是flat，评论数据覆盖了post数据，这是返回的一个例子:

The problem is that the resulting array is flat and the comment data overwrites the post data, this is an example of what is returned:

[1] => Array
    (
        [idPost] => 13
        [idTopic] => 11
        [idGroup] => 1
        [idUser] => 84
        [postContent] => Hello my name is Mud.
        [postUrl] => 13/hello-my-name-is-mud
        [postVotes] => 
        [postScore] => 
        [date] => 2009-07-21 16:39:09
        [fname] => John
        [lname] => Doe
        [profileUrl] => john-doe
        [photoUrl] => uploads/userprofiles/0/84/pic84_14
    )

我们希望结果更像这样:

What we would like the result to be is something more like this:

    [1] => array(
            [post] => array(
                [0] => array(
                    idPost => 12,
                    postContent => This is a post...,
                    idGroup => 1
                    ...
                )
            ),
            [user] => array(
                [0] => array(
                    userName => JohnDoe
                    ...
                    )
                ),
            [comments] => array(
                [0] => array(
                    idPost => 15,
                    postContent => This is a comment...,
                    idGroup => 1
                    ...
                ),
                [1] => array(
                    idPost => 17,
                    postContent => This is another comment...,
                    idGroup => 1
                    ...
                )
            )
        )

也非常欢迎对其他解决方案的任何提示.

Any hints to other solutions is also very welcome.

谢谢.

推荐答案

如果您将第二个连接中的所有列都别名为帖子(例如 idPost 作为 child_idPost... 等)，您将获得许多作为父级的行行与第二行的列.那是你会得到的最接近的.然后，您可以从第一行获取父数据，然后循环遍历后续行以获取您的一对多数据.

If you alias all the columns in the second join to posts (like idPost as child_idPost... etc), you'll get many rows that are the parent row with the columns of the second row. Thats about the closest you'll get. You can then grab the parent data from the first row, and then loop through the subsequent rows to get your one-to-many data.

否则，只需执行两个查询，一个针对父级，一项针对子级.无论如何，它可能比创建大型结果表更快.

Otherwise, just do two queries, one for the parent, one for the children. It may be faster than creating that large result table anyway.

这篇关于Zend Select 与 self join 覆盖字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！