Laravel Outputs Different Output Than MySQL Query

问题:

I generated a MySQL query which gives the correct output I require (tested on MySQL Workbench). But when I apply the same SQL query using Laravel DB::select(DB::raw()) I'm receiving a totally different output. Would really appreciate some advice on how to overcome this.

I've been tinkering with the code to figure out what is going wrong. Apparently the number sequence I am generating in the SQL is going haywire when the query is run from Laravel. But the raw query works perfectly fine in MySQL. I have added the SET statement in the Laravel code. But it is not necessary when I am running the query through MySQL (Workbench or PHPMyAdmin).

My requirement:

• To remove repeating values resulted by a GROUP BY WITH ROLLUP

SQL query:

SELECT hodname, year, month, ptype, pcode, rname, sah, sae, sbe
FROM
    (SELECT
         (CASE tblhod.hodname
             WHEN @curHOD THEN @curHODRow:=@curHODRow+1
             ELSE @curHODRow:=1
          END) AS seqhodname,
         @curHOD:=tblhod.hodname AS hodnamedata,
         (CASE 
             WHEN @curHODRow = 1 THEN tblhod.hodname
             ELSE ''
          END) AS hodname,
         (CASE tblhod.hodname
             WHEN @curHOD 
                THEN CASE tblperiod.year
                        WHEN @curYear THEN @curYearRow:=@curYearRow+1
                        ELSE @curYearRow:=1
                     END
          END) AS seqyear,
         @curYear:=tblperiod.year AS yeardata, 
         (CASE 
             WHEN @curYearRow = 1 THEN tblperiod.year
             ELSE ''
          END) AS year,
         (CASE tblhod.hodname
             WHEN @curHOD 
                THEN CASE tblperiod.year
                        WHEN @curYear 
                           THEN CASE tblperiod.month
                                   WHEN @curMonth THEN @curMonthRow:=@curMonthRow + 1
                                   ELSE @curMonthRow:=1
                                END
                     END
          END) AS seqmonth,
         @curMonth:=tblperiod.month AS monthdata,
        (CASE WHEN @curMonthRow=1 THEN tblperiod.month
ELSE ''
END) AS month,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN @curPtypeRow:=@curPtypeRow+1
ELSE @curPtypeRow:=1
END
END
END
END) AS seqptype,
@curPtype:=tblprotype.ptype AS ptypedata,
(CASE WHEN @curPtypeRow=1 THEN tblprotype.ptype
ELSE ''
END) AS ptype,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN CASE tblproject.pcode
WHEN @curPcode THEN @curPcodeRow:=@curPcodeRow+1
ELSE @curPcodeRow:=1
END
END
END
END
END) AS seqpcode,
@curPcode:=tblproject.pcode AS pcodedata,
(CASE WHEN @curPcodeRow=1 THEN tblproject.pcode
ELSE ''
END) AS pcode,
tblresource.rname AS rname, SUM(ah) AS sah, SUM(ae) AS sae, SUM(be) AS sbe
FROM `tblallocation`
JOIN tblhod ON tblallocation.hodid = tblhod.hodid
JOIN tblperiod ON tblallocation.periodid = tblperiod.periodid
JOIN tblprotype ON tblallocation.ptid = tblprotype.ptid
JOIN tblproject ON tblallocation.pid = tblproject.pid
JOIN tblresource ON tblallocation.rid = tblresource.rid
JOIN(SELECT @curHODRow:=0, @curHOD:=0,@curYearRow:=0, @curYear:=0, @curMonthRow:=0, @curMonth:=0, @curPtypeRow:=0, @curPtype:=0, @curPcodeRow:=0, @curPcode:=0) v
GROUP BY tblhod.hodname, tblperiod.year, tblperiod.month, tblprotype.ptype, tblproject.pcode, tblresource.rname WITH ROLLUP) AS final

Required Output:

COL1     COL2     COL3     COL4        COL5     COL6
-----------------------------------------------------
GRP1     GRP1.1   GRP1.1.1 GRP1.1.1.1   15       32
                           GRP1.1.1.2   26        5
                           GRP1.1.1.3   10       17
                                        51       54
                  GRP1.1.2 GRP1.1.2.1    2        1  
                           GRP1.1.2.2   31       24
                           GRP1.1.2.3    7       13
                           GRP1.1.2.4   17       15
                                        57       53
                                       108      107
         GRP1.2   GRP1.2.1 GRP1.2.1.1    9        3
                           GRP1.2.1.2    6        6
                                        15        9
                  GRP1.2.2 GRP1.2.2.1   10       10
                                        10       10   
                  GRP1.2.3 GRP1.2.3.1    4        3
                           GRP1.2.3.2    8        2
                           GRP1.2.3.3    1        1
                                        13        6
                                        38       25
                                       146      132
GRP2     GRP2.1   GRP2.1.1 GRP2.1.1.1   22       34
                           GRP2.1.1.2   11       30
                                        33       64
                  GRP2.1.2 GRP2.1.2.1   32       23
                                        32       23
                                        65       87
         GRP2.2   GRP2.2.1 GRP2.2.1.1    2        2
                                         2        2
                                         2        2
                                        67       89
                                       213      222

Laravel Output:

COL1     COL2     COL3     COL4        COL5     COL6
-----------------------------------------------------
                           GRP1.1.1.2   15       32
GRP1                       GRP1.1.1.2   26        5
                           GRP1.1.1.3   10       17
                                        51       54
                           GRP1.1.2.1    2        1  
                           GRP1.1.2.2   31       24
                           GRP1.1.2.3    7       13
                           GRP1.1.2.4   17       15
                                        57       53
                                       108      107
                           GRP1.2.1.1    9        3
                           GRP1.2.1.2    6        6
                                        15        9
                           GRP1.2.2.1   10       10
                                        10       10   
                           GRP1.2.3.1    4        3
                           GRP1.2.3.2    8        2
                           GRP1.2.3.3    1        1
                                        13        6
                                        38       25
                                       146      132
                           GRP2.1.1.1   22       34
                           GRP2.1.1.2   11       30
                                        33       64
                           GRP2.1.2.1   32       23
                                        32       23
                                        65       87
                           GRP2.2.1.1    2        2
                                         2        2
                                         2        2
                                        67       89
                                       213      222

Laravel Code:

DB::statement(DB::raw('SET @curHODRow=0, @curHOD=0,@curYearRow=0, @curYear=0, @curMonthRow=0, @curMonth=0, @curPtypeRow=0, @curPtype=0, @curPcodeRow=0, @curPcode=0'));
DB::select(DB::raw("SELECT hodname, year, month, ptype, pcode, rname, sah, sae, sbe
FROM
(SELECT
(CASE tblhod.hodname
WHEN @curHOD THEN @curHODRow:=@curHODRow+1
ELSE @curHODRow:=1
END) AS seqhodname,
@curHOD:=tblhod.hodname AS hodnamedata,
(CASE WHEN @curHODRow=1 THEN tblhod.hodname
ELSE ''
END) AS hodname,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN @curYearRow:=@curYearRow+1
ELSE @curYearRow:=1
END
END) AS seqyear,
@curYear:=tblperiod.year AS yeardata, 
(CASE WHEN @curYearRow=1 THEN tblperiod.year
ELSE ''
END) AS year,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN @curMonthRow:=@curMonthRow+1
ELSE @curMonthRow:=1
END
END
END) AS seqmonth,
@curMonth:=tblperiod.month AS monthdata,
(CASE WHEN @curMonthRow=1 THEN tblperiod.month
ELSE ''
END) AS month,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN @curPtypeRow:=@curPtypeRow+1
ELSE @curPtypeRow:=1
END
END
END
END) AS seqptype,
@curPtype:=tblprotype.ptype AS ptypedata,
(CASE WHEN @curPtypeRow=1 THEN tblprotype.ptype
ELSE ''
END) AS ptype,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN CASE tblproject.pcode
WHEN @curPcode THEN @curPcodeRow:=@curPcodeRow+1
ELSE @curPcodeRow:=1
END
END
END
END
END) AS seqpcode,
@curPcode:=tblproject.pcode AS pcodedata,
(CASE WHEN @curPcodeRow:=1 THEN tblproject.pcode
ELSE ''
END) AS pcode,
tblresource.rname AS rname, SUM(ah) AS sah, SUM(ae) AS sae, SUM(be) AS sbe
FROM `tblallocation`
JOIN tblhod ON tblallocation.hodid = tblhod.hodid
JOIN tblperiod ON tblallocation.periodid = tblperiod.periodid
JOIN tblprotype ON tblallocation.ptid = tblprotype.ptid
JOIN tblproject ON tblallocation.pid = tblproject.pid
JOIN tblresource ON tblallocation.rid = tblresource.rid
JOIN(SELECT @curHODRow:=0, @curHOD:=0,@curYearRow:=0, @curYear:=0, @curMonthRow:=0, @curMonth:=0, @curPtypeRow:=0, @curPtype:=0, @curPcodeRow:=0, @curPcode:=0) v
GROUP BY tblhod.hodname, tblperiod.year, tblperiod.month, tblprotype.ptype, tblproject.pcode, tblresource.rname WITH ROLLUP) AS final"));

回答1:

I managed to solve my problem, but not using the Laravel ORM. I don't really know why it didn't work with laravel but worked with php mysqli functions. I think the issue is that the MySQL variables aren't getting initialized when I am using the ORM methods.

I just used the php inbuilt mysql methods and voilà all is well.

Solution:

$query = SELECT hodname, year, month, ptype, pcode, rname, sah, sae, sbe
FROM
(SELECT
(CASE tblhod.hodname
WHEN @curHOD THEN @curHODRow:=@curHODRow+1
ELSE @curHODRow:=1
END) AS seqhodname,
@curHOD:=tblhod.hodname AS hodnamedata,
(CASE WHEN @curHODRow=1 THEN tblhod.hodname
ELSE ''
END) AS hodname,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN @curYearRow:=@curYearRow+1
ELSE @curYearRow:=1
END
END) AS seqyear,
@curYear:=tblperiod.year AS yeardata, 
(CASE WHEN @curYearRow=1 THEN tblperiod.year
ELSE ''
END) AS year,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN @curMonthRow:=@curMonthRow+1
ELSE @curMonthRow:=1
END
END
END) AS seqmonth,
@curMonth:=tblperiod.month AS monthdata,
(CASE WHEN @curMonthRow=1 THEN tblperiod.month
ELSE ''
END) AS month,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN @curPtypeRow:=@curPtypeRow+1
ELSE @curPtypeRow:=1
END
END
END
END) AS seqptype,
@curPtype:=tblprotype.ptype AS ptypedata,
(CASE WHEN @curPtypeRow=1 THEN tblprotype.ptype
ELSE ''
END) AS ptype,
(CASE tblhod.hodname
WHEN @curHOD THEN CASE tblperiod.year
WHEN @curYear THEN CASE tblperiod.month
WHEN @curMonth THEN CASE tblprotype.ptype
WHEN @curPtype THEN CASE tblproject.pcode
WHEN @curPcode THEN @curPcodeRow:=@curPcodeRow+1
ELSE @curPcodeRow:=1
END
END
END
END
END) AS seqpcode,
@curPcode:=tblproject.pcode AS pcodedata,
(CASE WHEN @curPcodeRow=1 THEN tblproject.pcode
ELSE ''
END) AS pcode,
tblresource.rname AS rname, SUM(ah) AS sah, SUM(ae) AS sae, SUM(be) AS sbe
FROM `tblallocation`
JOIN tblhod ON tblallocation.hodid = tblhod.hodid
JOIN tblperiod ON tblallocation.periodid = tblperiod.periodid
JOIN tblprotype ON tblallocation.ptid = tblprotype.ptid
JOIN tblproject ON tblallocation.pid = tblproject.pid
JOIN tblresource ON tblallocation.rid = tblresource.rid
JOIN(SELECT @curHODRow:=0, @curHOD:=0,@curYearRow:=0, @curYear:=0, @curMonthRow:=0, @curMonth:=0, @curPtypeRow:=0, @curPtype:=0, @curPcodeRow:=0, @curPcode:=0) v
GROUP BY tblhod.hodname, tblperiod.year, tblperiod.month, tblprotype.ptype, tblproject.pcode, tblresource.rname WITH ROLLUP) AS final;

$con = mysqli_connect('host', 'username', 'password', 'database');
$result = mysqli_query($con, $query);
$data = mysqli_fetch_all($result);

If anyone could point me towards an ORM solution for this would be great!