Finding data

Reading data starts with the find() method of the table class. It creates a query to which conditions, sorting and a limit are added in a chain. There is no SQL to write; the ORM builds it.

All records

// src/Controller/ArticlesController.php
public function index(): void
{
    $articles = $this->Articles->find()->all();
 
    $this->set(compact('articles'));
}

find() creates a query, all() runs it and returns a set of records. The template walks through it with a foreach loop.

One record by number

public function view(int $id): void
{
    $article = $this->Articles->get($id);
 
    $this->set(compact('article'));
}

get() looks up a record by its primary key. The address /articles/view/1 shows the first article, and for a number that does not exist the visitor gets a 404 page; there is no need to check this yourself.

Conditions

$articles = $this->Articles->find()
    ->where(['published' => true])
    ->all();

The array key is a column name and the value is what it must equal. Only published articles remain.

Other comparisons are written with an operator after the column name:

Several conditions in one array are joined with AND.

Sorting and limit

$articles = $this->Articles->find()
    ->where(['published' => true])
    ->orderByDesc('created')
    ->limit(5)
    ->all();

The result is the five newest published articles. For ascending order there is orderByAsc().

The first record

$article = $this->Articles->find()
    ->where(['title' => 'First article'])
    ->first();

first() returns one record, or null when nothing is found. The firstOrFail() method gives a 404 page instead of null.

Counting

$total = $this->Articles->find()->where(['published' => true])->count();

count() returns a number; the records themselves are not loaded.

Only the columns you need

$titles = $this->Articles->find()->select(['id', 'title'])->all();
 
$list = $this->Articles->find('list')->toArray();

select() limits the set of columns. find('list') returns an array of the "number to title" form, handy for drop-down lists.

A custom finder

A condition needed in several places is moved to the table class:

// src/Model/Table/ArticlesTable.php
use Cake\ORM\Query\SelectQuery;
 
public function findPublished(SelectQuery $query): SelectQuery
{
    return $query->where(['published' => true]);
}

In a controller it is called by its name without the find prefix: $this->Articles->find('published')->all().

Common mistake

first() may return null, and then $article->title ends with an error. Either check the result with if ($article === null), or use firstOrFail() and get(), which show a 404 page themselves.