Laravel Query Builder Error On Query Statement

Question

I am trying to select from two tables . From people table need name and image url and from messages table need the last message sent/received by user.

In people the people id field is unique and message table have people id.

I wrote the simple query which does the task , but when I use the same in laravel it gives me an error saying "access violation"

Here is my sql statement which works when I run:

select people.id, people.first_name, people.last_name, people.img_url, user_messages.message, user_messages.created_at from people inner join user_messages on people.id = user_messages.people_id where people.user_id =1 group by people.id order by user_messages.created_at DESC

And this is the Laravel query with the error

$people = DB::table('people')->select('people.id','people.first_name','people.last_name','people.img_url' ,'user_messages.message','user_messages.created_at')
        ->join('user_messages','people.id','=','user_messages.people_id')
        ->where(['people.user_id' => $user_id])
        ->groupBy('people.id')
        ->orderBy('user_messages.created_at', 'desc')
        ->get();

Its not showing me the error message if I include all fields in , but then all the messages is selected.

@aynber . SQLSTATE[42000]: Syntax error or access violation: 1055 'project_name.people.first_name' isn't in GROUP BY (SQL: select `people`.`id`, `people`.`first_name`, `people`.`last_name`, `people`.`img_url`, `user_messages`.`message`, `user_messages`.`created_at` from `people` join `user_messages` on `people`.`id` = `user_messages`.`people_id` where (`people`.`user_id` = 1) group by `people`.`id` order by `user_messages`.`created_at` desc) — Stacy Thompson, Feb 23 '18 at 19:08
Possible duplicate of [MySQL : isn't in GROUP BY](https://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by) — aynber, Feb 23 '18 at 19:11
@aynber its showing me the error until I do not include all fields in the Group By clause , but then it selects all the messages — Stacy Thompson, Feb 23 '18 at 19:17

score 0 · Answer 1 · answered Feb 23 '18 at 19:29

0

$people = DB::table('people')
    ->join('user_messages','people.id','=','user_messages.people_id')
    ->select('people.id','people.first_name','people.last_name','people.img_url' ,'user_messages.message','user_messages.created_at')
    ->where(['people.user_id' => $user_id])
    ->groupBy('people.id')
    ->orderBy('user_messages.created_at', 'desc')
    ->get();

answered Feb 23 '18 at 19:29

syam

892
8
19

try removing group by. if it works, there is problem with group by and likewise.. – syam Feb 23 '18 at 19:38
I changed the config database.php to 'strict' => false, and this worked. Doesn't know if that is the right way to remove the error. But its not selecting the last message, instead it selects any msg . Explained here : https://stackoverflow.com/questions/39138090/eloquent-groupby-make-sqlstate42000-with-valid-sql-query-in-laravel-5-3?rq=1 – Stacy Thompson Feb 23 '18 at 19:56

Laravel Query Builder Error On Query Statement

1 Answers1