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

laravel db::query
laravel raw query with parameters
simple select query in laravel
full join in laravel
print query in laravel
laravel eloquent sort
laravel collection
get single column value in laravel eloquent

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.

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

Database: Query Builder - Laravel, The table method returns a fluent query builder instance for the given table, These methods accept an optional array of bindings as their second The groupByRaw method may be used to set a raw string as the value of the group by clause: given number of results in the query, you may use the skip and take methods: Laravel query builder GROUP BY method alongside with SKIP and TAKE methods 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.

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.

Query Builder - Laravel, The database query builder provides a convenient, fluent interface to creating and running database queries. This method will return an array of role titles. Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks.

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()

Advanced Laravel Eloquent usage, Laravel makes building PHP applications a breeze. Eloquent provides accessor methods and properties corresponding to each table cell on the row. But that would make your database break the normalization code. Say you want to return both delivered and undelivered orders, and group them like  Query Builder Basic Usage. The database driver plugs right into the original query builder. When using MongoDB connections, you will be able to build fluent queries to perform database operations. For your convenience, there is a collection alias for table as well as some additional MongoDB specific operators/operations.

Laravel features you may not know about, Need to do a query builder 'where' query on a couple of columns? Try this clean, one-method way: Eloquent's find() can accept multiple rows; Did you know path to your route's group() to include that file; Eloquent: get a fresh version (fresh​()) of the Skip the controller, return the view from the routes file. The QueryBuilder used in this package extends Laravel's default Eloquent builder. This means all your favorite methods and macros are still available. Query parameter names follow the JSON API specification as closely as possible. Basic usage Filter a query based on a request: /users?filter[name]=John:

U.S. naval aviation at war, By the jeep-Ioad, VMF-222 pilots taxi to Bougainville strip tor take-oft on the next leg A. J. Isbell, was given a Presidential citation, along with its task group, for its their gasoline-filled belly tanks on a large building to soak it with high-test fuel, summer of 1944 is an eloquent testimonial to the Helld1'.ver's Sunday punch. in order for the relationship to work, make sure the select includes any required foreign keys i.e. if you'd leave out group_id, the value of the returned group attribute would be null; also, don't forget to load the relationship, using the query builder's with method

China after the cultural revolution, Most of them are incapable of altering overnight the ways and mores of ready to forego debating and submit to the leadership of a group of men who at Crudely speaking, the method applies a psychological solvent before the Military figures also took over many important posts such as the Ministry of Machine Building  However, the query builder's cursor method returns a LazyCollection instance. This allows you to still only run a single query against the database but also only keep one Eloquent model loaded in memory at a time.

Comments
  • Put your groupBy() before your ->get()
  • i have tried then groupBY() doesn't work.It shows 10 records without groups.
  • 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().
  • yes it shows 10 different actions with 10 different product_id's.
  • 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.
  • I tried its not returning 10 groups .it return 3 groups with 10 records
  • 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.
  • You can also use ->limit(10) over ->take(10). Take is an alias of limit. This is the correct answer. thumbs up
  • 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
  • @Mozammil which is the best approach splice() method or the take and skip methods?
  • 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 :)
  • Their is a splice method i have tried this after group by and it also worked like a charm 'splice($start,$rowperpage)'