[FIXED] Laravel-How to merge two collections when one column is common in both collection?

Issue

I am using Laravel 5.6.38.

I have two collections.

Collection 1

[{
"link": "http://example.com/posts/1",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 1",
"post_id": 1,
"priority": "2",
"identifiedBy": "x",

},
{
"link": "http://example.com/posts/2",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 2",
"post_id": 2,
"priority": "3",
"identifiedBy": "x",
}]

Collection 2

[{
"post_id": 1,
"qatagger": "Mr. X"
}]

Expected result

[{
"link": "http://example.com/posts/1",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 1",
"post_id": 1,
"priority": "2",
"identifiedBy": "x",
"qatagger": "Mr. X"
},
{
"link": "http://example.com/posts/2",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 2",
"post_id": 2,
"priority": "3",
"identifiedBy": "x",
}]

I tried $collection1->merge($collection2), gets result

[{
"link": "http://example.com/posts/1",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 1",
"post_id": 1,
"priority": "2",
"identifiedBy": "superadmin"
},
{
"link": "http://example.com/posts/2",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 2",
"post_id": 2,
"priority": "3",
"identifiedBy": "superadmin"
},
{
"post_id": 1,
"qatagger": "Mr. x"
}]

Is there anyway I can get the expected result with out using any loop?

Update 1st join

$posts = DB::table('posts')
            ->where('posts.post_status_id', '=', $status)
            ->leftJoin(DB::raw("(SELECT post_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
    GROUP BY comment_type, post_id ASC) AND comment_type = 'vip_comment') comments_vip"), 'posts.post_id', '=', 'comments_vip.post_id')
            ->leftJoin(DB::raw("(SELECT post_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
    GROUP BY comment_type, post_id ASC) AND comment_type = 'pm_comment') comments_pm"), 'posts.post_id', '=', 'comments_pm.post_id')
            ->leftJoin(DB::raw("(SELECT post_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments GROUP BY comment_type, post_id ASC) AND comment_type = 'bagging_qa_comment') comments_bq"),'posts.post_id', '=', 'comments_bq.post_id')
            ->leftJoin('users AS identifiedByUser', function($join) {
                $join->on('posts.post_identified_by', '=', 'identifiedByUser.id');
            })
            ->select(['posts.post_id as post_id', 'posts.post_link as link', 'posts.post_status_id as status_id', 'posts.post_description as description', 'posts.post_priority as priority', 'posts.created_at as created_at', 'comments_vip.comment AS vip_comment', 'comments_pm.comment AS 
            pm_comment', 'comments_bq.comment as bagger_qa_comment', 'identifiedByUser.name as identifiedBy'])
            ->groupBy('post_id')
            ->get();

2nd join

$baggers = DB::table('post_baggings')
                    ->leftJoin('posts', 'post_baggings.post_id', '=', 'posts.post_id' )
                    ->join('users AS baggers', function($join) {
                        $join->on('post_baggings.bagging_team_id', '=', 'baggers.id');
                    })->select(array('posts.post_id as post_id', 'baggers.name as bagger'))
                    ->get()->keyBy('post_id'); 

Merging

$out = [];
    foreach ($posts as $key => $post){
        $post->priority = Priority::where('priority_id', '=', $post->priority)->pluck('display_name')->first();
        $post = new Collection($post);
        $out[] = $post->merge($baggers[$post['post_id']]);
    }

Solution

As I suggested in my comments you can add the table as an extra join in the query to get the results off the database. Something like below would probably work:

 $posts = DB::table('posts')
        ->where('posts.post_status_id', '=', $status)
        ->leftJoin(DB::raw("(SELECT post_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
GROUP BY comment_type, post_id ASC) AND comment_type = 'vip_comment') comments_vip"), 'posts.post_id', '=', 'comments_vip.post_id')
        ->leftJoin(DB::raw("(SELECT post_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
GROUP BY comment_type, post_id ASC) AND comment_type = 'pm_comment') comments_pm"), 'posts.post_id', '=', 'comments_pm.post_id')
        ->leftJoin(DB::raw("(SELECT post_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments GROUP BY comment_type, post_id ASC) AND comment_type = 'bagging_qa_comment') comments_bq"),'posts.post_id', '=', 'comments_bq.post_id')
        ->leftJoin('users AS identifiedByUser', function($join) {
            $join->on('posts.post_identified_by', '=', 'identifiedByUser.id');
        })
        ->select(['posts.post_id as post_id', 'posts.post_link as link', 'posts.post_status_id as status_id', 'posts.post_description as description', 'posts.post_priority as priority', 'posts.created_at as created_at', 'comments_vip.comment AS vip_comment', 'comments_pm.comment AS 
        pm_comment', 'comments_bq.comment as bagger_qa_comment', 'identifiedByUser.name as identifiedBy', 'joinedTable.bagger as bagger'])
        ->leftJoin(DB::raw('('.
                DB::table('post_baggings')
                    ->leftJoin('posts', 'post_baggings.post_id', '=', 'posts.post_id' )
                    ->join('users AS baggers', function($join) {
                        $join->on('post_baggings.bagging_team_id', '=', 'baggers.id');
                    })->select(array('posts.post_id as post_id', 'baggers.name as bagger'))->toSql()
            .') as joinedTable'), 'joinedTable.post_id', 'posts.post_id')
        ->groupBy('post_id')
        ->get();

This is taking advantage of method toSql which will preserve the exact query you're doing for your 2nd case. I have not actually tested this however.

A workaround using the resulting collections would be:

$result1->map(function ($row) use ($result2) {
      if ($result2->has($row->post_id)) {
         return collect(array_merge((array)$row, (array)$result2)); //Casting to arrays and then to a collection. 
      }
      return collect((array)$row);
});

Answered By - apokryfos