I have following query:
SELECT
cd_4.userfield,
COUNT( DISTINCT (Case When cd_4.disposition_Last = 'ANSWERED' Then cd_4.uniqueid ELSE NULL END) ) AS Answered_Count,
COUNT( DISTINCT (Case When cd_4.disposition_Last <> 'ANSWERED' Then cd_4.uniqueid ELSE NULL END) ) AS Not_Answered_Count
FROM (
SELECT
cd_2.Userfield,
cd_2.Start_Date,
cd_2.uniqueid,
cd_2.Row_Id_Max,
cd_3.disposition AS disposition_Last
FROM (
SELECT
cd_1.userfield,
cd_1.Start_Date,
cd_1.uniqueid,
MAX(cd_1.Row_Id) AS Row_Id_Max
FROM
cdrnew As cd_1
WHERE (
cd_1.Start_Date BETWEEN '2020-12-01' AND '2020-12-10'
)
AND cd_1.userfield = 'Inbound'
GROUP BY
cd_1.userfield,
cd_1.Start_Date,
cd_1.uniqueid
) AS cd_2
INNER JOIN cdrnew As cd_3
ON cd_3.Row_Id = cd_2.Row_Id_Max
WHERE (
cd_3.Start_Date BETWEEN '2020-12-01' AND '2020-12-10'
)
AND cd_3.userfield = 'Inbound'
) AS cd_4 ;
I have 4 Aliases used for above for table cdrnew named cd_1, cd_2, cd_3 and cd_4
I want to achieve the same using laravel query builder:
I tried with following laravel query:
$sqlquery = DB::table('cdrnew')->select('cdrnew.userfield')
->addSelect(DB::raw('COUNT(DISTINCT (
Case When cdrnew.disposition = "ANSWERED" Then cdrnew.uniqueid ELSE NULL END
)) as Answered_Count'))
->addSelect(DB::raw('COUNT(DISTINCT (
Case When cdrnew.disposition <> "ANSWERED" Then cdrnew.uniqueid ELSE NULL END
)) as Not_Answered_Count'))
->addSelect(DB::raw('cdrnew.Start_Date, cdrnew.uniqueid, MAX(cdrnew.Row_Id) AS Row_Id_Max'))
->whereBetween('cdrnew.Start_Date', ["2020-12-12", "2020-12-15"])
->groupBy('userfield','Start_Date','uniqueid')
->join('cdrnew AS cd_1',"cdrnew.Row_Id", "=", "cd_1.Row_Id")
->limit(10)->get();
But I am only able to create two aliases. One which is default from DB::Table(cdrnew) and another from join(cd_1). How can we create multiple 'from' statements along with different aliases in laravel ?
E.g:
- from cdrnew as cd_1 (Aliases 1).
- from cdrnew as cd_2 (Aliases 2).
- from cdrnew as cd_3 (Aliases 3).
- ..... and so on
via Chebli Mohamed
Aucun commentaire:
Enregistrer un commentaire