CODEWITHSUNDEEP
phpsqllaravellaravel-4eloquent
user1894292
user1894292

Reputation: 859

Complex-ish query in Laravel (Eloquent)

I've read the Laravel documentation but I can't quite work this one out.

Does anyone know if this query is possible in Eloquent?

If so, how would you write it?

SELECT 

MONTHNAME(postdate) as monthname, 
DATE_FORMAT(postdate,'%m') as month, 
YEAR(postdate) as year, 
COUNT(MONTHNAME(postdate)) as num 

FROM 
postData 

WHERE 
status = 1 

GROUP BY 
monthname,
year 

ORDER BY 
postdate DESC

Upvotes: 0

Views: 552

Answers (2)

Kylie
Kylie

Reputation: 11759

If you don't mind using DB::raw() just do this...

Easy copy...

DB::select(DB::raw("SELECT MONTHNAME(postdate) as monthname, DATE_FORMAT(postdate,'%m') as month, YEAR(postdate) as year, COUNT(MONTHNAME(postdate)) as num FROM postData WHERE status = 1 GROUP BY monthname,year ORDER BY postdate DESC"));

One line...

  DB::select(DB::raw("SELECT MONTHNAME(postdate) as monthname, DATE_FORMAT(postdate,'%m') as month,YEAR(postdate) as year, COUNT(MONTHNAME(postdate)) as num FROM postData WHERE status = 1 GROUP BYmonthname,year ORDER BY postdate DESC"));

Upvotes: 2

rmobis
rmobis

Reputation: 27021

While simply using DB::raw for the whole query does work, it doesn't feel right to me. You might wanna try this solution using the Query Builder.

DB::table('postData')->select([
    DB::raw('MONTHNAME(postdate) AS monthname'),
    DB::raw('DATE_FORMAT(postdate, \'%m\') AS month'),
    DB::raw('YEAR(postdate) AS year'),
    DB::raw('COUNT(MONTHNAME(postdate)) AS num'),
])->where('status', 1)
  ->groupBy('monthname', 'year')
  ->orderBy('postdate', 'DESC');

It still uses DB::raw, but only for the select clause. Anyway, you could have used a PostData model instead of DB::table('postData'), but since it really wouldn't be a regular PostData object, I'd advise against it.

Upvotes: 3

Related Questions