[FIXED] How to retrieve the data from Eloquent or raw Query in Laravel?

Issue

I need to retrieve data from DB Week of the month and dates am able to get data from MYSQL Query

SELECT cnt, `creationdate`, week, weekname, 
DATE_ADD(firstOfMonth,INTERVAL (week-1) WEEK) as 'Week Start',
 IF(DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY) > eom,
 eom, DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY)) as 'Week End' 
FROM (
 SELECT COUNT(`firstname`) AS 'cnt', `creationdate`, 
FLOOR((DAYOFMONTH(`creationdate`) - 1) / 7 +1) AS week, 
CONCAT('Week ',FLOOR((DAYOFMONTH(`creationdate`) - 1) / 7) +1) AS weekname, DATE_ADD(`creationdate`,interval -DAY(`creationdate`)+1 DAY) AS firstOfMonth, 
LAST_DAY(`creationdate`) as 'eom' 
FROM `UserDetails` WHERE DATE_FORMAT(`creationdate`,'%m/%Y')='06/2017' GROUP BY week ) a

Am converting to laravel Raw Query was

$monthOfWeaks = DB::table("UserDetails")
            ->select(DB::raw('cnt, creationdate, week, weekname,
DATE_ADD(firstOfMonth,INTERVAL (week-1) WEEK) as WeekStart,
DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY) > eom, eom, DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY)) as WeekEnd'))
->select(DB::raw('COUNT(`firstname`) as cnt, creationdate,
 FLOOR((DAYOFMONTH(creationdate) - 1) / 7 +1) as week,
 CONCAT(Week,FLOOR((DAYOFMONTH(creationdate) - 1) / 7) +1) as weekname,
 DATE_ADD(creationdate,interval -DAY(creationdate)+1 DAY) as firstOfMonth,
 LAST_DAY(creationdate) as eom'))
            ->whereRaw("DATE_FORMAT(creationdate,'%m/%Y')='06/2017')")
            ->groupBy(DB::raw("WEEK(creationdate)"))
            ->get();

It seems like some error but I could not fix in laravel please give some input to get solve my issues? Thanks.

Solution

you can use

DB::select("
SELECT cnt, `creationdate`, week, weekname, 
DATE_ADD(firstOfMonth,INTERVAL (week-1) WEEK) as 'Week Start',
 IF(DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY) > eom,
 eom, DATE_ADD(firstOfMonth,INTERVAL ((week-1)*7+6) DAY)) as 'Week End' 
FROM (
 SELECT COUNT(`firstname`) AS 'cnt', `creationdate`, 
FLOOR((DAYOFMONTH(`creationdate`) - 1) / 7 +1) AS week, 
CONCAT('Week ',FLOOR((DAYOFMONTH(`creationdate`) - 1) / 7) +1) AS weekname, DATE_ADD(`creationdate`,interval -DAY(`creationdate`)+1 DAY) AS firstOfMonth, 
LAST_DAY(`creationdate`) as 'eom' 
FROM `UserDetails` WHERE DATE_FORMAT(`creationdate`,'%m/%Y')='06/2017' GROUP BY week ) a
");

if you don't have any parameter to add to the query that comes from the request/other third party (and so there is no SQL injection risk)

Answered By - Alberto Sinigaglia