Ad

How To Convert An SQL Sub Query In Laravel Query Builder?

- 1 answer

The following is my SQL query. I'm googling to convert Laravel Query Builder but can't find a good solution. Please help me.

SELECT p.WARD AS `Ward`
    , (SELECT COUNT(p1.SEX) FROM persons AS p1 WHERE p1.SEX=1 AND p.`WARD`=p1.WARD) AS `Male`
    , (SELECT COUNT(p1.SEX) FROM persons AS p1 WHERE p1.SEX=2 AND p.`WARD`=p1.WARD) AS `Female`
    , (SELECT COUNT(p1.SEX) FROM persons AS p1 WHERE p1.SEX=3 AND p.`WARD`=p1.WARD) AS `Other`
FROM persons AS p
GROUP BY p.`WARD` ORDER BY p.`WARD` ASC;
Ad

Answer

Here you can execute raw sql queries in laravel using just DB::select('');

Check the following. Hope solves your issue.

 $data = DB::select('SELECT p.WARD AS `Ward`
    , (SELECT COUNT(p1.SEX) FROM persons AS p1 WHERE p1.SEX=1 AND p.`WARD`=p1.WARD) AS `Male`
    , (SELECT COUNT(p1.SEX) FROM persons AS p1 WHERE p1.SEX=2 AND p.`WARD`=p1.WARD) AS `Female`
    , (SELECT COUNT(p1.SEX) FROM persons AS p1 WHERE p1.SEX=3 AND p.`WARD`=p1.WARD) AS `Other`
FROM persons AS p
GROUP BY p.`WARD` ORDER BY p.`WARD` ASC;');

Ad
source: stackoverflow.com
Ad