Laravel Query Builder Nested Join and Alias

Question

How to create this query using Laravel's Query Builder:

SELECT `content`.`tagable_id`,`taged`.`tag_id`
FROM `taged`
RIGHT JOIN
    (SELECT `taged`.`tagable_id`,`taged`.`tag_id`
    FROM `taged`
    WHERE `taged`.`mask_flag`='0'
        AND `taged`.`tagable_type`='App\\\Post'
        AND `taged`.`user_id`='1') AS `content`
ON `taged`.`mask_flag`='1'
    AND `content`.`tagable_id`=`taged`.`tagable_id`
    AND `taged`.`tagable_type`='App\\\Post'
    AND `taged`.`user_id`='1'

The parenthesis around the inner SELECT taged.tagable_id... is where my main problem lies.

I have been trying it and haven't been able to get it to work yet. It produces a number of errors. Thank you though. — CrackingTheCode
– CrackingTheCode, Commented Aug 25, 2015 at 4:12
What are those errors? Try moving the WHERE conditions out of the join function as I have updated below — Tim Biegeleisen
– Tim Biegeleisen, Commented Aug 25, 2015 at 4:15

Tim Biegeleisen · Accepted Answer · 2015-08-25 04:14:49Z

2

Here is your Laravel query:

$result = DB::table('taged')
             ->select('content.tagable_id', 'taged.tag_id')
             ->rightJoin(DB::raw('SELECT tagable_id, tag_id FROM taged AS content'), function($join)
         {
             $join->on('taged.mask_flag', '=', 1);
             $join->on('content.tagable_id', '=', 'taged.tagable_id');
             $join->on('taged.tagable_type', '=', 'App\\\Post');
             $join->on('taged.user_id', '=', 1);
         })
         ->where('content.mask_flag', '=', 0);
         ->where('content.tagable_type', '=', 'App\\\Post');
         ->where('content.user_id', '=', 1);
         ->get();

edited Aug 25, 2015 at 4:14

answered Aug 24, 2015 at 6:57

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel Query Builder Nested Join and Alias

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related