Creating a Laravel query from SQL with Join and alias

Question

I have written a SQL query in Microsoft SQL Management Studio i want to write it in Laravel.

Here is my SQL query.

SELECT Ordre.OrdreId, Ordre.BestiltDato, Ordre.PlanlagtFerdigDato, Ordre.FerdigDato,Ordre.FakturaDato, OAA11.Fritekst, OAA09.Dato, OAA26.Dato
from Ordre
LEFT OUTER JOIN OrdreArbeidAvdeling OAA11 ON( OAA11.AvdelingsId = 11 AND OAA11.OrdreId = Ordre.OrdreId )
LEFT OUTER JOIN OrdreArbeidAvdeling OAA09 ON( OAA09.AvdelingsId =  9 AND OAA09.OrdreId = Ordre.OrdreId )
LEFT OUTER JOIN OrdreArbeidAvdeling OAA26 ON( OAA26.AvdelingsId = 26 AND OAA26.OrdreId = Ordre.OrdreId )

Where Ordre.BestiltDato > '01.01.2013' AND Ordre.FerdigDato IS NULL AND Ordre.FakturaDato IS NULL and Ordre.PlanlagtFerdigDato < '12.24.2014'
AND (lower(OAA11.Fritekst) not LIKE '%avven%')
AND (OAA09.Dato IS NULL OR (OAA09.Dato IS NOT NULL and OAA26.Dato IS NOT NULL))
AND (lower(OAA11.Fritekst) NOT LIKE '%2015%')

Using the documentation from Laravel i am able to do all but one join, the one where it uses different "AvdelingsId" ( 9 and 26) in one where statement.

AND (OAA09.Dato IS NULL OR (OAA09.Dato IS NOT NULL and OAA26.Dato IS NOT NULL))

my Laravel code at this point looks like this.

$orders = DB::table('Ordre')->join('OrdreArbeidAvdeling', function($OAA11)
{
$OAA11->on('OrdreArbeidAvdeling.OrdreId','=','Ordre.OrdreId')
    ->where('OrdreArbeidAvdeling.AvdelingsId', '=', '11')
    ->where('OrdreArbeidAvdeling.Fritekst', 'not like', '%avven%')
    ->where('OrdreArbeidAvdeling.Fritekst', 'not like', '%2015%');
},'left outer')

/*->join('OrdreArbeidAvdeling', function($OAA9and26){
$OAA9and26->on('OrdreArbeidAvdeling.OrdreId','=','Ordre.OrdreId')
#What am i suppose to do with AvdelingsId = 9 and AvdelingsId = 26
#
;
},'left outer')*/


->select('Ordre.OrdreId', 'Ordre.BestiltDato', 'Ordre.PlanlagtFerdigDato', 'Ordre.FerdigDato','Ordre.FakturaDato')
    ->where('BestiltDato', '>', '2013-01-01')
    ->where('PlanlagtFerdigDato', '<', '2014-12-24')
    ->whereNull('FerdigDato')
    ->whereNull('FakturaDato')
->orderBy('OrdreId')
->get();

If i where able to use alias name i might just be able to make this work. I have also limited knowledge of Laravel, what i know i have found in the documentation, any suggestions on how to do this would be very kind.

Do you know you can use alias in the join? Something like this ->join('OrdreArbeidAvdeling as OAA11', ...). — fmgonzalez
– fmgonzalez, Commented Nov 26, 2014 at 10:59
no i did not know that. i will try that and maybe answer my own question ;) — eivl
– eivl, Commented Nov 26, 2014 at 11:08

fmgonzalez · Accepted Answer · 2014-11-26 11:10:44Z

2

You should try to add alias in the join, as I said in the comments.

Something like this:

...->join('OrdreArbeidAvdeling as OAA11', ...)

I hope it works fine for you.

answered Nov 26, 2014 at 11:10

fmgonzalez

8231 gold badge7 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

Creating a Laravel query from SQL with 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