Why Raw sql doesnt work in laravel DB

Question

I have this sentence:

$lastOperationUser = DB::select("SELECT last_operation FROM policies
                        INNER JOIN(users) 
                        ON (policies.user_id=users.id)
                        group by (user_id);");

If I execute in my phpmyadmin works fine but in laravel throw this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'test.policies.last_operation' isn't in GROUP BY (SQL: SELECT last_operation FROM policies INNER JOIN(users) ON (policies.user_id=users.id) group by (user_id);)

My model is:

USERS         POLICIES
id            id
name          last_operation
              user_id

One to many relationship

Any help please?

Possible duplicate of [Disable ONLY\_FULL\_GROUP\_BY](https://stackoverflow.com/questions/23921117/disable-only-full-group-by) — Ohgodwhy, Apr 11 '18 at 21:16
There can be multiple `last_operation` values per `user_id`. Which one do you want to get? — Jonas Staudenmeir, Apr 11 '18 at 21:18

score 0 · Accepted Answer · answered Apr 11 '18 at 22:48

0

Given the description of what you're looking for from various comments, the following should be close to what you're after:

SELECT user_id, MAX(last_operation) AS last_operation
  FROM policies
  JOIN users
    ON policies.user_id = users.id
  GROUP BY user_id

Best of luck.

answered Apr 11 '18 at 22:48

Bob Jarvis - Слава Україні

48,992
9
77
110

Thats worked. In the view I did an foreach: ´@foreach($lastOperationUser as $last_operation) @if( $last_operation->user_id == $user->id) {{ $last_operation->last_operation }} @endif @endforeach´ – Apr 12 '18 at 10:27

Jems · Answer 2 · 2018-04-12T17:16:43.847

0

The first is you have to make sure that your sql query can return something in your database tool like mysql or sqlite or etc.. After that change your code into like this:

$lastOperationUser = DB::table('policies')->join('users', 'users.id', '=', 'policies.user_id')
                                          ->select('last_operation')
                                          ->groupBy('user_id')
                                          ->get();

I think that you can use like this without use DB raw

if occurs error, go to config\database.php and set 'strict' => false

edited Apr 12 '18 at 17:16

answered Apr 12 '18 at 04:20

Jems

1,666
4
20
50

This sentence throw error: Syntax error or access violation: 1055 last_operation' isn't in GROUP BY – Apr 12 '18 at 11:10
If I insert last_operation in GROUP BY, now throw error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'user_id, last_operation' in 'group statement' – Apr 12 '18 at 11:12
go to your `config\database.php` and change `'strict' => false` – Jems Apr 12 '18 at 17:15

Why Raw sql doesnt work in laravel DB

2 Answers2