php - 合并来自两个查询输出的数据

Question

我想在我的网站上创建类似共享的功能。我有这些表

用户

1. 用户编号
2. 全名
3. 用户名等

帖子

1. post_id
2. 用户编号
3. 发布
4. orig_post_id
5. 日期

USER_FOLLOWERS

1. 关注ID
2. 用户编号
3. follower_id
4. 日期

我有这个查询来从当前用户关注的用户中选择帖子。

//user_id from session data
$user_id = $this->session->userdata('user_id');

$sql = "SELECT  p.*,u.fullname,u.username
        FROM    (
                 SELECT  user_id
                 FROM    user_followers
                 WHERE   follower_id = $user_id
                 UNION ALL
                 SELECT  $user_id
                ) uf
        JOIN    posts p
        ON      p.user_id = uf.user_id
        JOIN users u
        ON     u.user_id = p.user_id
        ORDER BY p.post_date DESC";


$query = $this->db->query($sql);

 if ($query) {

            foreach ($query->result() as $row) {
                $branch_id = $row->orig_post_id;
                $post_array[] = array(
                    'post_id' => $row->post_id,
                    'user_id' => $row->user_id,
                    'post' => $row->post,
                    'is_branch_of_id' => $branch_id,
                    'post_date' => $row->post_date,
                    'fullname' => $row->fullname,
                    'username' => $row->username
                );



             #i would explain what i'm tying to do here below


      if ($branch_id != 0) {
            $branch_array = array();
            #this contains the orignal posts user id
            $user_branch_id = $this->postid_return_user_id($branch_id);
            $branch_data = $this->branch_query($user_branch_id, $branch_id);
            $branch_array[] = array(
                'branch_uname' => $branch_data->username,
                'branch_fname' => $branch_data->fullname,
                'orig_post' => $branch_data->post
            );

            $post_obj = (object)array_merge($branch_array, $post_array);
        } else {
            $post_obj = (object)$post_array;
        }


            }
            return $post_obj;

然后分支查询

   public function branch_query($orig_post_user_id, $orig_post_id)
    {
        $sql = "SELECT  users.username,users.fullname,posts.post,posts.post_id
                FROM   users u
                JOIN    posts p
                ON      p.user_id = u.user_id
                WHERE   u.user_id = $orig_post_user_id
                AND     p.post_id = $orig_post_id";
        $q = $this->db->query($sql);
        return ($q)?$q->result():array();
    }

第一，在 post_array 中获取我需要的数据。

如果字段 orig_post_id 不为 0，则表示该帖子是由其他帖子的用户分享的。我创建了另一个名为 branch_array 的数组，branch 数组用于包含原始帖子的用户用户名、全名和原始帖子本身。这就是分支查询的用武之地。通过分支查询，我传递原始用户用户 ID 和原始帖子 ID，然后它返回原始帖子用户的用户名、全名和帖子本身。然后我在分支数据变量中获取它并将其放入分支数组。

现在我尝试将分支数据合并到 post_array 并将合并后的数组转换为对象。我想要的输出看起来像这样；

场景一，当orig_post_id不为0时

$post_obj = new stdClass([post_id] => 4,
                        [user_id] => 2,
                        [post] => ok ginny,
                        [orid_post_id] => 3,
                        [post_date] => some timestamp,
                        [fullname] => Harry Potter,
                        [username] => avadakedevra,
                        [branch_uname] => ginny,
                        [branch_fname] => Ginny Potter
                        [orig_post] => stop it harry
) 

可以看到，分支数据已经合并。场景2，当orig_post_id = 0

$post_obj = new stdClass([post_id] => 3,
                        [user_id] => 1,
                        [post] => stop it harry,
                        [orid_post_id] => 0,
                        [post_date] => some timestamp,
                        [fullname] => Ginny Potter,
                        [username] => ginny
) 

现在它只获取一个分支数据并将其放在对象之外。任何帮助将不胜感激。对不起，长度。正如你从哈利波特的帖子中看出的那样，我真的快要哭了哈哈再次感谢。

Answer 1

这只会让事情变得比它应该的更复杂。试试这个

 foreach ($query->result() as $row) {
                $branch_id = $row->is_branch_of_id;
                $user_branch_id = $this->postid_return_user_id($branch_id);
                $post_array[] = array(
                    'post_id' => $row->post_id,
                    'user_id' => $row->user_id,
                    'post' => $row->post,
                    'is_branch_of_id' => $branch_id,
                    'post_date' => $row->post_date,
                    'fullname' => $row->fullname,
                    'username' => $row->username,
                    'file_path_thumb' => $row->file_path_thumb,

---

                    'data' => $this->branch_query($user_branch_id, $branch_id)

---

                );
                    $post_obj = $this->array_to_object($post_array);

            }

由于 post_array 是多维的，您将需要此函数将其转换为对象。

 public function array_to_object($array) {
        $obj = new stdClass;
        foreach($array as $k => $v) {
            if(is_array($v)) {
                $obj->{$k} = $this->array_to_object($v);
            } else {
                $obj->{$k} = $v;
            }
        }
        return $obj;
    }