laravel - Laravel 从子查询连接数据库查询（合并绑定）

Question

我有这样的数据库查询：

$visitor = DB::table('visitor_anonymous')
->join('users', 'visitor_anonymous.host', '=', 'users.host')
->whereBetween('visitor_anonymous.created_at', [$startDate, $endDate])
->select(DB::raw('DATE(visitor_anonymous.created_at) AS date, COUNT(visitor_anonymous.host) AS visitor'))
->groupBy(DB::raw("DATE(visitor_anonymous.created_at)"));
    
$session = DB::table('visitor_anonymous')
->join('visitor_anonymous_pageviews', 'visitor_anonymous.session_id', '=', 'visitor_anonymous_pageviews.session_id')
->join('users', 'visitor_anonymous.host', '=', 'users.host')
->whereBetween('visitor_anonymous_pageviews.created_at', [$startDate, $endDate])
->select(DB::raw('DATE(visitor_anonymous_pageviews.created_at) AS date, COUNT(visitor_anonymous_pageviews.session_id) AS session'))
->groupBy(DB::raw("DATE(visitor_anonymous_pageviews.created_at), visitor_anonymous.session_id"));
    
$pageview = DB::table('visitor_anonymous')
->join('visitor_anonymous_pageviews', 'visitor_anonymous.session_id', '=', 'visitor_anonymous_pageviews.session_id')
->join('users', 'visitor_anonymous.host', '=', 'users.host')
->whereBetween('visitor_anonymous_pageviews.created_at', [$startDate, $endDate])
->select(DB::raw('DATE(visitor_anonymous_pageviews.created_at) AS date, sum(visitor_anonymous_pageviews.count) AS pageview'))
->groupBy(DB::raw("DATE(visitor_anonymous_pageviews.created_at)"));

希望这样的结果，但没有奏效。如何像这样加入子查询？

$table = DB::table( DB::raw("({$visitor->toSql()}) as v"))
->leftJoin(DB::raw("({$session->toSql()}) as s"), 'v.date','=','s.date')
->leftJoin(DB::raw("({$pageview->toSql()}) as p"), 'v.date','=','p.date')
->select(DB::raw('v.date, sum(visitor) AS visitor, sum(session) AS session, sum(pageview) AS pageview'))
->groupBy(DB::raw("v.date"))
->mergeBindings($visitor);

请告诉我，解决方案。谢谢

标签： laravelsubqueryleft-join