laravel 多个数组关联查询多个表并通过union拼接

暂无标签

节点表id
帖子表node_id
日志表node_id

public static function lists($node_id)
  {
      $author_fields = 'u.profile_photo_path';
      $node_fields = 'n.name as node_name'.','.'n.icon as node_icon'.','.'n.alias as node_alias';

        $posts = \DB::table('posts as p')
            ->select('p.id','p.alias','p.title','p.created_at','p.authorid','p.comments','p.views',$node_fields,$author_fields, \DB::raw("'post' as type"))
            ->leftJoin('nodes as n','n.id','=','p.node_id')
            ->leftJoin('users as u','u.id','=','p.authorid')
            ->where('node_id', $node_id);
        $blogs = \DB::table('blog_articles as b')
            ->select('b.id','b.alias','b.title','b.created_at','b.authorid','b.comments','b.views',$node_fields,$author_fields, \DB::raw("'blog' as type"))
            ->leftJoin('nodes as n','n.id','=','b.node_id')
            ->leftJoin('users as u','u.id','=','b.authorid')
           ->where('node_id', $node_id);
        $result = $posts->union($blogs)
            ->orderByDesc('created_at')
            ->paginate(20);
      foreach($result as $re){
          $re->avatar = $re->profile_photo_path ? Storage::disk('public')->url($re->profile_photo_path) : 'https://ui-avatars.com/api/?name='.urlencode($re->author).'&color=7F9CF5&background=EBF4FF';
      }
      return $result;
}