Limits, offsets, order by and result sets

Paginating data with limits and offsets

When you perform a query with TDBM, you never pass the limit or offset to the DAO method you are querying.

$users = $userDao->findAll();

// Iterate all users
foreach ($users as $user) {
    // Do stuff...
}

If you want to limit the number of records returned, or start at a given offset, you can use the take method.

$users = $userDao->findAll();

$page = $users->take(0, 10);

// Iterate only the first 10 records
foreach ($page as $user) {
    // Do stuff...
}

What is going on behind the scene? When you query a TDBM DAO for a list, instead of returning an array containing the results, TDBM will return a ResultIterator object. This object behaves like an array, so you can call foreach on it. It is important to understand that the SQL query is not actually performed until foreach is called! This is actually a nice thing, since we can call additional methods afterwards to modify the query. Like the take method.

Sorting

We can also modify the sorting of your result set using the withOrder method:

$users = $userDao->findAll();

// Changes the order of the query.
// Warning, this returns a new ResultIterator object!
$users = $users->withOrder('name asc, firstname asc');

foreach ($users as $user) {
    // Do stuff...
}

Parameters

We can modify the named parameters of a query with the withParameters method:

$users = $this->find("login LIKE :login");

// Changes the parameters of the query.
// Warning, this returns a new ResultIterator object!
$users = $users->withParameters([ 'login' => 'david%' ]);

foreach ($users as $user) {
    // Do stuff...
}

ResultIterator utility methods

Here is a list of the methods you can call on a ResultIterator:

• take($offset, $limit): this will add an OFFSET and a LIMIT to the query performed. The take method returns a PageIterator instance that represents the "limited" results.
• count(): will return the total count of records.
• first(): will return the first element of the result set.
• toArray(): will cast the ResultIterator into a plain old PHP array.
• map(callable $callback): will call the $callback on every bean of the recordset and return the matching array.
• withOrder($orderBy) : ResultIterator: changes the ORDER BY clause of your query (returns a new ResultIterator)
• withParameters($parameters) : ResultIterator: changes the named parameters of your query (returns a new ResultIterator)

Pages (retrieved with the take method) also have additional methods you can call:

• count(): will return the count of records in the page
• totalCount(): will return the total count of records (bypassing the limit and offset)
• getCurrentOffset(): returns the offset
• getCurrentLimit(): returns the limit
• getCurrentPage(): returns the page number (starting at one and based on the limit and offset given)
• toArray(): will cast the PageIterator into a plain old PHP array.
• map(callable $callback): will call the $callback on every bean of the page and return the matching array.

Implements Porpiginas

If you are already familiar with beberlei/porpaginas, you probably noticed that TDBM result sets implement Porpaginas interfaces (the Result and Page concept comes from Porpaginas).

This means that you can easily migrate to and from any ORM that uses Porpaginas for its results. You can also use any pagination library compatible with Porpaginas, like Pagerfanta.

Next step

Let's now learn how to regenerate DAOs when your data model changes. Or if you want to have a look at advanced stuff, learn how to model inheritance in your database.

Found a typo? Something is wrong in this documentation? Just fork and edit it!