CakePHP v3.4 Database related operations

CakePHP v3.4 Database related operations

Querying objects Query Object
Class Cake\orm\query

Get Query Object
The Table object is returned by using Find ()

1. Can be obtained from tableregistry in any case
Use Cake\orm\tableregistry;
$articles = Tableregistry::get (' articles ');
$query = $articles->find ();

2. In the corresponding controller (the framework is created automatically)
$query = $this->articles->find ();

Return rows in a table
Query object is an object that can be iteratively manipulated
The iterative process will actually query the database, incessantly generation
is not performing the query operation is lazy execution
foreach ($query as $article) {
Debug ($article->title);
}

Here are some common operations

$query = $articles
->find ()
->select ([' id ', ' name '])
->where ([' id! = ' = 1])
->order ([' Created ' = ' DESC ']);

This is what is called a chained call that is still returned at the end of the
Query Object object does not actually query the database

Debug ($query) to display the internal state of the object

$article = $articles
->find ()
->where ([' id ' = 1])
->first ();//Query and return the first row (database query actually executed)

$article = $articles
->find ()
->extract (' title '); Query and return the title column

Querying key-value pairs
$list = $articles->find (' list ')//query key-value pairs
->toarray ();//actual execution of data query operations
Returns two field IDs another is the default set in the model

$list = $articles->find (' list ', [' valuekey ' = ' id ', ' valuefield ' + ' other ');

Specifies the returned key-value pair field

Queries is Collection (collection set) objects
All of the query objects are collection objects can use all collection method

$keyValueList = $articles->find ()
->combine (' id ', ' title ');
The Combine method returns the ID of the title field
Equivalent to find (' list ')

Higher-level queries
Application of the map () method
$result = $articles->find ()
->where ([' id ' = 1])
->order ([' title ' = ' DESC '])
->map (function ($row) {
$row->trimmedtitle = Trim ($row->title);
return $row;
})
->combine (' id ', ' trimmedtitle ')
->toarray ();

Map (function ($row) {}) Collection method
Provides the ability to process data, such as some formatting operations
parameter is an anonymous function the anonymous function is passed in by default to each of the $row objects
That is, each row in the table is an object
In the anonymous function we can process the field data of the row
You can override the Increment field to return the result
You can choose to return an $row object or an array, a string

Example:
Map (function ($row) {
return $row->id; Returns only the ID at which time only the string is returned

return [$row->id, $row->title];//returns the array

return $row; Returns directly after processing the $row object
});

ToArray ()
Returns an array instead of an object that is only the outermost array and the rows are likely or

An object

How the database query will actually be performed
The foreach iteration Query object actually executes the database query

Execute () performs a query operation

All ()

ToArray ()

Field aliases
->select ([' pk ' = ' + ' id ', ' as_title ' = ' title '])

$query
->select ([' slug ' = + $query->func ()->concat ([' title ' = ' identifier ', '-', ' id ' = ' identifier '])])

Equivalent
Concat (title, '-', id)

->select ($this->articles)//Query all fields

Using functions
$query = $articles->find ();

$year = $query->func ()->year ([
' Created ' = ' identifier '
]);

$time = $query->func ()->date_format ([
' Created ' = ' identifier ',//Recognized as field identifier function
"'%h:%i '" = ' literal '//raw string literal
]);

$query->select ([
' Yearcreated ' = $year
' Timecreated ' = $time
]);

Equivalent
SELECT year (created) as yearcreated, Date_format (created, '%h:%i ') as timecreated
from articles;

Returns an array instead of an instance object
$query = $articles->find ();
$query->hydrate (FALSE); Returns an array instead of an object
$result = $query->tolist ();

[
[' id ' = 1, ' title ' = ' abc ']
[' id ' = 2, ' title ' = ' abc ']
]

Use of the FormatResult () method
Formatting results
$query->formatresult (function (\cake\collection\collectioninterface $result) {
return $results->map (function ($row)) {
$row [' age '] = $row [' Birth_data ']->diff (new \datetime)->y;
return $row;
}
});

$result is a Query object

The above method can also be used in contain correlation query
$query->contain ([
' Authors ' = function ($q) {//association table Authors
return $q->formatresult (function \cake\collection\collectioninterface $authors) {//default incoming Authors query object
return $authors->map (function ($author) {
$author [' age '] = $author [' Birth_date ']->diff (new \datetime)->y;
return $author;
})
}
}
]);

$result = $query->all ();
The above method realizes the more control of the associated table during the correlation check, and handles
It should be noted that only the associated authors table is manipulated

Other conditional queries can be viewed in the manual

CakePHP v3.4 Database related operations