Laravel Query Build with nested Right Joins

Question

I have a query that works but I need to get it into the Query Builder:

RIGHT JOIN (tlkpSUDesc RIGHT JOIN tblSU ON tlkpSUDesc.SUDescCode = tblSU.SUDescCode) ON tlkpSUTyp.SUTypCode = tblSU.SUTypCode

I can figure out how to do it. I tired this:

->rightJoin('tlkpSUDesc', function($join) {
$join->on('tblSU', 'tlkpSUDesc.SUDescCode', '=', 'tblSU.SUDescCode');
}
'tlkpSUTyp.SUTypCode', '=', 'tblSU.SUTypCode')

But that didn't work. I also tried

->rightJoin('tlkpSUDesc', 'tlkpSUTyp.SUTypCode', '=', 'tblSU.SUTypCode')
->rightJoin('tblSU', 'tlkpSUDesc.SUDescCode', '=', 'tblSU.SUDescCode')

But that didn't work either.

Thanks!

I never saw that type of nested joins... I recommend you 100% to use relationships and drop those weird and complex queries... what you can do (if ->rightJoin does not work), is use a raw query (laravel.com/docs/9.x/queries#raw-expressions, see if any of the next stuff in that documentation helps) — matiaslauriti
– matiaslauriti, Commented Oct 25, 2022 at 20:31
@matiaslauriti I am not sure what you mean by "I recommend you 100% to use relationships ". Sorry I am new to this and trying to navigate through. — stanhook
– stanhook, Commented Oct 26, 2022 at 11:54

Shawn Lindstrom · Accepted Answer · 2022-10-25 20:44:18Z

1

I agree with @matiaslauriti that there's nothing wrong with reaching for raw expressions. But, since you asked and it can be done with the query builder, try this:

$query = \DB::query();

$query->rightJoin('tlkpSUDesc', function ($query) {
  $query->rightJoin('tblSu', 'tlkpSUDesc.SUDescCode', '=', 'tblSU.SUDescCode')->on(
    'tlkpSUTyp.SUTypCode', '=', 'tblSU.SUTypCode');
})->toSql();

answered Oct 25, 2022 at 20:44

Shawn Lindstrom

73111 silver badges17 bronze badges

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

4 Comments

stanhook Over a year ago

I did get this to work (the whole query): ``` $sql = "SELECT tblSU.Site, tlkpSUDesc.ESR_SUcat, tlkpSUDesc.ESR_SUcatdisp, tlkpSUDesc.SUDesc, tlkpSUTyp.SUTyp, tblSU.SUNum FROM tlkpSUTyp RIGHT JOIN (tlkpSUDesc RIGHT JOIN tblSU ON tlkpSUDesc.SUDescCode = tblSU.SUDescCode) ON tlkpSUTyp.SUTypCode = tblSU.SUTypCode WHERE (((tblSU.Site) = '$id') AND ((tlkpSUDesc.ESR_SUcat) = '$category') AND (Not (tblSU.SUTypCode) = 'GEN')) ORDER BY tblSU.Site, tlkpSUDesc.ESR_SUcatdisp, tblSU.SUDescCode"; $selectStructures = DB::select($sql); ''' Any issues doing it this way?

Shawn Lindstrom Over a year ago

I'd say just use the query directly using PDO: $statement = \DB::getPdo()->prepare($sql); $statement->setFetchMode(\PDO::FETCH_OBJ); $statement->execute(['id' => $id, 'category' => $category]); $result = collect($statement->fetchAll()); Bind id and category by replacing the variables with :id and :category. Gather up the results in a collection as that's all you'd be missing. Sometimes it's better to unshackle yourself from the query builder.

Shawn Lindstrom Over a year ago

Gist: gist.github.com/shawnlindstrom/46ec3710d50fbacffbd7699b26cd1dee

stanhook Over a year ago

@ShawnLindstron Your other comment was really help and i rebuilt my queries using get Pdo. Thanks!!

Collectives™ on Stack Overflow

Laravel Query Build with nested Right Joins

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related