CODEWITHSUNDEEP
phpsqllaravel
eivl
eivl

Reputation: 148

Creating a Laravel query from SQL with Join and alias

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.

Upvotes: 1

Views: 1379

Answers (1)

fmgonzalez
fmgonzalez

Reputation: 823

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.

Upvotes: 2

Related Questions