Is any possible to execute this query in Laravel 5?.
select t1.*, v.hash from
( select dr.*, d.id as d_id, d.name, d.user_id from directory d
left join directory_relations dr on d.id = dr.directory_id
union select dr.*, d.id as d_id, d.name, d.user_id from directory_relations dr
left join directory d on d.id = dr.directory_id ) t1
left join videos v on t1.video_id = v.id WHERE t1.user_id = 265
You can use a DB::raw
$results = DB::select( DB::raw("select t1.*, v.hash from
( select dr.*, d.id as d_id, d.name, d.user_id from directory d
left join directory_relations dr on d.id = dr.directory_id
union select dr.*, d.id as d_id, d.name, d.user_id from directory_relations dr
left join directory d on d.id = dr.directory_id ) t1
left join videos v on t1.video_id = v.id WHERE t1.user_id = :user_id"), array(
'user_id' => 265,
));
With the query builder you can do it like:
$union = DB::select(['dr.*', 'd.id as d_id', 'd.name', 'd.user_id'])
->from('directory_relations as dr')
->leftJoin('directory as d', 'd.id', '=', 'dr.directory_id');
$base = DB::select(['dr.*', 'd.id as d_id', 'd.name', 'd.user_id'])
->from('directory as d')
->leftJoin('directory_relations as dr', 'd.id', '=', 'dr.directory_id')
->union($union);
DB::select('t1.*', 'v.hash')
->from(DB::raw("({$base->toSql()}) as t1"))
->mergeBindings($base->getQuery())
->leftJoin('videos as v', 't1.video_id', '=', 'v.id')
->where('t1.user_id', 265)
->get();
