Laravel输出与MySQL查询不同的输出 [英] Laravel Outputs Different Output Than MySQL Query
本文介绍了Laravel输出与MySQL查询不同的输出的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我生成了一个MySQL查询，该查询提供了我所需的正确输出(在MySQL Workbench上进行了测试).但是，当我使用Laravel DB :: select(DB :: raw())应用相同的SQL查询时，我会收到完全不同的输出.非常感谢您提供一些有关如何克服这一问题的建议.
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.
我一直在修改代码以找出问题所在.当从Laravel运行查询时，我在SQL中生成的数字序列显然变得混乱.但是原始查询在MySQL中工作得很好.我在Laravel代码中添加了SET语句.但是当我通过MySQL(Workbench或PHPMyAdmin)运行查询时，这不是必需的.
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).
我的要求:
- 要删除GROUP BY WITH ROLLUP导致的重复值
SQL查询:
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
必需的输出:
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输出:
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代码:
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"));
推荐答案
我设法解决了问题，但是没有使用Laravel ORM.我真的不知道为什么它不能与laravel一起使用，而不能与php mysqli函数一起使用.我认为问题是我在使用ORM方法时没有初始化MySQL变量.
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.
我只是使用php内置的mysql方法，所以一切都很好.
I just used the php inbuilt mysql methods and voilà all is well.
解决方案:
$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);
如果有人可以指出我的ORM解决方案，那就太好了！
If anyone could point me towards an ORM solution for this would be great!
这篇关于Laravel输出与MySQL查询不同的输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
