I want to return data from a table, grouping the rows either by company_id
or worker_id
if company_id
is null
filtering by member_id
My table looks like this:
id | library_id | member_id | created_at | updated_at | deleted_at | worker_id | company_id |
---|---|---|---|---|---|---|---|
1 | 1 | 2 | 2022-01-31 15:02:32 | 2022-01-31 15:02:32 | 1 | ||
3 | 3 | 2 | 2022-01-31 15:02:32 | 2022-01-31 15:02:32 | 1 | 1 | |
4 | 4 | 2 | 2022-01-31 15:02:32 | 2022-01-31 15:02:32 | 1 | 2 | |
5 | 5 | 2 | 2022-01-31 15:02:32 | 2022-01-31 15:02:32 | 2 | 1 | |
6 | 6 | 2 | 2022-01-31 15:02:32 | 2022-01-31 15:02:32 | 2 | 2 | |
7 | 7 | 2 | 2022-01-31 15:02:32 | 2022-01-31 15:02:32 | 1 | ||
8 | 8 | 2 | 2022-01-31 15:02:32 | 2022-01-31 15:02:32 | 2 |
My Answer:
To achieve this in Laravel using Eloquent, you can use the `groupBy()` method along with `select()` to specify the columns you want to retrieve. Here's an example query that groups the rows by `company_id` or `worker_id` if `company_id` is null and filters by `member_id`:
php
$data = YourModel::select('id', 'library_id', 'member_id', 'created_at', 'updated_at', 'deleted_at', 'worker_id', 'company_id')
->where('member_id', $member_id)
->groupBy('company_id', 'worker_id')
->get();
This query will return all rows grouped by `company_id` or `worker_id` if `company_id` is null, and filtered by `member_id`. Each row will be returned as it is in the database without any aggregation.
Rate this post
4 of 5 based on 8062 votesComments