Le constructeur de requête de l’ORM fournit une interface facile à utiliser pour créer et lancer les requêtes. En arrangeant les requêtes ensemble, vous pouvez créer des requêtes avancées en utilisant les unions et les sous-requêtes avec facilité.
Sous le capot, le constructeur de requête utilise les requêtes préparées de PDO qui protègent contre les attaques d’injection SQL.
La façon la plus simple de créer un objet Query
est d’utiliser find()
à
partir d’un objet Table
. Cette méthode va retourner une requête incomplète
prête à être modifiée. Vous pouvez aussi utiliser un objet de connexion à une
table pour accéder à un constructeur de requête de plus bas niveau qui n’inclut
pas les fonctionnalités de l’ORM, si nécessaire. Consultez la section
Exécuter des Requêtes pour plus d’informations:
use Cake\ORM\Locator\LocatorAwareTrait;
$articles = $this->getTableLocator()->get('Articles');
// Commence une nouvelle requête.
$query = $articles->find();
Depuis un controller, vous pouvez utiliser la variable de table créée automatiquement par le système de conventions:
// À l'intérieur de ArticlesController.php
$query = $this->Articles->find();
use Cake\ORM\Locator\LocatorAwareTrait;
$query = $this->getTableLocator()->get('Articles')->find();
foreach ($query->all() as $article) {
debug($article->title);
}
Pour les exemples restants, imaginez que $articles
est une
Table
. Quand vous êtes dans des controllers, vous
pouvez utiliser $this->Articles
plutôt que $articles
.
Presque toutes les méthodes d’un objet Query
retournent la requête
elle-même.
cela signifie que les objets Query
sont lazy, et ne seront pas exécutés à
moins que vous ne lui disiez de le faire:
$query->where(['id' => 1]); // Retourne le même objet query
$query->order(['title' => 'DESC']); // Toujours le même objet, aucun SQL exécuté
Vous pouvez bien sûr chainer les méthodes que vous appelez sur les objets Query:
$query = $articles
->find()
->select(['id', 'name'])
->where(['id !=' => 1])
->order(['created' => 'DESC']);
foreach ($query->all() as $article) {
debug($article->created);
}
Si vous essayez d’appeler debug()
sur un objet Query, vous verrez son état
interne et le SQL qui sera exécuté dans la base de données:
debug($articles->find()->where(['id' => 1]));
// Affiche
// ...
// 'sql' => 'SELECT * FROM articles where id = ?'
// ...
Vous pouvez exécuter une requête directement sans avoir à utiliser foreach
.
La façon la plus simple est d’appeler les méthodes all()
ou toArray()
:
$resultsIteratorObject = $articles
->find()
->where(['id >' => 1])
->all();
foreach ($resultsIteratorObject as $article) {
debug($article->id);
}
$resultsArray = $articles
->find()
->where(['id >' => 1])
->toList();
foreach ($resultsArray as $article) {
debug($article->id);
}
debug($resultsArray[0]->title);
Dans l’exemple ci-dessus, $resultsIteratorObject
sera une instance de
Cake\ORM\ResultSet
, un objet que vous pouvez itérer et sur lequel vous
pouvez appliquer plusieurs méthodes d’extraction ou de traversée.
Souvent, il n’y a pas besoin d’appeler all()
, vous pouvez juste itérer
l’objet Query pour récupérer ses résultats. les objets Query peuvent également
être utilisés directement en tant qu’objet résultat. Essayer d’itérer la requête
en utilisant toList()
ou n’importe qu’elle méthode héritée de
Collection, provoquera l’exécution
de la requête et la récupération des résultats.
Vous pouvez utilisez la méthode first()
pour récupérer le premier résultat
dans la requête:
$article = $articles
->find()
->where(['id' => 1])
->first();
debug($article->title);
// Utilise la méthode extract() à partir de la libraire collections
// Ceci exécute aussi la requête
$allTitles = $articles->find()->all()->extract('title');
foreach ($allTitles as $title) {
echo $title;
}
Vous pouvez aussi récupérer une liste de clés-valeurs à partir du résultat d’une requête:
$list = $articles->find('list')->all();
foreach ($list as $id => $title) {
echo "$id : $title"
}
Pour plus d’informations sur la façon de personnaliser les champs utilisés pour remplir la liste, consultez la section Trouver les Paires de Clé/Valeur.
Une fois que vous êtes familier avec les méthodes de l’objet Query, il est fortement recommandé de consulter la section Collection pour améliorer vos compétences sur une traversée efficace des données. En résumé, il est important de se rappeler que tout ce que vous pouvez appeler sur un objet Collection, vous pouvez aussi le faire avec un objet Query:
// Utilise la méthode combine() à partir de la libraire collection
// Ceci est équivalent au find('list')
$keyValueList = $articles->find()->combine('id', 'title');
// Un exemple avancé
$results = $articles->find()
->where(['id >' => 1])
->order(['title' => 'DESC'])
->map(function ($row) { // map() est une méthode de collection, elle exécute la requête
$row->trimmedTitle = trim($row->title);
return $row;
})
->combine('id', 'trimmedTitle') // combine() est une autre méthode de collection
->toArray(); // Aussi une méthode de la librairie collection
foreach ($results as $id => $trimmedTitle) {
echo "$id : $trimmedTitle";
}
CakePHP permet de construire simplement des requêtes SELECT
. La
méthode select()
vous permet de ne récupérer que les champs qui vous sont
nécessaires:
$query = $articles->find();
$query->select(['id', 'title', 'body']);
foreach ($query->all() as $row) {
debug($row->title);
}
Vous pouvez également définir des alias pour vos champs en les définissant dans un tableau associatif:
// Génère SELECT id AS pk, titre AS alias_de_titre, contenu ...
$query = $articles->find();
$query->select(['pk' => 'id', 'alias_de_titre' => 'titre', 'contenu']);
Pour sélectionner les valeurs distinctes dans des champs, vous pouvez utiliser
la méthode distinct()
:
// Génère SELECT DISTINCT pays FROM ...
$query = $articles->find();
$query->select(['pays'])
->distinct(['pays']);
Pour définir certaines conditions basiques, vous pouvez utiliser la
méthode where()
:
// Les conditions sont combinées par défaut avec AND
$query = $articles->find();
$query->where(['titre' => 'Premier Post', 'published' => true]);
// Vous pouvez aussi appeler where() plusieurs fois
$query = $articles->find();
$query->where(['titre' => 'Premier Post'])
->where(['published' => true]);
Vous pouvez aussi passer une fonction anonyme à la méthode where()
. La
fonction anonyme recevra une instance de
\Cake\Database\Expression\QueryExpression
en premier argument, et
\Cake\ORM\Query
pour le second:
$query = $articles->find();
$query->where(function (QueryExpression $exp, Query $q) {
return $exp->eq('published', true);
});
Consultez la section Conditions Avancées pour voir comment
construire des conditions WHERE
plus complexes.
// Sélectionne seulement id et titre dans la table articles $articles->find()->select([“id”, “titre”]);
Si vous souhaitez sélectionner à nouveau tous les champs d’une table après avoir
appelé select($fields)
, vous pouvez passer l’instance de table à
select()
:
// Sélectionne tous les champs de la table articles
// ainsi qu'un champ calculé slug
$query = $articlesTable->find();
$query
->select(['slug' => $query->func()->concat(['titre' => 'identifier', '-', 'id' => 'identifier'])])
->select($articlesTable); // Sélectionne tous les champs de articles
Si vous souhaitez sélectionner tous les champs d’une table sauf quelques-uns,
vous pouvez utiliser selectAllExcept()
:
$query = $articlesTable->find();
// Obtenir tous les champs sauf le champ published
$query->selectAllExcept($articlesTable, ['published']);
Vous pouvez aussi passer un objet Association
quand vous travaillez avec des
associations.
L’ORM de CakePHP offre une abstraction pour les fonctions SQL les plus
communément utilisées. Cette abstraction permet à l’ORM de sélectionner
l’implémentation de la fonction voulue spécifique à la plateforme.
Par exemple, concat
est implémentée différemment dans MySQL, PostgreSQL et
SQL Server. L’abstraction permet à votre code d’être portable:
// Génère SELECT COUNT(*) count FROM ...
$query = $articles->find();
$query->select(['count' => $query->func()->count('*')]);
Notez que la plupart des fonctions acceptent un argument supplémentaire pour spécifier le type de données à lier aux arguments et/ou le type de la valeur à retourner, par exemple:
$query->select(['minDate' => $query->func()->min('date', ['date']);
Pour plus de détails, lisez la documentation de
Cake\Database\FunctionsBuilder
.
Vous pouvez accéder aux wrappers existants de plusieurs fonctions SQL grâce à
Query::func()
:
rand()
Génère un nombre aléatoire entre 0 et 1 via SQL.
sum()
Calcule une somme. Les arguments sont considérés comme des valeurs littérales.
avg()
Calcule une moyenne. Les arguments sont considérés comme des valeurs littérales.
min()
Calcule le minimum d’une colonne. Les arguments sont considérés comme des valeurs littérales.
max()
Calcule le maximum d’une colonne. Les arguments sont considérés comme des valeurs littérales.
count()
Calcule le nombre de valeurs. Les arguments sont considérés comme des valeurs littérales.
concat()
Concatène deux valeurs. Les arguments sont considérés comme des paramètres liés.
coalesce()
Renvoie la première expression dont la valeur n’est pas NULL. Les arguments sont considérés comme des paramètres liés.
dateDiff()
Récupère la différence entre deux dates/heures. Les arguments sont considérés comme des paramètres liés.
now()
Renvoie la date et l’heure courantes par défaut, mais accepte “time” ou “date” comme argument pour récupérer seulement l’heure ou la date courante.
extract()
Renvoie une partie de la date spécifiée dans une expression SQL.
dateAdd()
Ajoute une unité de temps à l’expression de date.
dayOfWeek()
Renvoie une FunctionExpression représentant un appel à la fonction SQL WEEKDAY.
Ces fonctions de fenêtrage (window-only) contiennent une expression de fenêtrage par défaut:
rowNumber()
Renvoie une expression Aggregate pour la fonction SQL ROW_NUMBER()
.
lag()
Renvoie une expression Aggregate pour la fonction SQL LAG()
.
lead()
Renvoie une expression Aggregate pour la fonction SQL LEAD()
.
Nouveau dans la version 4.1.0: Les fonctions de fenêtrage ont été ajoutées dans 4.1.0
Quand vous fournissez des arguments pour les fonctions SQL, il y a deux types de paramètres que vous pouvez utiliser: les arguments littéraux et les paramètres liés. Les paramètres d’identification/littéraux vous permettent de référencer les colonnes ou d’autres valeurs littérales en SQL. Les paramètres liés peuvent être utilisés pour ajouter en toute sécurité les données utilisateur aux fonctions SQL. Par exemple:
$query = $articles->find()->innerJoinWith('Categories');
$concat = $query->func()->concat([
'Articles.title' => 'identifier',
' - CAT: ',
'Categories.name' => 'identifier',
' - Age: ',
$query->func()->dateDiff([
'NOW()' => 'literal',
'Articles.created' => 'identifier'
])
]);
$query->select(['link_title' => $concat]);
Vous pouvez faire référence à d’autres colonnes ou expressions SQL littérales
aussi bien avec literal
qu’avec identifier
, cependant identifier
ajoutera des quotes dans les cas appropriés si l’auto-quoting a été activé. Les
arguments qui ne sont pas marqués comme literal ni identifier seront
utilisés comme des paramètres liés, qui serviront à passer des données
utilisateur à la fonction. Le code ci-dessus va générer le SQL suivant sur
MySQL:
SELECT CONCAT(
Articles.title,
:c0,
Categories.name,
:c1,
(DATEDIFF(NOW(), Articles.created))
) FROM articles;
La valeur :c0
aura le texte ' - CAT:'
lié quand la requête est exécutée.
L’expression datediff
a été transcrite en SQL de façon appropriée.
Si func()
ne propose pas déjà un wrap de la fonction SQL dont vous avez
besoin, vous pouvez l’appeler directement à travers func()
et passer des
arguments et des données utilisateur en toute sécurité comme décrit ci-dessus.
Assurez-vous de passer les bons types d’arguments aux fonctions personnalisées,
sans quoi ils seront traités comme des paramètres liés:
$query = $articles->find();
$year = $query->func()->year([
'created' => 'identifier'
]);
$time = $query->func()->date_format([
'created' => 'identifier',
"'%H:%i'" => 'literal'
]);
$query->select([
'yearCreated' => $year,
'timeCreated' => $time
]);
Entraînera:
SELECT YEAR(created) as yearCreated,
DATE_FORMAT(created, '%H:%i') as timeCreated
FROM articles;
Note
Utilisez func()
pour passer des données externes (non fiables) à
n’importe quelle fonction SQL.
Pour appliquer un tri, vous pouvez utiliser la méthode order
:
$query = $articles->find()
->order(['title' => 'ASC', 'id' => 'ASC']);
Si vous appelez order()
plusieurs fois sur la même requête, les clauses
s’ajouteront les unes aux autres. Néanmoins, quand vous utiliserez les finders,
vous aurez parfois besoin de remplacer la clause ORDER BY
déjà définie.
Passez Query::OVERWRITE
ou true
au second paramètre de order()
(idem
pour orderAsc()
ou orderDesc()
):
$query = $articles->find()
->order(['title' => 'ASC']);
// Plus tard, remplacez la clause ORDER BY au lieu de la compléter.
$query = $articles->find()
->order(['created' => 'DESC'], Query::OVERWRITE);
Vous pouvez utiliser les méthodes orderAsc
et orderDesc
pour trier selon
des expressions complexes:
$query = $articles->find();
$concat = $query->func()->concat([
'title' => 'identifier',
'synopsis' => 'identifier'
]);
$query->orderAsc($concat);
Pour construire des clauses de tri complexes, utilisez une Closure:
$query->orderAsc(function (QueryExpression $exp, Query $query) {
return $exp->addCase(...);
});
Pour limiter le nombre de lignes, ou définir un offset, utilisez les méthodes
limit()
et page()
:
// Récupérer les lignes 50 à 100
$query = $articles->find()
->limit(50)
->page(2);
Comme vous pouvez le voir sur cet exemple, toutes les méthodes qui modifient la requête proposent une interface fluide, vous permettant de construire une requête par l’appel de méthodes chaînées.
Quand vous utilisez les fonctions d’agrégation comme count
et sum
, vous
pouvez utiliser les clauses group by
et having
:
$query = $articles->find();
$query->select([
'count' => $query->func()->count('view_count'),
'published_date' => 'DATE(created)'
])
->group('published_date')
->having(['count >' => 3]);
L’ORM offre également l’expression SQL case
. L’expression case
permet
l’implémentation d’une logique if ... then ... else
dans votre SQL. Cela
peut être utile pour créer des rapports sur des données que vous avez besoin
d’additionner ou de compter conditionnellement, ou si vous avez besoin de
données spécifiques basées sur une condition.
Si vous vouliez savoir combien d’articles sont publiés dans notre base de données, nous pourrions utiliser le SQL suivant:
SELECT
COUNT(CASE WHEN published = 'Y' THEN 1 END) AS number_published,
COUNT(CASE WHEN published = 'N' THEN 1 END) AS number_unpublished
FROM articles
Pour faire ceci avec le générateur de requêtes, vous utiliseriez le code suivant:
$query = $articles->find();
$publishedCase = $query->newExpr()
->case()
->when(['published' => 'Y'])
->then(1);
$unpublishedCase = $query->newExpr()
->case()
->when(['published' => 'N'])
->then(1);
$query->select([
'number_published' => $query->func()->count($publishedCase),
'number_unpublished' => $query->func()->count($unpublishedCase)
]);
La méthode when()
accepte des extraits de code SQL, des conditions en
tableau, et des Closure
pour les situations où vous avez besoin d’une
logique supplémentaire pour construire les cas. Si nous souhaitions classer des
villes selon des tailles de population PETITE, MOYENNE, ou GRANDE, nous
pourrions le faire ainsi:
$query = $cities->find();
$sizing = $query->newExpr()->case()
->when(['population <' => 100000])
->then('PETITE')
->when($query->newExpr()->between('population', 100000, 999000))
->then('MOYENNE')
->when(['population >=' => 999001])
->then('GRANDE');
$query = $query->select(['size' => $sizing]);
# SELECT CASE
# WHEN population < 100000 THEN 'PETITE'
# WHEN population BETWEEN 100000 AND 999000 THEN 'MOYENNE'
# WHEN population >= 999001 THEN 'GRANDE'
# END AS size
Il faut que vous fassiez attention lorsque vous incluez dans des expression case des données fournies par l’utilisateur, car cela peut créer des vulnérabilités aux injections SQL:
// Non sécurisé. *Ne pas* utiliser
$case->when($requestData['published']);
// Au lieu de cela, passez les données utilisateur en valeurs dans un
// tableau de conditions
$case->when(['published' => $requestData['published']]);
Pour des scénarios plus complexes, vous pouvez utiliser les objets
QueryExpression
et les valeurs liées:
$userValue = $query->newExpr()
->case()
->when($query->newExpr('population >= :userData'))
->then(123, 'integer');
$query->select(['val' => $userValue])
->bind(':userData', $requestData['value'], 'integer');
L’utilisation des valeurs liées permet d’insérer des données utilisateur en
toute sécurité dans des bouts de code SQL bruts complexes. then()
,
when()
et else()
essayeront de deviner le type de valeur en se basant
sur le type de paramètre. Si vous avez besoin de lier une valeur d’un type
différent, vous pouvez déclarer le type souhaité:
$case->when(['published' => true])->then('1', 'integer');
Vous pouvez créer des conditions if ... then ... else
en utilisant
else()
:
$published = $query->newExpr()
->case()
->when(['published' => true])
->then('Y')
->else('N');
# CASE WHEN published = true THEN 'Y' ELSE 'N' END;
Il est également possible créer une variable simple en passant une valeur à
case()
:
- $published = $query->newExpr()
->case($query->identifier(“published”)) ->when(true) ->then(“Y”); ->else(“N”);
# CASE published WHEN true THEN “Y” ELSE “N” END;
Modifié dans la version 4.3.0: Ajout du builder fluide case()
.
Avant 4.3.0, vous deviez utiliser:
$query = $articles->find();
$publishedCase = $query->newExpr()
->addCase(
$query->newExpr()->add(['published' => 'Y']),
1,
'integer'
);
$unpublishedCase = $query->newExpr()
->addCase(
$query->newExpr()->add(['published' => 'N']),
1,
'integer'
);
$query->select([
'number_published' => $query->func()->count($publishedCase),
'number_unpublished' => $query->func()->count($unpublishedCase)
]);
La fonction addCase
peut aussi chaîner ensemble plusieurs instructions pour
créer une logique if .. then .. [elseif .. then .. ] [ .. else ]
dans
votre SQL.
Si nous souhaitions classer des villes selon des tailles de population PETITE, MOYENNE, ou GRANDE, nous pourrions le faire ainsi:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->addCase(
[
$q->newExpr()->lt('population', 100000),
$q->newExpr()->between('population', 100000, 999000),
$q->newExpr()->gte('population', 999001),
],
['PETITE', 'MOYENNE', 'GRANDE'], # valeurs correspondant aux conditions
['string', 'string', 'string'] # type de chaque valeur
);
});
# WHERE CASE
# WHEN population < 100000 THEN 'PETITE'
# WHEN population BETWEEN 100000 AND 999000 THEN 'MOYENNE'
# WHEN population >= 999001 THEN 'GRANDE'
# END
S’il y a moins de conditions que de valeurs, addCase
va automatiquement produire une instruction if .. then .. else
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->addCase(
[
$q->newExpr()->eq('population', 0),
],
['DESERTE', 'INHABITEE'], # valeurs correspondant aux conditions
['string', 'string'] # type de chaque valeur
);
});
# WHERE CASE
# WHEN population = 0 THEN 'DESERTE' ELSE 'INHABITEE' END
Bien que les ensembles de résultats en objet de l’ORM soient puissants, créer des entities est parfois superflu. Par exemple, quand vous accédez à des données agrégées, la construction d’une Entity n’a pas vraiment de sens. Le processus de conversion des résultats de la base de données en entities est appelé hydratation. Si vous souhaitez désactiver ce processus, vous pouvez faire ceci:
$query = $articles->find();
$query->enableHydration(false); // Résultats en tableaux plutôt qu'en entities
$result = $query->toList(); // Exécute la requête et retourne le tableau
Après avoir exécuté ces lignes, votre résultat devrait ressembler à quelque chose comme ceci:
[
['id' => 1, 'title' => 'Premier Article', 'body' => 'Corps de l\'article 1' ...],
['id' => 2, 'title' => 'Deuxième Article', 'body' => 'Corps de l\'article 2' ...],
...
]
Après vos requêtes, vous aurez peut-être besoin de faire des traitements
postérieurs. Si vous voulez ajouter quelques champs calculés ou des données
dérivées, vous pouvez utiliser la méthode formatResults()
. C’est une
façon légère d’appliquer une fonction sur les ensembles de résultats. Si vous
avez besoin de plus de contrôle sur le processus, ou si vous souhaitez réduire
les résultats, vous devriez plutôt utiliser la fonctionnalité de
Map/Reduce. Si vous requêtiez sur une liste de personnes,
vous pourriez calculer leur âge avec un formateur de résultats (result
formatter):
// En supposant que nous avons construit les champs, les conditions et les contain.
$query->formatResults(function (\Cake\Collection\CollectionInterface $results) {
return $results->map(function ($row) {
$row['age'] = $row['date_de_naissance']->diff(new \DateTime)->y;
return $row;
});
});
Comme vous pouvez le voir dans cet exemple, les callbacks de formatage
récupéreront un ResultSetDecorator
en premier argument. Le second argument
sera l’instance Query sur laquelle le formateur a été attaché. L’argument
$results
peut être traversé et modifié autant que nécessaire.
Les formateurs de résultat deivent retourner un objet itérateur,
qui sera utilisé comme valeur retournée pour la requête. Les fonctions de
formatage sont appliquées après que toutes les routines Map/Reduce auront été
exécutées. Les formateurs de résultat peuvent aussi être
appliqués depuis des associations contain
. CakePHP va s’assurer que vos
formateurs s’appliquent au bon périmètre. Par exemple, ceci fonctionnera
comme vous pouvez vous y attendre:
// Dans une méthode dans la table Articles
$query->contain(['Auteurs' => function ($q) {
return $q->formatResults(function (\Cake\Collection\CollectionInterface $auteurs) {
return $auteurs->map(function ($auteur) {
$auteur['age'] = $auteur['date_de_naissance']->diff(new \DateTime)->y;
return $auteur;
});
});
}]);
// Récupère les résultats
$results = $query->all();
// Affiche 29
echo $results->first()->auteur->age;
Comme vu précédemment, les formateurs attachés aux constructeurs de requête associées sont limités pour agir seulement sur les données de l’association. CakePHP va s’assurer que les valeurs calculées soient insérées dans la bonne entity.
Le constructeur de requête facilite la construction de clauses where
complexes. Les conditions groupées peuvent être exprimées en fournissant
une combinaison de where()
et d’objets représentant une expression. Pour les
requêtes simples, vous pouvez construire des conditions en utilisant un tableau
de conditions:
$query = $articles->find()
->where([
'auteur_id' => 3,
'OR' => [['nombre_de_vues' => 2], ['nombre_de_vues' => 3]],
]);
Ce qui précède générerait le code SQL:
SELECT * FROM articles WHERE auteur_id = 3 AND (nombre_de_vues = 2 OR nombre_de_vues = 3)
Si vous préférez éviter des tableaux avec de nombreux niveaux imbriqués, vous pouvez
utiliser where()
avec une fonction de rappel pour construire vos requêtes.
La fonction de rappel prend une QueryExpression, ce qui vous permet d’utiliser
l’interface du constructeur d’expression pour construire des conditions
complexes sans utiliser de tableaux. Par exemple:
$query = $articles->find()->where(function (QueryExpression $exp, Query $query) {
// Utilisez add() pour ajouter des conditions multiples sur le même champ.
$auteur = $query->newExpr()->or(['auteur_id' => 3])->add(['auteur_id' => 2]);
$published = $query->newExpr()->and(['published' => true, 'nombre_de_vues' => 10]);
return $exp->or([
'promoted' => true,
$query->newExpr()->and([$auteur, $published])
]);
});
Ce qui précède générerait le code SQL:
SELECT *
FROM articles
WHERE (
(
(auteur_id = 2 OR auteur_id = 3)
AND
(published = 1 AND nombre_de_vues = 10)
)
OR promoted = 1
)
La QueryExpression
passée à la fonction de rappel vous permet d’utiliser à
la fois les combinators et les conditions pour construire votre
expression globale.
Ils créent de nouveaux objets QueryExpression
et définissent la façon
dont les conditions ajoutées à cette expression sont combinées.
- and()
crée un nouvel objet expression qui combine toutes les conditions avec AND
.
- or()
crée un nouvel objet expression qui combine toutes les conditions avec OR
.
Elles sont ajoutées à l’expression et combinées automatiquement selon le combinator qui a été utilisé.
La QueryExpression
passée à la fonction de rappel est par défaut and()
:
$query = $articles->find()
->where(function (QueryExpression $exp) {
return $exp
->eq('auteur_id', 2)
->eq('published', true)
->notEq('spam', true)
->gt('nombre_de_vues', 10);
});
Puisque nous avons commencé à utiliser where()
, nous n’avons pas besoin
d’appeler and()
, puisqu’elle est appelée implicitement. Le code ci-dessus
montre quelques nouvelles méthodes de conditions combinées avec AND
. Le code
SQL résultant serait:
SELECT *
FROM articles
WHERE (
auteur_id = 2
AND published = 1
AND spam != 1
AND nombre_de_vues > 10)
Cependant, si nous souhaitons utiliser les deux conditions AND
& OR
,
nous pouvons faire ceci:
$query = $articles->find()
->where(function (QueryExpression $exp) {
$orConditions = $exp->or(['auteur_id' => 2])
->eq('auteur_id', 5);
return $exp
->add($orConditions)
->eq('published', true)
->gte('nombre_de_vues', 10);
});
Ce qui générerait le code SQL suivant:
SELECT *
FROM articles
WHERE (
(auteur_id = 2 OR auteur_id = 5)
AND published = 1
AND nombre_de_vues > 10
)
Les combinators vous autorisent aussi à passer une fonction de rappel, qui prend en paramètre un nouvel objet expression, si vous voulez scinder le chaînage des méthodes:
$query = $articles->find()
->where(function (QueryExpression $exp) {
$orConditions = $exp->or(function (QueryExpression $or) {
return $or->eq('auteur_id', 2)
->eq('auteur_id', 5);
});
return $exp
->not($orConditions)
->lte('nombre_de_vues', 10);
});
Vous pouvez faire une négation des sous-expressions en utilisant not()
:
$query = $articles->find()
->where(function (QueryExpression $exp) {
$orConditions = $exp->or(['author_id' => 2])
->eq('author_id', 5);
return $exp
->not($orConditions)
->lte('view_count', 10);
});
Ce qui générerait le code SQL suivant:
SELECT *
FROM articles
WHERE (
NOT (auteur_id = 2 OR auteur_id = 5)
AND view_count <= 10
)
Il est aussi possible de construire les expressions en utilisant les fonctions SQL:
$query = $articles->find()
->where(function (QueryExpression $exp, Query $q) {
$year = $q->func()->year([
'created' => 'identifier'
]);
return $exp
->gte($year, 2014)
->eq('published', true);
});
Ce qui générerait le code SQL suivant:
SELECT *
FROM articles
WHERE (
YEAR(created) >= 2014
AND published = 1
)
Quand vous utilisez les objets expression, vous pouvez utiliser les méthodes suivantes pour créer des conditions:
eq()
Crée une condition d’égalité:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->eq('population', '10000');
});
# WHERE population = 10000
notEq()
Crée une condition d’inégalité:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->notEq('population', '10000');
});
# WHERE population != 10000
like()
Crée une condition en utilisant l’opérateur LIKE
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->like('name', '%A%');
});
# WHERE name LIKE "%A%"
notLike()
Crée une condition négative de type LIKE
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->notLike('name', '%A%');
});
# WHERE name NOT LIKE "%A%"
in()
Crée une condition en utilisant IN
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->in('pays_id', ['AFG', 'USA', 'EST']);
});
# WHERE pays_id IN ('AFG', 'USA', 'EST')
notIn()
Crée une condition négative en utilisant IN
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->notIn('pays_id', ['AFG', 'USA', 'EST']);
});
# WHERE pays_id NOT IN ('AFG', 'USA', 'EST')
gt()
Crée une condition >
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->gt('population', '10000');
});
# WHERE population > 10000
gte()
Crée une condition >=
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->gte('population', '10000');
});
# WHERE population >= 10000
lt()
Crée une condition <
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->lt('population', '10000');
});
# WHERE population < 10000
lte()
Crée une condition <=
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->lte('population', '10000');
});
# WHERE population <= 10000
isNull()
Crée une condition IS NULL
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->isNull('population');
});
# WHERE (population) IS NULL
isNotNull()
Crée une condition négative IS NULL
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->isNotNull('population');
});
# WHERE (population) IS NOT NULL
between()
Crée une condition BETWEEN
:
$query = $villes->find()
->where(function (QueryExpression $exp, Query $q) {
return $exp->between('population', 999, 5000000);
});
# WHERE population BETWEEN 999 AND 5000000,
exists()
Crée une condition en utilisant EXISTS
:
$subquery = $villes->find()
->select(['id'])
->where(function (QueryExpression $exp, Query $q) {
return $exp->equalFields('pays.id', 'villes.pays_id');
})
->andWhere(['population >', 5000000]);
$query = $pays->find()
->where(function (QueryExpression $exp, Query $q) use ($subquery) {
return $exp->exists($subquery);
});
# WHERE EXISTS (SELECT id FROM villes WHERE pays.id = villes.pays_id AND population > 5000000)
notExists()
Crée une condition négative en utilisant EXISTS
:
$subquery = $villes->find()
->select(['id'])
->where(function (QueryExpression $exp, Query $q) {
return $exp->equalFields('pays.id', 'villes.pays_id');
})
->andWhere(['population >', 5000000]);
$query = $pays->find()
->where(function (QueryExpression $exp, Query $q) use ($subquery) {
return $exp->notExists($subquery);
});
# WHERE NOT EXISTS (SELECT id FROM villes WHERE pays.id = villes.pays_id AND population > 5000000)
Les objets expression devraient couvrir la plupart des fonctions et expressions
communément utilisées. Si vous vous sentez incapable de créer les conditions
voulues avec des expressions, vous vous sentirez peut-être capable d’utiliser
bind()
pour lier manuellement les paramètres aux conditions:
$query = $villes->find()
->where([
'date_debut BETWEEN :debut AND :fin'
])
->bind(':debut', '2014-01-01', 'date')
->bind(':fin', '2014-12-31', 'date');
Dans les cas où vous ne pouvez ou ne voulez pas utiliser les méthodes du constructeur pour créer les conditions que vous voulez, vous pouvez utiliser du code SQL dans des clauses where:
// Compare deux champs l'un avec l'autre
$query->where(['Categories.parent_id != Parents.id']);
Avertissement
Les noms de champs utilisés dans les expressions et le code SQL ne doivent jamais contenir de contenu non fiable, sinon vous ouvrez la porte à une injection SQL. Référez-vous à la section Utiliser les Fonctions SQL pour savoir comment inclure des données non fiables de manière sécurisée dans vos appels de fonctions.
Quand vous avez besoin de faire référence à une colonne ou à un identificateur
SQL dans vos requêtes, vous pouvez utiliser la méthode identifier()
:
$query = $pays->find();
$query->select([
'year' => $query->func()->year([$query->identifier('created')])
])
->where(function ($exp, $query) {
return $exp->gt('population', 100000);
});
Avertissement
Pour prévenir les injections SQL, les expressions Identifier ne doivent jamais contenir de données non fiables.
Quand vous construisez des requêtes en utilisant l’ORM, vous n’avez
généralement pas besoin d’indiquer les types de données des colonnes avec
lesquelles vous interagissez, puisque CakePHP peut déduire les types en se
basant sur les données du schéma. Si dans vos requêtes, vous souhaitez que
CakePHP convertisse automatiquement l’égalité en comparaisons IN
, vous
devez indiquer les types de données des colonnes:
$query = $articles->find()
->where(['id' => $ids], ['id' => 'integer[]']);
// Ou bien incluez IN pour caster automatiquement en tableau
$query = $articles->find()
->where(['id IN' => $ids]);
Ceci va créer automatiquement id IN (...)
plutôt que
id = ?
. Cela peut être utile quand vous ne savez pas si vous allez
récupérer un scalaire ou un tableau de paramètres. Le suffixe []
sur un
nom de type de données indique au constructeur de requête que vous souhaitez
que les données soient gérées en tableau. Si les données ne sont pas un tableau,
elles vont d’abord être converties en tableau. Après cela, chaque valeur dans
le tableau va être convertie en utilisant le
système de types. Cela fonctionne aussi avec des types
complexes. Par exemple, vous pourriez prendre une liste d’objets DateTime
en utilisant:
$query = $articles->find()
->where(['post_date' => $dates], ['post_date' => 'date[]']);
Quand une valeur dans une condition est supposée être null
, ou bien une
valeur quelconque,
vous pouvez utiliser l’opérateur IS
pour créer automatiquement la bonne
expression:
$query = $categories->find()
->where(['parent_id IS' => $parentId]);
Ce code va créer parent_id` = :c1
ou parent_id IS NULL
selon le
type de $parentId
.
Quand une valeur dans une condition est supposée ne pas être null
, ou ne pas
être une valeur quelconque,
vous pouvez utiliser l’opérateur IS NOT
pour créer automatiquement la bonne
expression:
$query = $categories->find()
->where(['parent_id IS NOT' => $parentId]);
Ce code va créer parent_id` != :c1
ou parent_id IS NOT NULL
selon le type de $parentId
.
Si le constructeur de requêtes ne vous permet pas de construire le code SQL
que vous souhaitez, vous pouvez utiliser les objets Expression
pour
ajouter directement du code SQL à vos requêtes:
$query = $articles->find();
$expr = $query->newExpr()->add('1 + 1');
$query->select(['deux' => $expr]);
Les objets Expression
peuvent être utilisés avec n’importe quelle méthode du
constructeur de requêtes comme where()
, limit()
, group()
,
select()
et bien d’autres.
Avertissement
Les objets Expression
vous rendent vulnérable aux injections SQL.
Vous devez absolument éviter d’utiliser dans les expressions des données
venant d’une source non fiable.
Une fois que vous aurez fait votre requête, vous voudrez récupérer les résultats. Il y a plusieurs façons de faire:
// Itérer la requête
foreach ($query as $row) {
// Faire le boulot.
}
// Récupérer les résultats
$results = $query->all();
Vous pouvez utiliser toutes les méthodes des collections sur vos objets query pour retraiter ou transformer les résultats:
// Utilise une des méthodes collection.
$ids = $query->map(function ($row) {
return $row->id;
});
$maxAge = $query->max(function ($max) {
return $max->age;
});
Vous pouvez utiliser first()
ou firstOrFail()
pour récupérer un
enregistrement unique. Ces méthodes vont modifier la requête en ajoutant
une clause LIMIT 1
:
// Récupère uniquement la première ligne
$row = $query->first();
// Récupère la première ligne ou une exception.
$row = $query->firstOrFail();
Il est possible d’obtenir le nombre total de lignes trouvées pour un ensemble de conditions en utilisant un seul objet query:
$total = $articles->find()->where(['is_active' => true])->count();
La méthode count()
va ignorer les clauses limit
, offset
et page
,
donc ce qui suit va retourner les mêmes résultats:
$total = $articles->find()->where(['is_active' => true])->limit(10)->count();
C’est utile quand vous avez besoin de connaître le nombre de résultats à
l’avance, sans avoir à construire un autre objet Query
. De la même façon,
tous les formatages de résultats et les routines map-reduce sont ignorées
quand vous utilisez la méthode count()
.
De plus, il est possible d’obtenir le nombre total de lignes pour une requête contenant des clauses group by sans avoir à réécrire la requête de quelque façon que ce soit. Par exemple, considérons la requête qui permet de récupérer les ids d’article et leurs nombres de commentaires:
$query = $articles->find();
$query->select(['Articles.id', $query->func()->count('Comments.id')])
->matching('Comments')
->group(['Articles.id']);
$total = $query->count();
Après avoir compté, la requête peut toujours être utilisée pour récupérer les enregistrements associés:
$list = $query->all();
Vous voudrez parfois fournir une méthode alternative pour compter le nombre
total d’enregistrements d’une requête. Un cas d’utilisation courante de cette
fonctionnalité est de fournir une valeur mise en cache ou une estimation du
nombre total de lignes, ou de modifier la requête pour retirer les parties
coûteuses non nécessaires comme les left joins. Ceci devient particulièrement
pratique quand vous utilisez le système de pagination intégré à CakePHP qui
appelle la méthode count()
:
$query = $query->where(['is_active' => true])->counter(function ($query) {
return 100000;
});
$query->count(); // Retourne 100000
Dans l’exemple ci-dessus, quand le component Pagination appellera la méthode count, il recevra le nombre de lignes estimé codé en dur.
Quand vous récupérez des entities qui ne changent pas souvent, vous voudrez
peut-être mettre en cache les résultats. La classe Query
facilite cela:
$query->cache('recent_articles');
va activer la mise en cache des résultats de la requête. Si un seul
argument est fourni à cache()
alors c’est la configuration du cache
“default” qui sera utilisée. Vous pouvez contrôler la configuration de cache à
utiliser avec le deuxième paramètre:
// Nom de la config.
$query->cache('recent_articles', 'dbResults');
// Instance de CacheEngine
$query->cache('recent_articles', $memcache);
En plus de supporter les clés statiques, la méthode cache()
accepte une
fonction pour générer la clé. La fonction que vous lui donnez va recevoir la
requête en argument. Vous pouvez ensuite lire les aspects de la requête pour
générer dynamiquement la clé de mise en cache:
// Génère une clé basée sur un checksum simple
// de la clause where de la requête
$query->cache(function ($q) {
return 'articles-' . md5(serialize($q->clause('where')));
});
La méthode cache facilite l’ajout des résultats mis en cache à vos finders personnalisés ou à travers des écouteurs d’évènements.
Quand les résultats d’une requête mise en cache sont récupérés, voici ce qui va se passer:
Si la requête a des résultats, ceux-ci vont être retournés.
La clé du cache va être déterminée et les données du cache vont être lues. Si les données du cache ne sont pas vides, ces résultats vont être retournés.
Si le cache est manquant, la requête sera exécutée, l’événement Model.beforeFind
sera déclenché, et un nouveau ResultSet
sera créé. Ce ResultSet
sera écrit dans le cache et retourné.
Note
Vous ne pouvez pas mettre en cache un résultat de requête streaming.
Le constructeur peut vous aider à récupérer les données de plusieurs tables à la fois avec un minimum de requêtes. Pour pouvoir récupérer les données associées, vous devez d’abord configurer les associations entre les tables comme décrit dans la section Associations - Lier les Tables Ensemble. Cette technique de requêtes combinées pour récupérer les données associées à partir d’autres tables est appelé eager loading.
L’eager loading aide à éviter la plupart des problèmes potentiels de performance qui entourent le lazy loading dans un ORM. Les requêtes générées par eager loading peuvent davantage tirer parti des jointures, ce qui permet de créer des requêtes plus efficaces. Dans CakePHP, vous utilisez la méthode “contain” pour indiquer quelles associations doivent être chargées en eager:
// Dans un controller ou une méthode de table.
// En option du find()
$query = $articles->find('all', ['contain' => ['Authors', 'Comments']]);
// En méthode sur un objet query
$query = $articles->find('all');
$query->contain(['Authors', 'Comments']);
Ceci va charger les auteurs et commentaires liés à chaque article du result set. Vous pouvez charger des associations imbriquées en utilisant les tableaux imbriqués pour définir les associations à charger:
$query = $articles->find()->contain([
'Authors' => ['Addresses'], 'Comments' => ['Authors']
]);
Au choix, vous pouvez aussi exprimer des associations imbriquées en utilisant la notation par points:
$query = $articles->find()->contain([
'Authors.Addresses',
'Comments.Authors'
]);
Vous pouvez charger les associations en eager aussi profondément que vous le souhaitez:
$query = $produits->find()->contain([
'Shops.Cities.Countries',
'Shops.Managers'
]);
Vous pouvez sélectionner des champs de toutes les associations en utilisant
plusieurs appels à contain()
:
$query = $this->find()->select([
'Realestates.id',
'Realestates.title',
'Realestates.description'
])
->contain([
'RealestatesAttributes' => [
'Attributes' => [
'fields' => [
// Les champs dépendant d'un alias doivent inclure le préfixe
// du modèle dans contain() pour être mappés correctement.
'Attributes__name' => 'attr_name'
]
]
]
])
->contain([
'RealestatesAttributes' => [
'fields' => [
'RealestatesAttributes.realestate_id',
'RealestatesAttributes.value'
]
]
])
->where($condition);
Si vous avez besoin de réinitialiser les contain sur une requête, vous pouvez
définir le second argument à true
:
$query = $articles->find();
$query->contain(['Authors', 'Comments'], true);
Note
Les noms d’association dans les appels à contain()
doivent respecter la
casse (majuscules/minuscules) avec lequelle votre association a été définie,
et non pas selon le nom de la propriété utilisée pour accéder aux données
associées depuis l’entity. Par exemple, si vous avez déclaré une association
par belongsTo('Users')
, alors vous devez utiliser contain('Users')
et pas contain('users')
ni contain('user')
.
Avec l’utilisation de contain()
, vous pouvez restreindre les données
retournées par les associations et les filtrer par conditions. Pour spécifier
des conditions, passez une fonction anonyme qui reçoit en premier argument la
query, de type \Cake\ORM\Query
:
// Dans un controller ou une méthode de table.
$query = $articles->find()->contain('Comments', function (Query $q) {
return $q
->select(['contenu', 'author_id'])
->where(['Comments.approved' => true]);
});
Cela fonctionne aussi pour la pagination au niveau du Controller:
$this->paginate['contain'] = [
'Comments' => function (Query $query) {
return $query->select(['body', 'author_id'])
->where(['Comments.approved' => true]);
}
];
Avertissement
Si vous constatez qu’il manque des entités associées, vérifiez que les champs de clés étrangères sont bien sélectionnés dans la requête. Sans les clés étrangères, l’ORM ne peut pas retrouver les lignes correspondantes.
Il est aussi possible de restreindre les associations imbriquées en utilisant la notation par point:
$query = $articles->find()->contain([
'Comments',
'Authors.Profiles' => function (Query $q) {
return $q->where(['Profiles.is_published' => true]);
}
]);
Dans cet exemple, vous obtiendrez les auteurs même s’ils n’ont pas de profil publié. Pour ne récupérer que les auteurs avec un profil publié, utilisez matching().
Si vous avez des finders personnalisés dans votre table associée,
vous pouvez les utiliser à l’intérieur de contain()
:
// Récupère tous les articles, mais récupère seulement les commentaires qui
// sont approuvés et populaires.
$query = $articles->find()->contain('Comments', function ($q) {
return $q->find('approved')->find('popular');
});
Note
Pour les associations BelongsTo
et HasOne
, seules les clauses
where
et select
sont utilisées lors du chargement par contain()
.
Avec HasMany
et BelongsToMany
, toutes les clauses sont valides,
telles que order()
.
Vous pouvez contrôler plus que les simples clauses utilisées par contain()
.
Si vous passez un tableau avec l’association, vous pouvez surcharger
foreignKey
, joinType
et strategy
. Reportez-vous à
Associations - Lier les Tables Ensemble pour plus de détails sur la valeur par défaut et les
options de chaque type d’association.
Vous pouvez passer false
comme nouvelle valeur de foreignKey
pour
désactiver complètement les contraintes liées aux clés étrangères.
Utilisez l’option queryBuilder
pour personnaliser la requête quand vous
passez un tableau:
$query = $articles->find()->contain([
'Authors' => [
'foreignKey' => false,
'queryBuilder' => function (Query $q) {
return $q->where(...); // Conditions complètes pour le filtrage
}
]
]);
Si vous avez limité les champs que vous chargez avec select()
mais que
vous souhaitez aussi charger les champs des associations avec contain,
vous pouvez passer l’objet association à select()
:
// Sélectionne id & title de articles, mais aussi tous les champs de Users.
$query = $articles->find()
->select(['id', 'title'])
->select($articles->Users)
->contain(['Users']);
Autre possibilité, si vous avez des associations multiples, vous pouvez utiliser
enableAutoFields()
:
// Sélectionne id & title de articles, mais tous les champs de
// Users, Comments et Tags.
$query->select(['id', 'title'])
->contain(['Comments', 'Tags'])
->enableAutoFields(true)
->contain(['Users' => function(Query $q) {
return $q->autoFields(true);
}]);
Quand vous chargez des associations HasMany et BelongsToMany, vous pouvez
utiliser l’option sort
pour trier les données dans ces associations:
$query->contain([
'Comments' => [
'sort' => ['Comments.created' => 'DESC']
]
]);
Un cas de requête couramment utilisé avec les associations consiste à trouver les enregistrements qui correspondent à certaines données associées. Par exemple si vous avez une association “Articles belongsToMany Tags”, vous voudrez probablement trouver les Articles qui portent le tag CakePHP. C’est extrêmement simple à faire avec l’ORM de CakePHP:
// Dans un controller ou une méthode de table.
$query = $articles->find();
$query->matching('Tags', function ($q) {
return $q->where(['Tags.name' => 'CakePHP']);
});
Vous pouvez aussi appliquer cette stratégie aux associations HasMany. Par exemple si “Authors HasMany Articles”, vous pouvez trouver tous les auteurs ayant publié un article récemment en écrivant ceci:
$query = $authors->find();
$query->matching('Articles', function ($q) {
return $q->where(['Articles.created >=' => new DateTime('-10 days')]);
});
La syntaxe de contain()
, qui doit déjà vous être familière, permet aussi de
filtrer des associations imbriquées:
// Dans un controller ou une méthode de table.
$query = $produits->find()->matching(
'Shops.Cities.Countries', function ($q) {
return $q->where(['Countries.name' => 'Japon']);
}
);
// Récupère les articles qui ont été commentés par 'markstory',
// en passant une variable.
// Utiliser la notation par points plutôt que des appels imbriqués à matching()
$username = 'markstory';
$query = $articles->find()->matching('Comments.Users', function ($q) use ($username) {
return $q->where(['username' => $username]);
});
Note
Dans la mesure où cette fonction va créer un INNER JOIN
, il serait
judicieux d’utiliser distinct
dans la requête. Sinon, vous risquez
d’obtenir des doublons si les conditions posées ne l’excluent pas par
principe. Dans notre exemple, cela peut être le cas si un utilisateur
commente plusieurs fois le même article.
Les données des associations qui correspondent aux conditions (données
matchées) seront disponibles
dans l’attribut _matchingData
des entities. Si vous utilisez à la fois
match
et contain
sur la même association, vous pouvez vous attendre à
avoir à la fois la propriété _matchingData
et la propriété standard
d’association dans vos résultats.
Utiliser la fonction matching()
, comme nous l’avons vu précédemment, va
créer un INNER JOIN
avec l’association spécifiée et va aussi charger les
champs dans un ensemble de résultats.
Il peut arriver que vous veuillez utiliser matching()
mais que vous n’êtes
pas intéressé par le chargement des champs de l’association. Dans ce cas, vous
pouvez utiliser innerJoinWith()
:
$query = $articles->find();
$query->innerJoinWith('Tags', function ($q) {
return $q->where(['Tags.name' => 'CakePHP']);
});
La méthode innerJoinWith()
fonctionne de la même manière que matching()
,
ce qui signifie que vous pouvez utiliser la notation par points pour faire des
jointures pour les associations imbriquées profondément:
$query = $products->find()->innerJoinWith(
'Shops.Cities.Countries', function ($q) {
return $q->where(['Countries.name' => 'Japon']);
}
);
Si vous voulez à la fois poser des conditions sur certains champs de
l’association et charger d’autres champs de cette même association, vous pouvez
parfaitement combiner innerJoinWith()
et contain()
.
L’exemple ci-dessous filtre les Articles qui ont des Tags spécifiques et charge
ces Tags:
$filter = ['Tags.name' => 'CakePHP'];
$query = $articles->find()
->distinct($articles->getPrimaryKey())
->contain('Tags', function (Query $q) use ($filter) {
return $q->where($filter);
})
->innerJoinWith('Tags', function (Query $q) use ($filter) {
return $q->where($filter);
});
Note
Si vous utilisez innerJoinWith()
et que vous voulez sélectionner des
champs de cette association avec select()
, vous devez utiliser un alias
pour les noms des champs:
$query
->select(['country_name' => 'Countries.name'])
->innerJoinWith('Countries');
Sinon, vous verrez les données dans _matchingData
, comme cela a été
décrit ci-dessous à propos de matching()
. C’est un angle mort de
matching()
, qui ne sait pas que vous avez sélectionné des champs.
Avertissement
Vous ne devez pas combiner innerJoinWith()
and matching()
pour la
même association. Cela produirait de multiple requêtes INNER JOIN
et ne
réaliserait pas ce que vous en attendez.
L’opposé de matching()
est notMatching()
. Cette fonction va changer
la requête pour qu’elle filtre les résultats qui n’ont pas de relation avec
l’association spécifiée:
// Dans un controller ou une méthode de table.
$query = $articlesTable
->find()
->notMatching('Tags', function ($q) {
return $q->where(['Tags.name' => 'ennuyeux']);
});
L’exemple ci-dessus va trouver tous les articles qui n’ont pas été taggés avec
le mot ennuyeux
. Vous pouvez aussi utiliser cette méthode avec les
associations HasMany. Vous pouvez, par exemple, trouver tous les auteurs qui
n’ont publié aucun article dans les 10 derniers jours:
$query = $authorsTable
->find()
->notMatching('Articles', function ($q) {
return $q->where(['Articles.created >=' => new \DateTime('-10 days')]);
});
Il est aussi possible d’utiliser cette méthode pour filtrer les enregistrements qui ne matchent pas des associations imbriquées. Par exemple, vous pouvez trouver les articles qui n’ont pas été commentés par un utilisateur précis:
$query = $articlesTable
->find()
->notMatching('Comments.Users', function ($q) {
return $q->where(['username' => 'jose']);
});
Puisque les articles n’ayant absolument aucun commentaire satisfont aussi cette
condition, vous aurez intérêt à combiner matching()
et notMatching()
dans cette requête. L’exemple suivant recherchera les articles ayant au moins un
commentaire, mais non commentés par un utilisateur précis:
$query = $articlesTable
->find()
->notMatching('Comments.Users', function ($q) {
return $q->where(['username' => 'jose']);
})
->matching('Comments');
Note
Comme notMatching()
va créer un LEFT JOIN
, vous pouvez envisager
d’appeler distinct
sur la requête pour éviter d’obtenir des lignes
dupliquées.
Gardez à l’esprit que le contraire de la fonction matching()
,
notMatching()
, ne va pas ajouter de données à la propriété _matchingData
dans les résultats.
Dans certaines situations, vous aurez à calculer un résultat à partir d’une
association, sans avoir à charger tous ses enregistrements. Par
exemple, si vous voulez charger le nombre total de commentaires d’un article, en
parallèle des données de l’article, vous pouvez utiliser la fonction
leftJoinWith()
:
$query = $articlesTable->find();
$query->select(['total_comments' => $query->func()->count('Comments.id')])
->leftJoinWith('Comments')
->group(['Articles.id'])
->enableAutoFields(true);
Le résultat de cette requête contiendra les données de l’article et la propriété
total_comments
pour chacun d’eux.
leftJoinWith()
peut aussi être utilisée avec des associations imbriquées.
C’est utile par exemple pour rechercher, pour chaque auteur, le nombre
d’articles taggés avec un certain mot:
$query = $authorsTable
->find()
->select(['total_articles' => $query->func()->count('Articles.id')])
->leftJoinWith('Articles.Tags', function ($q) {
return $q->where(['Tags.name' => 'redoutable']);
})
->group(['Authors.id'])
->enableAutoFields(true);
Cette fonction ne va charger aucune colonne des associations spécifiées dans les résultats.
En plus de charger les données liées avec contain()
, vous pouvez aussi
ajouter des jointures supplémentaires avec le constructeur de requête:
$query = $articles->find()
->join([
'table' => 'comments',
'alias' => 'c',
'type' => 'LEFT',
'conditions' => 'c.article_id = articles.id',
]);
Vous pouvez ajouter plusieurs jointures en même temps en passant un tableau
associatif avec plusieurs join
:
$query = $articles->find()
->join([
'c' => [
'table' => 'comments',
'type' => 'LEFT',
'conditions' => 'c.article_id = articles.id',
],
'u' => [
'table' => 'users',
'type' => 'INNER',
'conditions' => 'u.id = articles.user_id',
]
]);
Comme vu précédemment, lors de l’ajout de join
, l’alias peut être la clé du
tableau externe. Les conditions join
peuvent être aussi exprimées en tableau
de conditions:
$query = $articles->find()
->join([
'c' => [
'table' => 'comments',
'type' => 'LEFT',
'conditions' => [
'c.created >' => new DateTime('-5 days'),
'c.moderated' => true,
'c.article_id = articles.id'
]
],
], ['c.created' => 'datetime', 'c.moderated' => 'boolean']);
Lors de la création de join
à la main, et l’utilisation de conditions à
partir d’un tableau, vous devez fournir les types de données pour chaque colonne
dans les conditions du join
. En fournissant les types de données pour les
conditions de join
, l’ORM peut convertir correctement les types de données
en code SQL. En plus de join()
vous pouvez utiliser rightJoin()
,
leftJoin()
et innerJoin()
pour créer les jointures:
// Jointure avec un alias et des conditions littérales
$query = $articles->find();
$query->leftJoin(
['Auteurs' => 'auteurs'],
['Auteurs.id = Articles.auteur_id']);
// Jointure avec un alias, tableau de conditions, et types
$query = $articles->find();
$query->innerJoin(
['Auteurs' => 'auteurs'],
[
'Auteurs.promoted' => true,
'Auteurs.created' => new DateTime('-5 days'),
'Auteurs.id = Articles.auteur_id'
],
['Auteurs.promoted' => 'boolean', 'Auteurs.created' => 'datetime']);
Notez que si vous définissez l’option quoteIdentifiers
à true
quand vous
configurez votre Connection
, les conditions de jointure entre deux champs de
tables doivent être définies de cette manière:
$query = $articles->find()
->join([
'c' => [
'table' => 'comments',
'type' => 'LEFT',
'conditions' => [
'c.article_id' => new \Cake\Database\Expression\IdentifierExpression('articles.id')
]
],
]);
Cela permet de s’assurer que tous les identifiers
seront bien quotés dans la
requête générée, permettant d’éviter des erreurs avec certains drivers
(PostgreSQL notamment).
Contrairement aux exemples précédents, vous ne devez pas utiliser find()
pour créer des requêtes d’insertion. À la place, créez un nouvel objet Query
en utilisant query()
:
$query = $articles->query();
$query->insert(['titre', 'corps'])
->values([
'titre' => 'Premier post',
'corps' => 'Un corps de texte'
])
->execute();
Pour insérer plusieurs lignes en une seule requête, vous pouvez chaîner la
méthode values()
autant de fois que nécessaire:
$query = $articles->query();
$query->insert(['titre', 'corps'])
->values([
'titre' => 'Premier post',
'corps' => 'Un corps de texte'
])
->values([
'titre' => 'Second post',
'corps' => 'Un autre corps de texte'
])
->execute();
Généralement, il est plus facile d’insérer des données en utilisant les
entities et save()
. En composant des requêtes
SELECT
et INSERT
ensemble, vous pouvez créer des requêtes du style
INSERT INTO ... SELECT
:
$select = $articles->find()
->select(['titre', 'corps', 'published'])
->where(['id' => 3]);
$query = $articles->query()
->insert(['titre', 'corps', 'published'])
->values($select)
->execute();
Note
L’insertion d’enregistrements avec le constructeur de requêtes ne va pas
déclencher les événements comme Model.afterSave
. À la place, vous pouvez
utiliser l’ORM pour sauvegarder les données.
Comme pour les requêtes d’insertion, vous ne devez pas utiliser find()
pour
créer des requêtes de mise à jour. A la place, créez un nouvel objet Query
en utilisant query()
:
$query = $articles->query();
$query->update()
->set(['published' => true])
->where(['id' => $id])
->execute();
Généralement, il est plus facile de mettre à jour des données en utilisant des
entities et patchEntity()
.
Note
La mise à jour d’enregistrements avec le constructeur de requêtes ne va pas
déclencher les événements comme Model.afterSave
. À la place, vous pouvez
utiliser l’ORM pour sauvegarder des données.
Comme pour les requêtes d’insertion, vous ne devez pas utiliser find()
pour créer des requêtes de suppression. À la place, créez un nouvel objet de
requête en utilisant query()
:
$query = $articles->query();
$query->delete()
->where(['id' => $id])
->execute();
Généralement, il est plus facile de supprimer les données en utilisant les
entities et delete()
.
Bien que l’ORM et les couches d’abstraction de base de données vous prémunisse de la plupart des problèmes relatifs aux injections SQL, il est toujours possible de vous rendre vulnérable en raison d’une utilisation inappropriée.
Lorsque vous utilisez des tableaux de conditions, la clé (la partie à gauche) aussi bien que les valeurs simples ne doivent pas contenir de données utilisateur:
$query->where([
// Utiliser cette clé est dangereux car elle sera insérée telle quelle
// dans la requête générée
$userData => $value,
// Même chose pour les valeurs simples : il est
// dangereux de les utiliser avec des données utilisateur
$userData,
"MATCH (comment) AGAINST ($userData)",
'created < NOW() - ' . $userData
]);
Lorsque vous utilisez le constructeur d’expressions, les noms de colonnes ne doivent pas contenir de données provenant d’utilisateurs:
$query->where(function (QueryExpression $exp) use ($userData, $values) {
// Dans toutes les expressions, les noms de colonnes ne sont pas sûrs.
return $exp->in($userData, $values);
});
Lorsque vous construisez des expressions de fonctions, les noms de fonctions ne doivent jamais contenir de données provenant d’utilisateurs:
// Non sécurisé.
$query->func()->{$userData}($arg1);
// L'utilisation d'un tableau de données utilisateurs
// dans une fonction n'est également pas sécurisée
$query->func()->coalesce($userData);
Les expressions brutes ne sont jamais sécurisées:
$expr = $query->newExpr()->add($userData);
$query->select(['deux' => $expr]);
Il est possible de vous prémunir contre de nombreuses situations à risque en
utilisant les bindings. De la même manière que vous pouvez
lier des valeurs pour les requêtes préparées,
des valeurs peuvent être liées aux requêtes en utilisant la méthode
Cake\Database\Query::bind()
.
L’exemple ci-dessous est une alternative sûre à la version donnée plus haut, qui était vulnérable à une injection SQL:
$query
->where([
'MATCH (comment) AGAINST (:userData)',
'created < NOW() - :moreUserData'
])
->bind(':userData', $userData, 'string')
->bind(':moreUserData', $moreUserData, 'datetime');
Note
Contrairement à Cake\Database\StatementInterface::bindValue()
,
Query::bind()
a besoin que vous passiez les « placeholders » en incluant
les deux-points (:
) !
Si votre application a besoin de recourir à des requêtes plus complexes, vous pouvez en écrire de nombreuses manières avec le constructeur de requêtes ORM.
Les unions sont créées en composant une ou plusieurs requêtes select ensemble:
$inReview = $articles->find()
->where(['a_relire' => true]);
$unpublished = $articles->find()
->where(['published' => false]);
$unpublished->union($inReview);
Vous pouvez créer les requêtes UNION ALL
en utilisant la méthode
unionAll()
:
$inReview = $articles->find()
->where(['a_relire' => true]);
$unpublished = $articles->find()
->where(['published' => false]);
$unpublished->unionAll($inReview);
Les sous-requêtes vous permettent de combiner des requêtes et de construire des conditions et des résultats basés sur les résultats d’autres requêtes:
$matchingComment = $articles->getAssociation('Comments')->find()
->select(['article_id'])
->distinct()
->where(['comment LIKE' => '%CakePHP%']);
$query = $articles->find()
->where(['id IN' => $matchingComment]);
Les sous-requêtes sont acceptées partout où une expression de requête peut être
utilisée. Par exemple, dans les méthodes select()
et join()
. L’exemple
ci-dessus utilise un objet Orm\Query
standard qui générera des alias. Ces
alias peuvent complexifier le référencement des résultats dans la requête
englobante. Depuis 4.2.0 vous pouvez utiliser Table::subquery()
pour créer
une instance de requête spécialisée qui ne générera pas d’alias:
$comments = $articles->getAssociation('Comments')->getTarget();
$matchingComment = $comments->subquery()
->select(['article_id'])
->distinct()
->where(['comment LIKE' => '%CakePHP%']);
$query = $articles->find()
->where(['id IN' => $matchingComment]);
Nouveau dans la version 4.2.0: Table::subquery()
et Query::subquery()
ont été ajoutées.
La plupart des fournisseurs de bases de données relationnelles supportent la
pose de verrous pendant les opérations SELECT. Pour cela, vous pouvez utiliser
la méthode epilog()
:
// Dans MySQL
$query->epilog('FOR UPDATE');
La méthode epilog()
vous permet d’ajouter du SQL brut à la fin des requêtes.
Vous ne devez jamais insérer des données utilisateur dans epilog()
.
Les fonctions de fenêtrage (window functions) vous permettent d’effectuer des calculs en utilisant des lignes relatives à la ligne courante. Elles sont couramment utilisées pour calculer des totaux ou des positions dans des sous-ensembles de lignes dans une requête. Par exemple si vous nous voulons connaître la date du plus ancien et du plus récent commentaires sur chaque article, nous pouvons utiliser des fonctions de fenêtrage:
$query = $articles->find();
$query->select([
'Articles.id',
'Articles.titre',
'Articles.user_id'
'premier_commentaire' => $query->func()
->min('Comments.created')
->partition('Comments.article_id'),
'dernier_commentaire' => $query->func()
->max('Comments.created')
->partition('Comments.article_id'),
])
->innerJoinWith('Comments');
Ce code générerait du SQL ressemblant à:
SELECT
Articles.id,
Articles.titre,
Articles.user_id
MIN(Comments.created) OVER (PARTITION BY Comments.article_id) AS premier_commentaire,
MAX(Comments.created) OVER (PARTITION BY Comments.article_id) AS dernier_commentaire,
FROM articles AS Articles
INNER JOIN comments AS Comments
Les expressions de fenêtrage peuvent s’appliquer à la plupart des fonctions
d’agrégation. Toutes les fonctions d’agrégation pour lesquelle cake propose une
abstraction avec un wrapper dans FunctionsBuilder
retourneront une
AggregateExpression
qui vous permet d’y rattacher une expression de
fenêtrage. Vous pouvez créer des fonctions d’agrégation personnalisées avec
FunctionsBuilder::aggregate()
.
Ce sont les fonctionnalités de fenêtrage les plus communément supportées. La
plupart des fonctionnalités sont fournies par AggregateExpresion
, mais
assurez-vous de suivre la documentation de votre base de données sur
leur utilisation et leurs restrictions.
order($fields)
Trie les groupes agrégés de la même façon que ORDER BY.
partition($expressions)
Ajoute une ou plusieurs partitions à la fenêtre
à partir des noms de colonnes.
rows($start, $end)
Définit un offset de lignes qui précèdent et/ou suivent
la ligne en cours et qui devraient être incluses dans la fonction
d’agrégation.
range($start, $end)
Définit une plage de valeurs de lignes qui précèdent
et/ou suivent la ligne en cours et qui devraient être incluses dans la
fonction d’agrégation. Cela évalue les valeurs sur la base du champ de
order()
.
Si vous avez besoin d’utiliser une même expression de fenêtrage à plusieurs
reprises, vous pouvez créer des fenêtres nommées en utilisant la méthode
window()
:
$query = $articles->find();
// Définit une fenêtre nommée
$query->window('article_concerne', function ($window, $query) {
$window->partition('Comments.article_id');
return $window;
});
$query->select([
'Articles.id',
'Articles.titre',
'Articles.user_id'
'premier_commentaire' => $query->func()
->min('Comments.created')
->over('article_concerne'),
'dernier_commentaire' => $query->func()
->max('Comments.created')
->over('article_concerne'),
]);
Nouveau dans la version 4.1.0: Le support des fonctions de fenêtrage a été ajouté.
Les Common Table Expressions ou CTE sont utiles pour construire des requêtes dans lesquelles vous devez rassembler les résultats de plusieurs petites requêtes. Ils peuvent avoir la même utilité que les vues de base de données ou que les résultats de sous-requêtes. Les Common Table Expression se différencient des tables dérivées et des vues sur plusieurs points:
Contrairement aux vues, vous n’avez pas besoin de maintenir un schéma pour les CTE. Le schéma est basé implicitement sur le result set de l’expression.
Vous pouvez faire plusieurs références aux résultats d’une CTE sans dégradation de performance, contrairement aux jointures de sous-requêtes.
À titre d’exemple, récupérons une liste de clients et le nombre de commandes qu’ils ont passées. En SQL nous utiliserions:
WITH commandes_par_client AS (
SELECT COUNT(*) AS nb_commandes, client_id FROM commandes GROUP BY client_id
)
SELECT nom, commandes_par_client.nb_commandes
FROM clients
INNER JOIN commandes_par_client ON commandes_par_client.client_id = clients.id
Pour construire cette requête avec le constructeur de requêtes, nous utiliserions:
// Démarrer la requête finale
$query = $this->Clients->find();
// Attacher une common table expression
$query->with(function ($cte) {
// Créer une sous-requête à utiliser dans notre CTE
$q = $this->Commandes->subquery();
$q->select([
'nb_commandes' => $q->func()->count('*'),
'client_id'
])
->group('client_id');
// Attacher la nouvelle requête à notre CTE
return $cte
->name('commandes_par_client')
->query($q);
});
// Terminer la construction de la requête finale
$query->select([
'name',
'nb_commandes' => 'commandes_par_client.nb_commandes',
])
->join([
// Définir la jointure avec notre CTE
'commandes_par_client' => [
'table' => 'commandes_par_client',
'conditions' => 'commandes_par_client.client_id = Clients.id'
]
]);
Nouveau dans la version 4.1.0: Ajout des Common table expressions.
Bien que le constructeur de requêtes permette la construction de la plupart des requêtes, les requêtes très complexes peuvent être fastidieuses et compliquées à construire. Vous pourrez être tenté d’exécuter votre propre code SQL directement.
L’exécution directe de code SQL vous permet d’affiner la requête qui sera lancée.
Cependant, cela vous empêchera d’utiliser contain
ou toute autre
fonctionnalité de plus haut niveau de l’ORM.
Comment les Requêtes sont Évaluées Lazily¶
Les objets Query sont évalués paresseusement (lazily). Cela signifie qu’une requête n’est pas exécutée jusqu’à ce qu’une des actions suivantes soit lancée:
La requête est itérée avec
foreach()
.La méthode
execute()
est appelée. Elle retourne l’objet d’instruction (statement) sous-jacent, et est faite pour être utilisée avec les requêtes insert/update/delete.La méthode
first()
est appelée. Elle retourne le premier résultat correspondant à l’instructionSELECT
(elle ajouteLIMIT 1
à la requête).La méthode
all()
est appelée. Elle retourne l’ensemble des résultats et peut seulement être utilisée avec les instructionsSELECT
.La méthode
toList()
outoArray()
est appelée.Tant qu’aucune de ces conditions n’est rencontrée, la requête peut être modifiée sans qu’aucun nouveau code SQL ne soit envoyé à la base de données. Cela signifie aussi que si une Query n’a pas été évaluée, aucun SQL ne sera envoyé à la base de données. Une fois exécutée, la modification suivie de la ré-évaluation de la requête va entraîner l’exécution du SQL supplémentaire. Si une requête est exécutée plusieurs fois d’affilée sans avoir été modifiée entre-temps, elle renvoie la même référence.
Si vous souhaitez jeter un œil sur le SQL que CakePHP génère, vous pouvez activer les logs de requête.