phpmyadmin Question posted in
The official documentation can be found here.

How can I use GroupBy in Laravel without turning things off – Phpmyadmin

The following code isn’t working:

AppModelsNews::groupBy("category")->orderBy('pubDate', 'DESC')->limit(21)->get();

It says id isn’t in the group by ….
I tried using DB::table and even raw query (DB::raw) but result was the same.
I googled and somebody said it’s because of the changes in newer version of MySQL.
So I got the generated query by the following:

$res =AppModelsNews::groupBy("category")->orderBy('pubDate', 'DESC')->limit(21)->toSql(); 
     echo "<div style='display:none'>n   ".print_r($res,true)." n</div>";

And It gives me this:

  select * from `news` group by `category` order by `pubDate` desc limit 21

I copied and pasted it to PhpMyAdmin and it works fine.

Another one said you should disable strict mode in config/database.php. But what if I don’t want to change any configs?
I don’t think strict mode being enabled means I can’t use groupBy anymore. Right?

If I’m right just tell how should I make the above code work.

Please don’t give me links to the docs. They’re hard for me to understand.

All I want is a simplest sql query which has a "groupBy" statment and works without turning something off.

ThankYou

Edit

Table structure for table news

|Column|Type|Null|Default

|//id//|int(11)|No|

|title|varchar(200)|No|

|description|varchar(500)|No|

|link|varchar(300)|No|

|source|varchar(50)|No|

|category|varchar(20)|Yes|NULL

|pubDate|varchar(40)|No|

|ts|datetime|No|current_timestamp()

Tags:

2

Answers


  1. 0

    try this :

     DB::select("SELECT * FROM `news` GROUP BY `category` ORDER BY `pubDate` DES limit 21")
    Login or Signup to reply.
  2. 0

    Use this

    News::select("*")->orderBy('pubDate', 'desc')->groupBy("category")->limit(21)->get();

    FYI

    In some cases, I had to use the below line before executing the query.

    DB::statement("set sql_mode='' ");
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search