Laravel query builder GROUP BY method alongside with SKIP and TAKE methods

Question

I have around 50000 records and I am showing them in Datatable with server side processing. In my query I am applying the groupBy() method with skip() and take() method.

I want to be able to apply the limit AFTER groupBy() e.g.

If limit is 10 it should return 10 groups not 10 records.

DB::table('actions')->whereBetween('timestamp', 
array($dates['startDate'], $dates['endDate']))
->where('shop_name', $shopName)
->skip($start)
->take(10)
->get()
->groupBy('product_id');

With this query i am getting 10 records not 10 groups.

i have tried then groupBY() doesn't work.It shows 10 records without groups. — Waleed Raza, Jan 25 '19 at 09:59
Does it show 10 different actions with 10 different product_id's? Since that's what you're grouping by. You **HAVE** to put your groupBy() before your get(). — , Jan 25 '19 at 10:01
yes it shows 10 different actions with 10 different product_id's. — Waleed Raza, Jan 25 '19 at 10:42
Then it worked, you have 10 different groups, because you have more than 10+ product_id's, you'll receive 10 records. It did exactly what you asked it. — , Jan 25 '19 at 10:43

score 0 · Answer 1 · answered Jan 25 '19 at 09:57

0

Try omitting take(10) and adding limit(10) at the very end of your query but before get().

answered Jan 25 '19 at 09:57

I tried its not returning 10 groups .it return 3 groups with 10 records – Waleed Raza Jan 25 '19 at 10:02

Mozammil · Answer 2 · 2019-01-25T11:22:20.443

0

The order of your query statements are wrong. The following should do the trick. Notice the groupBy() is before your take() and get().

In your case, you are grouping after the records have been fetched.

DB::table('actions')
    ->whereBetween('timestamp', array($dates['startDate'], $dates['endDate']))
    ->where('shop_name', $shopName)
    ->groupBy('product_id')
    ->skip($start)
    ->take(10)
    ->get()

edited Jan 25 '19 at 11:22

answered Jan 25 '19 at 10:00

Mozammil

8,520
15
29

1

You can also use `->limit(10)` over `->take(10)`. Take is an alias of limit. This is the correct answer. *thumbs up* – Jan 25 '19 at 10:03
You're welcome. Feel free to upvote/mark this as the answer if it worked for you. See [What should I do when someone answers](https://stackoverflow.com/help/someone-answers) – Mozammil Jan 25 '19 at 10:27
@Mozammil which is the best approach splice() method or the take and skip methods? – Waleed Raza Jan 25 '19 at 10:33
There's no splice method. The alias to `take()` is `limit()`. It's just a matter of personal choice. They both do the same thing :) – Mozammil Jan 25 '19 at 10:35
Their is a splice method i have tried this after group by and it also worked like a charm 'splice($start,$rowperpage)' – Waleed Raza Jan 25 '19 at 10:38
[Splice](https://laravel.com/api/5.7/Illuminate/Support/Collection.html#method_splice) is a Collection method. Not one available in the QueryBuilder. For this particular case, I'd rather use `take()` or `limit()` – Mozammil Jan 25 '19 at 10:43

score 0 · Accepted Answer · answered Jan 25 '19 at 10:30

0

DB::table('actions')
            ->whereBetween('timestamp', array($dates['startDate'], $dates['endDate']))
            ->where('shop_name', $shopName)
            ->get()
            ->groupBy('product_id')
            ->splice($start,$rowperpage);

and this order worked.

answered Jan 25 '19 at 10:30

Waleed Raza

73
11

While this will *work*, you are performing the splicing operations after you fetched `50,000` records. It's no an ideal case from a memory perspective. I edited my answer to include an `offset()` statement. You could use that to offset your query if you want. – Mozammil Jan 25 '19 at 11:16

Laravel query builder GROUP BY method alongside with SKIP and TAKE methods

3 Answers3