3,796 total views, 1 views today

A basic query in CakePHP 3.0 is different than the ones used in CakePHP 2.0. In 3.0, the ORM (Object-relational mapping) used for database management related operations has completely changed.  For example, if ‘Articles‘ is a model & we have to fetch some of the fields using query builder, the query would look as below:

$results=$articles->find()
->where(['id >'=>1])
->order(['title'=>'DESC'])
->toArray();

Here we get all the records from ‘Articles’ table where id is greater than 1 & the results are sorted in descending order by the field ‘title’.

Let’s take this example. There are 2 tables such as ‘Tag’ & ‘Article’ with many to one relationship. In case we need to fetch the tag information associated with articles, then we opt for the ‘contain’ method of CakePHP. In version 3.0 we have full control over the associated models. Here is the query:

$results=$articles->find()
->contain(['Authors', Tags])
->where(['id >'=>1])
->order(['title'=>'DESC'])
->toArray();

By default, the ‘Contain’ method selects all the fields from associated tables. If we wish to select only a few & leave the rest, we have to query like the following:

$results=$articles->find()
->contain(['Authors'=> function ($q) {									
return $q->select(['id', article_id, author_name])->autoFields(false);
	}, Tags])
		->where(['id >'=>1])
		->order(['title'=>'DESC'])
		->toArray();

In order to select an individual field, we have to set the autoFields to false. To filter associated records based on conditions, here’s how it is:

$results=$articles->find()
->contain(['Authors'=> function ($q) {								
return $q->select(['id', article_id, author_name])
->where(['Authors.article_id' =>1])
->autoFields(false);
	}, Tags])
		->where(['id >'=>1])
		->order(['title'=>'DESC'])
		->toArray();

We can also attach formatter functions to the associated table to get specific results. Suppose, we have a birth date column in the author table & we want to get the author’s current age; we can attach a formatter function to the query.

For example,

$query->contain(['Authors'=>function ($q) {
return$q->formatResults(function ($authors) {
return$authors->map(function ($author) {
$author['age'] =$author['birth_date']->diff(new\DateTime)->y;
return$author;
        });
    });
});
$results=$query->all();
echo$results->first()->author->age;  //Give the age of the author

Get all your queries answered from Andolasoft‘s experienced pool of PHP/CakePHP Developers. Planning anything on CakePHP? We would love to get in touch with you.

Check outour flagship product – Orangescrum built completely with CakePHP. Also, share your comments below.