3.4. Fetching Data
Prev  Chapter 3. Working With Models  Next

3.4. Fetching Data

Once you have a model object, you can use it to fetch data from the table it represents.

3.4.1. Fetch Methods

fetch()

Fetches a record or collection object by primary key value(s).

fetchAll()

Fetches a collection object (keyed sequentially) populated with record objects.

fetchAllAsArray()

The same as fetchAll(), except the result is a sequential array of row arrays, instead of a collection object.

fetchAssoc()

Similar to fetchAll(), this fetches a collection object using associative keys, populated with record objects. The associative keys are taken from the first column of the results.

fetchAssocAsArray()

The same as fetchAssoc(), except the result is an associative array of row arrays, instead of a collection object.

fetchCol()

Fetches a sequential array populated with only the first column of data.

fetchOne()

Fetches a single record object.

fetchOneAsArray()

Similar to fetchOne(), except the result is an associative array where the key is the column name and the value is the column value.

fetchPairs()

Fetches an associative array of key-value pairs where the first column is the key and the second column is the value.

fetchValue()

Returns a single value; i.e., the first column of the first row.

3.4.2. Fetch Parameters

For the fetch() method, the only parameter is a primary key value (or an array of such values). 

<?php
// a single blog record, blog.id = 1
$item = $model->blogs->fetch(1);

// a collection of blog records
$list = $model->blogs->fetch(array(1, 2, 5));

All the other fetch methods take a series of parameters that get translated to SELECT clauses. The parameters are expressed as an associative array. 

<?php
$fetch = array(
    // SELECT ... FROM blogs AS zim
    'alias' => 'zim',
    
    // select these columns
    'cols' => array('id', 'foo', 'bar', 'SUM(baz) AS total'),
    
    // WHERE conditions
    'where' => array(
        'foo = :foo',
        'dib IN (?)' => array(1, 2, 5),
        'OR bar > ?' => $bar,
    ),
    
    // GROUP BY columns
    'group' => array('zim.id'),
    
    // HAVING conditions
    'having' => array(
        'total > ?' => $total,
    ),
    
    // ORDER BY columns
    'order' => array('total'),
    
    // you can use regular LIMITs ...
    'limit' => array($count, $offset),
    
    // ... or let the model calculate a page-based LIMIT
    'page'   => $page_number,
    'paging' => $rows_per_page,
    
    // do a followup query without limits to get a page count
    'count_pages' => true,
    
    // bind these placeholder values to the query
    'bind' => array('bar' => $bar),
);

// fetch a collection using the fetch params
$list = $model->blogs->fetchAll($fetch);

alias

Normally, the model will use the table name without an alias in the SELECT query it generates. However, you can use this to specify an alias. In the above example, this would generate FROM blogs AS zim.

cols

Normally, the model will fetch all table columns. You can use this to specify which columns to select, including computed columns.

where

This is an array of conditions for the WHERE clause. The elements each take one of two forms. The first is a single value passed literally as the condition. The second is a key-value pair where the key has one or more ? placeholders in it; the value is then quoted against SQL injection and placed into the condition immediately.

group

Indicates a GROUP BY clause using these columns.

having

As with where, this is an array of conditions for the HAVING clause.

order

Indicates an ORDER BY clause using these columns.

limit

This can be an integer indicating a LIMIT count, or it can be an array of two elements, indicating a LIMIT count and offset. This parameter overrides the page and paging values.

page

When combined with paging, this will fetch the indicated page-number of the result set.

paging

Indicates to page how many result rows per page it should use when calculating the page number.

count_pages

When set to true, this will cause a second query to occur after the fetch. The second query will perform a COUNT() using the same parameters, but without fetching any data and without a limit. It will retain the count of rows, count of pages, first and last page numbers, and other pagination data in the collection object. Unless you are using pagination, you generally should not ask for count_pages.

bind

This is an array of key-value pairs to bind to named placeholders in the resulting SELECT statement.

3.4.3. Magic Fetch Methods

The model objects allow a shorthand form of fetching records and collections by using the magic __call() method to dynamically interpret the incoming method name and convert it to a series of WHERE clauses for you.

There are two recgonized magic methods: fetchAllBy...() and fetchOneBy...(). The remaining words in the method name are treated as column names (where column_name is recognzed as ColumnName) separated by And. The method params are equality values for the columns in the method name; a final trailing argument of additional fetch parameters is also recognized. 

<?php
// fetch a single blog record WHERE foo = 'bar'
$item = $model->blogs->fetchOneByFoo('bar');

// fetch a collection of blogs WHERE foo = 'bar' AND baz = 'dib'
$list = $model->blogs->fetchAllByFooAndBaz('bar', 'dib');

// fetch a collection of blogs WHERE zim = 'gir',
// with additional parameters
$params = array('order' => 'id DESC');
$list = $model->blogs->fetchAllByZim('gir', $params);
[Note] Note

The magic fetch methods are comparatively slow. If you find yourself using a particular fetch over and over, consider writing a method on your model object for that fetch.

Prev  Up  Next
3.3. The Model Catalog  Home  3.5. Records and Collections

Local