How to Convert SQL Query to Eloquent Laravel

Question

MY SQL Query: SELECT * ,COUNT(posts_id) as `view `FROM `post_view` JOIN posts ON post_view.posts_id = posts.id GROUP BY posts_id My Eloquent Laravel:

$this->model->Join('posts','post_view.posts_id', '=', 'posts.id')
          ->selectRaw('*, count(posts_id) as view')
          ->groupBy('posts_id')
          ->get();

Error when Get by Postman:

SQLSTATE[42000]: Syntax error or access violation: 1055 'databasePost.post_view.id' isn't in GROUP BY
        (SQL: select *, count(posts_id) as view from `post_view` inner join `posts` on `post_view`.`posts_id` =
        `posts`.`id` group by `posts_id`)

what have you tried ? and what variable you set in related model ? — Niklesh Raut
– Niklesh Raut, Commented Dec 7, 2019 at 7:20
tou have to set model for that and then you can use that model class for laravel eloquent — Devsi Odedra
– Devsi Odedra, Commented Dec 7, 2019 at 7:27

TsaiKoga · Accepted Answer · 2019-12-07 08:11:47Z

2

This error occurs because group by requiring all columns, is the speed of the query.

I think you are using a MySQL server with version 5.7.5+. Check this out: MySQL Handling of GROUP BY

Method 1:

Use ANY_VALUE to the column or disable mysql mode:ONLY_FULL_GROUP_BY.

->selectRaw('ANY_VALUE(post_view.id), ... count(posts_id) as view')
          ->groupBy('posts_id')

PS: post_view.id and posts.id both named id, select them all out that one of them will be covered.

Method 2:

edit your applications's database config file config/database.php

In mysql array, set strict => false to disable MySQL's strict mode:

'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            ...
            'strict' => false,
        ],

answered Dec 7, 2019 at 8:11

TsaiKoga

13.4k2 gold badges23 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

ltt88 Over a year ago

Temporarily I used the method 2.

apokryfos Over a year ago

@ltt88 Using either of these methods may hide some rows. Make sure you know what you're doing when using them

Collectives™ on Stack Overflow

How to Convert SQL Query to Eloquent Laravel

1 Answer 1

Method 1:

Method 2:

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Method 1:

Method 2:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related