Querying

Reading data from your database (or any datasource available) is a common
tasks that you will perform. All query-related operations may be done through
the static find() method, along with some additional utility methods provided
for convenience.

// Read all posts.
$posts = Posts::find('all');
// Read the first post.
$post = Posts::find('first');
// Read all posts with the newest ones first.
$posts = Posts::find('all', [
    'order' => ['created' => 'DESC']
]);
// Read only the title of the newest post.
$post = Posts::find('first', [
    'fields' => ['title'],
    'order' => ['created' => 'DESC']
]);
// Read only posts where the author name is "michael".
$posts = Posts::find('all', [
    'conditions' => ['author' => 'michael']
]);

Built-in Finders

find() utilizes finders to do most of its work. The first parameter to find()
selects the type of finder you want to use. The second parameter provides options to the
finder.

There are four built-in finders. The most commonly used ones are 'all' and 'first'.

• 'all': Returns a collection of records matching the conditions.
• 'first': Returns the first record matching the conditions.
• 'count': Returns an integer count of all records matching the conditions.
• 'list': Returns a one dimensional array, where the key is the primary
  key and the value the title of the record (a 'title' field is required
  for this). A result may look like: array(1 => 'Foo', 2 => 'Bar').

All bultin-in finders take the same set of options to control which and
how many records they return.

Using Conditions

The 'conditions' option allows you to provide conditions for the query
i.e. 'array('is_published' => true). The following example finds all
posts author name is michael.

$posts = Posts::find('all', [
    'conditions' => ['author' => 'michael']
]);

By default the conditions are AND’ed together so the following
example would require each post to be published and authored
by michael.

$posts = Posts::find('all', [
    'conditions' => [
        'author' => 'michael',
        'is_published' => true
    ]
]);

To OR conditions together use the following syntax.

$posts = Posts::find('all', [
    'conditions' => [
        'or' => [
            'author' => 'michael',
            'is_published' => true
        ]
    ]
]);

To find all records from a set of authors use the array
syntax.

$posts = Posts::find('all', [
    'conditions' => [
        'author' => ['michael', 'nate']
    ]
]);

Ordering

The 'order' option allows to control the order in which the data will be returned, i.e.
'created ASC' sorts by created date in ascending order, 'created DESC' sorts
the same field in descending order. To sort by multiple fields use the array
syntax array('title' => 'ASC', 'id' => 'ASC).

The following are equal groups:

Posts::find('all', ['order' => 'title']);
Posts::find('all', ['order' => 'title ASC']);
Posts::find('all', ['order' => ['title']]);
Posts::find('all', ['order' => ['title' => 'ASC']]);
Posts::find('all', ['order' => ['title', 'id']]);
Posts::find('all', ['order' => ['title' => 'ASC', 'id' => 'ASC']]);

Restricting Returned Fields

The 'fields' option allows to specify the fields that should be retrieved. By default
all fields are retrieved. To optimize query performance, limit to just the ones actually
needed.

Posts::find('all', [
    'fields' => ['title', 'author']            
]);

Limiting Number of Records

The 'limit' option allows to limit the set of returned records to a maximum number.

Posts::find('all', [
    'limit' => 10            
]);

Pagination

The 'page' option allows to paginate data sets. It specifies the page of the set
together with the limit option specifying the number of records per page. The first
page starts at 1.

$page1 = Posts::find('all', [
    'page' => 1,
    'limit' => 10    
]);
$page2 = Posts::find('all', [
    'page' => 2,
    'limit' => 10    
]);

Shorthands

li3 also provides some additional basic methods around the find() method which
make your code less verbose and easier to read:

// Shorthand for find first by primary key.
Posts::find(23);
Posts::all();

The example below shows two different approaches to finding all the posts related to the
username “Michael”. The first bare approach shows how to use find() directly. The second
example uses camelCase convention to tell li3 to filter by a specific field name and
value.

$posts = Posts::findAllByUsername('michael');
// ... is functionally equal to ...
$posts = Posts::find('all', [
    'conditions' => ['username' => 'michael']
]);

Custom Finders

The basic finders are nice, but the framework also provides you with a set of highly
dynamic methods that match against your dataset. It allows you to build
custom finders to extend functionality.

As you use your models, you might start to wish for a shortcut. For example,
instead of having to do this repeatedly:

$recentComments = Comments::find('all', [
    'conditions' => [
        'created' => [
            '>=' => date('Y-m-d H:i:s', time() - (86400 * 3))
        ]
    ]
]);

You could create a custom finder method that packages the specified conditions into a one-liner:

$recentComments = Comments::find('recent');
// or, as a "magic" method:
$recentComments = Comments::recent();

At a basic level, this is done by utilizing the finder() method of the model.
You call finder() and supply the name of the finder, along with a definition
so li3 knows how to form the query. The definition in this simple case looks
just like the query array we supplied to find() earlier:

Comments::finder('recent', [
    'conditions' => [
        'created' => [
            '>=' => date('Y-m-d H:i:s', time() - (86400 * 3))
        ]
    ]
]);

Some finder implementations might require a little processing in addition to a default set
of conditions. In that case, you can define a finder using a closure that will be called
as part of find chaining. In this use case, you supply the name of the finder along with a
closure that looks much like a filter definition:

Comments::finder('recentCategories', function($params, $next){
    // Set up default conditions
    $defaults = [
        'created' => [
            '>=' => date('Y-m-d H:i:s', time() - (86400 * 3))
        ]
    ];
    // Merge with supplied params
    $params['options']['conditions'] = $defaults + (array) $params['options']['conditions'];
    // Do a bit of reformatting
    $results = [];
    foreach ($next($params) as $entity) {
        $results[] = $entity->categoryName;
    }
    // Returns an array of recent categories given the supplied query params.
    return $results;
});

Rich Returned Values

The response from a model method is not just a plain array but actually an
entity object (or a collection of them). This means that you can perform a
variety of actions on them if you need to. Here are a few examples:

// Find all Posts
$posts = Posts::all();
// Get the first and last post of the collection
$first = $posts->first();
$last  = $posts->last();
// Iterate over all posts and print out the title
foreach ($posts as $post) {
    echo $post->title;
}

Entities can be easily converted into other formats.

Posts::find('all')->data();
Posts::find('all')->to('array');

Default Query Options

In cases where you always want finders results constrained to i.e. certain conditions,
default query options can be used. Default options may be defined by using the query()
method or alternatively by defining the $_query property on the model class.

Specific query options overwrite default ones. As both are merged by simply using the +
operator for arrays. Note that this can also be a common pitfall.

Posts::query([
    'conditions' => ['is_published' => true],
    'limit' => 4
]);
// Will retrieve maximum of 4 results which are published.
Posts::find('all');
// Potential pitfall: will retrieve results published or not 
// for author michael. Limited to 4 results maximum.
Posts::find('all', [
    'conditions' => ['author' => 'michael']            
]);
// Will retrieve only published results for author michael.
// Limited to 4 results.
Posts::find('all', [
    'conditions' => ['author' => 'michael', 'is_published' => true]
]);