I have a pretty complicated sql query which works fine when I test it in phpMyAdmin or similar.
I am trying to get it working in laravel now but I am so stuck. It just always returns empty array.
Here is what I have tried:
$result = DB::select('SELECT sa.division, Sum(P) as games, Sum(W) as wins,
Sum(D) as draws, Sum(L) as losses,SUM(F) as goals, SUM(A) as conceded, SUM(Pts) as points FROM(SELECT gameday, division, home club, season, id, changes, penalties, ft, 1 P,
IF (changes=2 OR penalties=2 OR (homegoals < awaygoals AND changes!=1 AND penalties!=1),1,0) W,
IF (homegoals = awaygoals AND changes!=1 AND club!=2 AND penalties!=1 AND penalties!=2,1,0) D,
IF (changes=1 OR penalties=1 OR homegoals > awaygoals,1,0) L,
CASE WHEN changes=2 THEN 3 WHEN changes=1 THEN 0 ELSE awaygoals END F,
CASE WHEN changes=2 THEN 0 WHEN changes=1 THEN 3 ELSE homegoals END A,
CASE WHEN changes=2 OR penalties=2 OR (homegoals < awaygoals AND changes!=1) THEN 3 WHEN homegoals = awaygoals AND changes!=1 AND changes!=2 AND penalties!=1 AND penalties!=2 THEN 1 ELSE 0 END PTS
FROM games) as tot, clubs s, series sa WHERE tot.club=s.id AND tot.division = sa.id AND s.url="awayteam" AND tot.ft=1 GROUP BY tot.division ORDER BY tot.division
');
dd($result);
This is exactly how Laravel docs tell to do this.
What am I doing wrong? I would use query builder but my query is too complicated for that.
My query:
SELECT sa.division,
Sum(p) AS games,
Sum(w) AS wins,
Sum(d) AS draws,
Sum(l) AS losses,
Sum(f) AS goals,
Sum(a) AS conceded,
Sum(pts) AS points
FROM (SELECT gameday,
division,
home club,
season,
id,
changes,
penalties,
ft,
1 P,
IF (changes = 2
OR penalties = 2
OR ( homegoals < awaygoals
AND changes != 1
AND penalties != 1 ), 1, 0) W,
IF (homegoals = awaygoals
AND changes != 1
AND club != 2
AND penalties != 1
AND penalties != 2, 1, 0) D,
IF (changes = 1
OR penalties = 1
OR homegoals > awaygoals, 1, 0) L,
CASE
WHEN changes = 2 THEN 3
WHEN changes = 1 THEN 0
ELSE awaygoals
end F,
CASE
WHEN changes = 2 THEN 0
WHEN changes = 1 THEN 3
ELSE homegoals
end A,
CASE
WHEN changes = 2
OR penalties = 2
OR ( homegoals < awaygoals
AND changes != 1 ) THEN 3
WHEN homegoals = awaygoals
AND changes != 1
AND changes != 2
AND penalties != 1
AND penalties != 2 THEN 1
ELSE 0
end PTS
FROM games) AS tot,
clubs s,
series sa
WHERE tot.club = s.id
AND tot.division = sa.id
AND s.url = "awayteam"
AND tot.ft = 1
GROUP BY tot.division
ORDER BY tot.division