Zend Select具有自联接覆盖字段

时间:2023-01-03 00:15:39

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();

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:

问题是生成的数组是平的,注释数据会覆盖post数据,这是返回的示例:

[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.

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

Thanks.

3 个解决方案

#1


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.

如果您将第二个连接中的所有列别名发布到帖子(如idPost作为child_idPost ...等),您将获得许多行,这些行是包含第二行列的父行。多数民众赞成你最接近的。然后,您可以从第一行获取父数据,然后遍历后续行以获取一对多数据。

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

否则,只需执行两个查询,一个用于父级,一个用于子级。它可能比创建大型结果表更快。

#2


Zend does not make your preferred form easy, but it might be possible. Note, however, that you are asking the database server to do far more work than you actually want, because the post and user information are duplicated for each comment. Justin is correct that a second query is easier and probably faster. However, I can provide some pointers toward a solution.

Zend不会让你喜欢的形式变得容易,但它可能是有可能的。但请注意,您要求数据库服务器执行的操作远远超出您的实际需要,因为每个注释的帖子和用户信息都是重复的。贾斯汀是正确的,第二个查询更容易,可能更快。但是,我可以提供一些解决方案。

To start, consider what you would get by using the Zend_Db::FETCH_NUM fetch mode:

首先,考虑使用Zend_Db :: FETCH_NUM获取模式可以获得什么:

Array(
    [0] => Array(
        [0] => 12
        [1] =>
        [2] => 1
        [3] => 84
        [4] => This is a post...,
        [5] => 12/this-is-a-post
        [6] =>
        [7] =>
        [8] => 2009-07-21 16:39:09
        [9] => 84
        [10] => John
        [11] => Doe
        [12] => john-doe
        [13] => uploads/userprofiles/0/84/pic84_14
        [14] => 15
        [15] => 12
        [16] => 1
        [17] => 79
        [18] => This is a comment...,
        [19] =>
        [20] =>
        [21] =>
        [22] => 2009-07-21 17:40:10
    ),
    [1] => Array(
        [0] => 12
        [1] =>
        [2] => 1
        [3] => 84
        [4] => This is a post...,
        [5] => 12/this-is-a-post
        [6] =>
        [7] =>
        [8] => 2009-07-21 16:39:09
        [9] => 84
        [10] => John
        [11] => Doe
        [12] => john-doe
        [13] => uploads/userprofiles/0/84/pic84_14
        [14] => 17
        [15] => 12
        [16] => 1
        [17] => 127
        [18] => This is another comment...,
        [19] =>
        [20] =>
        [21] =>
        [22] => 2009-07-20 10:31:26
    )
)

Then somehow you have to come up with the mapping of column numbers to table and column names:

然后你不得不想出列数到表和列名的映射:

Array(
    [0] => post.idPost
    [1] => post.idTopic
    [2] => post.idGroup
    [3] => post.idUser
    [4] => post.postContent
    [5] => post.postUrl
    [6] => post.postVotes
    [7] => post.postScore
    [8] => post.date
    [9] => user.idUser
    [10] => user.fname
    [11] => user.lname
    [12] => user.profileUrl
    [13] => user.photoUrl
    [14] => comment.idPost
    [15] => comment.idTopic
    [16] => comment.idGroup
    [17] => comment.idUser
    [18] => comment.postContent
    [19] => comment.postUrl
    [20] => comment.postVotes
    [21] => comment.postScore
    [22] => comment.date
)

This is the part where it gets tricky, because the table part of that is strongly specific to the database interface, and not always possible. Because it is tied to the result set, the adapter will also be the best place to obtain it; that means hacking Zend classes, possibly by providing your own adapter class. Depending on your database, the information might come from:

这是它变得棘手的部分,因为它的表部分强烈特定于数据库接口,并不总是可能的。因为它与结果集绑定,所以适配器也是获得它的最佳位置;这意味着可能通过提供自己的适配器类来攻击Zend类。根据您的数据库,信息可能来自:

Other adapters might not have a way to obtain the table name, unfortunately.

遗憾的是,其他适配器可能无法获取表名。

#3


Yeah we've had the same issue, just finished blogging about it: http://www.kintek.com.au/blog/zend-db-and-joins/

是的,我们有同样的问题,刚刚完成了关于它的博客:http://www.kintek.com.au/blog/zend-db-and-joins/

You need to name each field uniquely, it's a known bug in Zend: http://framework.zend.com/issues/browse/ZF-6421?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

你需要唯一地命名每个字段,这是Zend中一个已知的错误:http://framework.zend.com/issues/browse/ZF-6421?page = com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

#1


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.

如果您将第二个连接中的所有列别名发布到帖子(如idPost作为child_idPost ...等),您将获得许多行,这些行是包含第二行列的父行。多数民众赞成你最接近的。然后,您可以从第一行获取父数据,然后遍历后续行以获取一对多数据。

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

否则,只需执行两个查询,一个用于父级,一个用于子级。它可能比创建大型结果表更快。

#2


Zend does not make your preferred form easy, but it might be possible. Note, however, that you are asking the database server to do far more work than you actually want, because the post and user information are duplicated for each comment. Justin is correct that a second query is easier and probably faster. However, I can provide some pointers toward a solution.

Zend不会让你喜欢的形式变得容易,但它可能是有可能的。但请注意,您要求数据库服务器执行的操作远远超出您的实际需要,因为每个注释的帖子和用户信息都是重复的。贾斯汀是正确的,第二个查询更容易,可能更快。但是,我可以提供一些解决方案。

To start, consider what you would get by using the Zend_Db::FETCH_NUM fetch mode:

首先,考虑使用Zend_Db :: FETCH_NUM获取模式可以获得什么:

Array(
    [0] => Array(
        [0] => 12
        [1] =>
        [2] => 1
        [3] => 84
        [4] => This is a post...,
        [5] => 12/this-is-a-post
        [6] =>
        [7] =>
        [8] => 2009-07-21 16:39:09
        [9] => 84
        [10] => John
        [11] => Doe
        [12] => john-doe
        [13] => uploads/userprofiles/0/84/pic84_14
        [14] => 15
        [15] => 12
        [16] => 1
        [17] => 79
        [18] => This is a comment...,
        [19] =>
        [20] =>
        [21] =>
        [22] => 2009-07-21 17:40:10
    ),
    [1] => Array(
        [0] => 12
        [1] =>
        [2] => 1
        [3] => 84
        [4] => This is a post...,
        [5] => 12/this-is-a-post
        [6] =>
        [7] =>
        [8] => 2009-07-21 16:39:09
        [9] => 84
        [10] => John
        [11] => Doe
        [12] => john-doe
        [13] => uploads/userprofiles/0/84/pic84_14
        [14] => 17
        [15] => 12
        [16] => 1
        [17] => 127
        [18] => This is another comment...,
        [19] =>
        [20] =>
        [21] =>
        [22] => 2009-07-20 10:31:26
    )
)

Then somehow you have to come up with the mapping of column numbers to table and column names:

然后你不得不想出列数到表和列名的映射:

Array(
    [0] => post.idPost
    [1] => post.idTopic
    [2] => post.idGroup
    [3] => post.idUser
    [4] => post.postContent
    [5] => post.postUrl
    [6] => post.postVotes
    [7] => post.postScore
    [8] => post.date
    [9] => user.idUser
    [10] => user.fname
    [11] => user.lname
    [12] => user.profileUrl
    [13] => user.photoUrl
    [14] => comment.idPost
    [15] => comment.idTopic
    [16] => comment.idGroup
    [17] => comment.idUser
    [18] => comment.postContent
    [19] => comment.postUrl
    [20] => comment.postVotes
    [21] => comment.postScore
    [22] => comment.date
)

This is the part where it gets tricky, because the table part of that is strongly specific to the database interface, and not always possible. Because it is tied to the result set, the adapter will also be the best place to obtain it; that means hacking Zend classes, possibly by providing your own adapter class. Depending on your database, the information might come from:

这是它变得棘手的部分,因为它的表部分强烈特定于数据库接口,并不总是可能的。因为它与结果集绑定,所以适配器也是获得它的最佳位置;这意味着可能通过提供自己的适配器类来攻击Zend类。根据您的数据库,信息可能来自:

Other adapters might not have a way to obtain the table name, unfortunately.

遗憾的是,其他适配器可能无法获取表名。

#3


Yeah we've had the same issue, just finished blogging about it: http://www.kintek.com.au/blog/zend-db-and-joins/

是的,我们有同样的问题,刚刚完成了关于它的博客:http://www.kintek.com.au/blog/zend-db-and-joins/

You need to name each field uniquely, it's a known bug in Zend: http://framework.zend.com/issues/browse/ZF-6421?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel

你需要唯一地命名每个字段,这是Zend中一个已知的错误:http://framework.zend.com/issues/browse/ZF-6421?page = com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel