CODEWITHSUNDEEP
phplaravellaravel-4
JustinHo
JustinHo

Reputation: 4745

How to properly take and skip in Laravel 4

In Laravel 4, I want to return objects in two parts: first 5 and rest of them. I've tried the following:

$firstFive = Category::take(5)->get(); // this works.

$theRest = Category::skip(5)->take(5)->get(); // this works

$theRest = Category::take('THE REST')->skip(5)->get(); // of course not working

I know I can just enter a very large number in the take() to get the rest of them, but is there a way that takes exactly the rest of the objects ?

Upvotes: 1

Views: 414

Answers (2)

The Alpha
The Alpha

Reputation: 146201

I assumed that the id field is your primary key and has the type of INT/BIGINT and here you can use the maximum number for an INT type 4294967295 and BIGINT type 18446744073709551615 so there is no chance to exceed the number of rows in a table that a particular type (INT/BIGINT) can have their maximum value. MySQL Integer Types (Exact Value).

Also, the MyISAM engine (default) can store by default 2^32 rows. That's 4,294,967,296 rows. That could be extended to 2^64 using --with-big-tables option, so it'll support up to 2^64 rows per table.

The InnoDB storage engine doesn't have a limit on the number of rows, but it has a limit on table size of 64TB. How many rows fits into this depends on the size of each row.

From MySql Documentation:

To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:

SELECT * FROM tbl LIMIT 95,18446744073709551615;

With one argument, the value specifies the number of rows to return from the beginning of the result set. SO, you may use following to retrieve all rows from the 6th row to the last:

SELECT * FROM tbl LIMIT 5,18446744073709551615;

Since you asked for exact numbers of rows then you may always use the count(*) - offset but IMO you don't need this and can avoid the count query.

Upvotes: 2

alexrussell
alexrussell

Reputation: 14212

You can pass a stupidly high value into take and it should work. I don't know of any other way. Leaving the take call out does not work, as it generates SQL like this:

/* MyModel::skip(2)->lists('id'); */
select `id` from `table` offset 2

And MySQL (at least) needs a LIMIT with an OFFSET.

/* MyModel::take(9999999)->skip(2)->lists('id'); */
select `id` from `table` limit 9999999 offset 2

It's hardly elegant though. Also it's probably worth investigating the maximum number that can be passed to LIMIT if you were to use this method to avoid edge cases when your DB is huge!

Upvotes: 3

Related Questions