Why does not work SQL query Laravel?

Question

I have the following SQL query:

return User::where("users.id", $id)
            ->select(array('rating.rate', User::raw('count(offers.id) AS ofrs, count(announcements.id) as appts')))
            ->join('announcements', 'users.id', '=', 'announcements.user_id')
            ->join('offers', 'users.id', '=', 'offers.user_id')
            ->join('rating', 'users.id', '=', 'rating.user_id')
            ->group_by('ofrs')
            ->get();

It gives me an error:

BadMethodCallException in Builder.php line 2508: Call to undefined method Illuminate\Database\Query\Builder::group_by()

Why does not work group by?

Also as way I tried:

  return User::where("users.id", $id)
            ->selectRaw('rating.rate, count(offers.id) AS ofrs, count(announcements.id) as appts')
            ->groupBy('appts')
            ->groupBy('ofrs')
            ->join('announcements', 'users.id', '=', 'announcements.user_id')
            ->join('offers', 'users.id', '=', 'offers.user_id')
            ->join('rating', 'users.id', '=', 'rating.user_id')
            ->get();

Result:

SQLSTATE[42000]: Syntax error or access violation: 1056 Can't group on 'appts'

I have written SQL query directly in SQL:

select rating.rate, COUNT(offers.id) AS ofrs, COUNT(announcements.id) as appts from `users` left join `announcements` on `users`.`id` = `announcements`.`user_id` left join `offers` on `users`.`id` = `offers`.`user_id` left join `rating` on `users`.`id` = `rating`.`user_id` where `users`.`id` = 1

It works, by model is not:

return User::where("users.id", $id)
            ->selectRaw('rating.rate, COUNT(offers.id) AS ofrs, COUNT(announcements.id) as appts')
            ->leftJoin('announcements', 'users.id', '=', 'announcements.user_id')
            ->leftJoin('offers', 'users.id', '=', 'offers.user_id')
            ->leftJoin('rating', 'users.id', '=', 'rating.user_id')
            ->get();

This work:

return User::where("users.id", $id)
            ->selectRaw('rating.rate, COUNT(offers.id) AS ofrs, COUNT(announcements.id) AS appts')
            ->leftJoin('announcements', 'users.id', '=', 'announcements.user_id')
            ->leftJoin('offers', 'users.id', '=', 'offers.user_id')
            ->leftJoin('rating', 'users.id', '=', 'rating.user_id')
            ->groupBy('rating.rate')
            ->get();

Do you have the relationships set up in your User model for announcements, offers and rating? — Rwd
– Rwd, Commented Mar 12, 2017 at 10:02

Reisclef · Accepted Answer · 2017-03-12 09:54:24Z

2

The function name you're calling for group by is incorrect. Try:

->groupBy('ofrs')

See here for more.

answered Mar 12, 2017 at 9:54

Reisclef

2,1561 gold badge23 silver badges26 bronze badges

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

6 Comments

Reisclef Over a year ago

The edit to your question is a completely different problem. In that case, you appear to be trying to group on your counted aggregate. What exactly are you trying to achieve here? This is an entirely different kind of question to your original.

Darama Over a year ago

I need to join table User with others tables and to amount rows in related tabled for current user

Reisclef Over a year ago

So you're effectively trying to get the count of offers and announcements for each user?

Darama Over a year ago

I have written this Query directly in SQL: select rating.rate, COUNT(offers.id) AS ofrs, COUNT(announcements.id) as appts from users` left join announcements on users.id = announcements.user_id left join offers on users.id = offers.user_id left join rating on users.id = rating.user_id where users.id = 1` It works, but in Laravel does not

Darama Over a year ago

Look question please

|

Trouble · Accepted Answer · 2017-03-12 11:58:17Z

1

You need to read on how SQL works. You can't group by counted alias. What you are looking for is probably three queries or three relations.

You should create relation on User for announcements, second one for offers and third for ratings.

Than you would User::find($id)->announcements->count(), User::find($id)->offers->count() and User::find($id)->ratings->count()

Or you can use your Fluent way with something like:

return User::find($id) ->leftJoin('announcements', 'users.id', '=', 'announcements.user_id') ->count();

answered Mar 12, 2017 at 11:58

Trouble

1993 silver badges22 bronze badges

Collectives™ on Stack Overflow

Why does not work SQL query Laravel?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related