Here's a typical example of a problem. Have you seen something like this?
We will show how to fix this error later, but let's start with more simple examples and get more complex, step by step.
Setup: Our Database
For the examples in this section, we will use a database of user orders and products for all examples. The database structure and data look like this:
With the following data:
Typically, GROUP BY
is used with aggregating functions like COUNT()
OR SUM()
but not necessarily. Let's start with a more simple example.
What if we want to display all different users that had any orders purchased:
To do this we can use the groupBy()
method on the Order
model:
Controller
use App\Models\Order; $orders = Order::query() ->select('user_id') ->with('user:name,id') ->groupBy('user_id') ->get(); return view('examples.groupBy', [ 'orders' => $orders]);
This executes the following Queries:
select `user_id`from `orders`group by `user_id` select `name`, `id`from `users`where `users`.`id` in (1, 2, 3, 4, 5, 6, 7, 8, 9, ..., 100)
Then in our Blade view, we have a $order->user
relationship loaded with the user's name:
View
{{-- ... --}}<table class="table-auto w-full"> <thead> <tr> <th class="px-4 py-2">Name</th> </tr> </thead> <tbody> @foreach ($orders as $order) <tr> <td class="border px-4 py-2">{{ $order->user->name }}</td> </tr> @endforeach </tbody></table>{{-- ... --}}
This resulted in Unique rows for each of our users with any order.
In other words, the result would be identical in this example, using GROUP BY
instead of the DISTINCT
operator.
is there a repo associated with this course? would like to have seeders and factories so I can follow along.
This time there are no repositories as the dataset is quite simple if I'm being honest. But we can check if we can add one here :)
its simple but adding just a factory and seeder file to copy from should be sufficient to run those routines
https://github.com/LaravelDaily/group-by-demo/
Repo for reference
awesome thanks @Amit