代码之家 › 专栏 › 技术社区 › Prafulla Kumar Sahu umang naik

Laravel当两个集合中都有一列时如何合并两个集合?

laravel-collection laravel-5 laravel php

1

Prafulla Kumar Sahu umang naik · 技术社区 · 6 年前

我用的是Laravel 5.6.38。

我有两个收藏。

馆藏1

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

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

馆藏2

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

预期结果

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

我尝试了$collection1->合并($collection2),得到结果

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

不管怎样,我是否可以在不使用任何循环的情况下得到预期的结果?

更新 第一连接

$videos = DB::table('videos')
            ->where('videos.video_status_id', '=', $status)
            ->leftJoin(DB::raw("(SELECT video_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
    GROUP BY comment_type, video_id ASC) AND comment_type = 'vip_comment') comments_vip"), 'videos.video_id', '=', 'comments_vip.video_id')
            ->leftJoin(DB::raw("(SELECT video_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
    GROUP BY comment_type, video_id ASC) AND comment_type = 'pm_comment') comments_pm"), 'videos.video_id', '=', 'comments_pm.video_id')
            ->leftJoin(DB::raw("(SELECT video_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments GROUP BY comment_type, video_id ASC) AND comment_type = 'tagging_qa_comment') comments_tq"),'videos.video_id', '=', 'comments_tq.video_id')
            ->leftJoin('users AS identifiedByUser', function($join) {
                $join->on('videos.video_identified_by', '=', 'identifiedByUser.id');
            })
            ->select(['videos.video_id as video_id', 'videos.video_link as link', 'videos.video_status_id as status_id', 'videos.video_description as description', 'videos.video_priority as priority', 'videos.created_at as created_at', 'comments_vip.comment AS vip_comment', 'comments_pm.comment AS 
            pm_comment', 'comments_tq.comment as tagger_qa_comment', 'identifiedByUser.name as identifiedBy'])
            ->groupBy('video_id')
            ->get();

第二连接

$taggers = DB::table('video_taggings')
                    ->leftJoin('videos', 'video_taggings.video_id', '=', 'videos.video_id' )
                    ->join('users AS taggers', function($join) {
                        $join->on('video_taggings.tagging_team_id', '=', 'taggers.id');
                    })->select(array('videos.video_id as video_id', 'taggers.name as tagger'))
                    ->get()->keyBy('video_id');

合并

$out = [];
    foreach ($videos as $key => $video){
        $video->priority = Priority::where('priority_id', '=', $video->priority)->pluck('display_name')->first();
        $video = new Collection($video);
        $out[] = $video->merge($taggers[$video['video_id']]);
    }

3 回复 | 直到 6 年前

1

2

apokryfos 6 年前

正如我在评论中建议的那样,您可以将表作为额外的联接添加到查询中,以从数据库中获取结果。下面这样的方法可能有效:

 $videos = DB::table('videos')
        ->where('videos.video_status_id', '=', $status)
        ->leftJoin(DB::raw("(SELECT video_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
GROUP BY comment_type, video_id ASC) AND comment_type = 'vip_comment') comments_vip"), 'videos.video_id', '=', 'comments_vip.video_id')
        ->leftJoin(DB::raw("(SELECT video_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments
GROUP BY comment_type, video_id ASC) AND comment_type = 'pm_comment') comments_pm"), 'videos.video_id', '=', 'comments_pm.video_id')
        ->leftJoin(DB::raw("(SELECT video_id, comment, comment_type FROM comments WHERE comment_id in (SELECT MAX(comment_id) FROM comments GROUP BY comment_type, video_id ASC) AND comment_type = 'tagging_qa_comment') comments_tq"),'videos.video_id', '=', 'comments_tq.video_id')
        ->leftJoin('users AS identifiedByUser', function($join) {
            $join->on('videos.video_identified_by', '=', 'identifiedByUser.id');
        })
        ->select(['videos.video_id as video_id', 'videos.video_link as link', 'videos.video_status_id as status_id', 'videos.video_description as description', 'videos.video_priority as priority', 'videos.created_at as created_at', 'comments_vip.comment AS vip_comment', 'comments_pm.comment AS 
        pm_comment', 'comments_tq.comment as tagger_qa_comment', 'identifiedByUser.name as identifiedBy', 'joinedTable.tagger as tagger'])
        ->leftJoin(DB::raw('('.
                DB::table('video_taggings')
                    ->leftJoin('videos', 'video_taggings.video_id', '=', 'videos.video_id' )
                    ->join('users AS taggers', function($join) {
                        $join->on('video_taggings.tagging_team_id', '=', 'taggers.id');
                    })->select(array('videos.video_id as video_id', 'taggers.name as tagger'))->toSql()
            .') as joinedTable'), 'joinedTable.video_id', 'videos.video_id')
        ->groupBy('video_id')
        ->get();

这是利用方法 toSql 这将保留您为第二个案例所做的确切查询。不过,我还没有真正测试过这个。

使用结果集合的解决方法是:

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

2

1

Shokry Mohamed 6 年前

退出结果后不能合并,必须在查询中合并。无论基于何种基础,您将被纳入这一结果,这里没有任何要求。

这个解决方案,但我不认为是真的

$arr1 = json_decode('[{
"link": "http://example.com/videos/1",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 1",
"video_id": 1,
"priority": "2",
"identifiedBy": "x"
},
{
"link": "http://example.com/videos/2",
"created_at": "2018-09-20 05:14:10",
"description": "some desc 2",
"video_id": 2,
"priority": "3",
"identifiedBy": "x"
}]');

$arr2 = json_decode('[{
"video_id": 1,
"qatagger": "Mr. X"
}]');

$arr1[0] = (object)array_merge((array)$arr1[0],(array)$arr2[0]);
dd(new \Illuminate\Support\Collection($arr1));

3

0

Prafulla Kumar Sahu umang naik 6 年前

我们的工作将是

$out = array();
 foreach ($collections1 as $key => $collection1){
      $out[] = (object)array_merge((array)$collections2[$key], (array)$value);
 }

dd(new \Illuminate\Support\Collection($out));

一个更好的方法(目前的工作解决方案)可以是

$out = [];
    foreach ($videos as $key => $video){
        $video->priority = Priority::where('priority_id', '=', $video->priority)->pluck('display_name')->first();
        $video = new Collection($video);
        $out[] = $video->merge($taggers[$video['video_id']]);
    }

    return new Collection($out);

又是一个解决办法。