Zend Select with self join overwriting fields

Question

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:

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

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

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

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

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

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:

• PDO: PDOStatement->getColumnMeta
• Mysqli: mysqli_fetch_field_direct

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