Query Builder¶

Récupérer les Lignes d’une Table¶

use Cake\ORM\TableRegistry;

// Prior to 3.6 use TableRegistry::get('Articles')
$query = TableRegistry::getTableLocator()->get('Articles')->find();

foreach ($query as $article) {
    debug($article->title);
}

Pour les exemples restants, imaginez que $articles est une ORM\Table. Quand vous êtes dans des controllers, vous pouvez utiliser $this->Articles plutôt que $articles.

Presque chaque méthode dans un objet Query va retourner la même requête, cela signifie que les objets Query sont lazy, et ne seront pas exécutés à moins que vous 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 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])
    ->toArray();

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’extractions ou de traversement.

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 toArray() ou n’importe qu’elle méthode héritée de Collection, aura pour résultat l’exécution de la requête et la récupération des résultats.

Récupérez une Ligne Unique d’une Table¶

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);

Récupérer une Liste de Valeurs à Partir d’une Colonne¶

// Utilise la méthode extract() à partir de la libraire collections
// Ceci exécute aussi la requête
$allTitles = $articles->find()->extract('title');

foreach ($allTitles as $title) {
    echo $title;
}

Vous pouvez aussi récupérer une liste de clé-valeur à partir d’un résultat d’une requête:

$list = $articles->find('list');

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.

Les Requêtes sont des Objets Collection¶

Une fois que vous êtes familier avec les méthodes de l’objet Query, il est fortement recommandé que vous consultiez la section Collection pour améliorer vos compétences dans le traversement efficace de 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";
}

Comment les Requêtes sont Évaluées Lazily¶

Les objets Query sont évalués « lazily » (paresseusement). Cela signifie qu’une requête n’est pas exécutée jusqu’à ce qu’une des prochaines actions se fasse:

• La requête est itérée avec foreach().

• La méthode execute() de query est appelée. Elle retourne l’objet d’instruction sous-jacente, et va être utilisée avec les requêtes insert/update/delete.

• La méthode first() de query est appelée. Elle retourne le premier résultat correspondant à l’instruction SELECT (ajoute LIMIT 1 à la requête).

• La méthode all() de query est appelée. Elle retourne l’ensemble de résultats et peut seulement être utilisée avec les instructions SELECT.

• La méthode toArray() de query est appelée.

Jusqu’à ce qu’une de ces conditions soit rencontrée, la requête peut être modifiée avec du SQL supplémentaire envoyé à la base de données. Cela signifie que si une Query n’a pas été évaluée, aucun SQL ne sera jamais envoyé à la base de données. Une fois exécutée, la modification et la ré-évaluation d’une requête va entraîner l’exécution de SQL supplémentaire.

Si vous souhaitez jeter un œil sur le SQL que CakePHP génère, vous pouvez activer les logs de requête de la base de données.

Les sections suivantes vont vous montrer tout ce qu’il faut savoir sur l’utilisation et la combinaison des méthodes de l’objet Query pour construire des requêtes SQL et extraire les données.

Sélectionner Tous les Champs d’une Table¶

Par défaut, une requête va sélectionner tous les champs d’une table sauf si vous appelez la fonction select() vous-même et passez certains champs:

// Sélectionne seulement id et title de la table articles
$articles->find()->select(['id', 'title']);

Si vous souhaitez toujours sélectionner tous les champs d’une table après avoir appelé select($fields), vous pouvez dans cette optique passer l’instance de table à select():

// Sélectionne seulement id et title de la table articles
$query = $articlesTable->find();
$query
    ->select(['slug' => $query->func()->concat(['title' => 'identifier', '-', 'id' => 'identifier'])])
    ->select($articlesTable); // Sélectionne tous les champs de articles

Utiliser les Fonctions SQL¶

L’ORM de CakePHP offre une abstraction pour les fonctions les plus communément utilisées par SQL. Utiliser l’abstraction permet à l’ORM de sélectionner l’intégration spécifique de la fonction pour la plateforme que vous souhaitez. Par exemple, concat est intégré différemment dans MySQL, PostgreSQL et SQL Server. Utiliser l’abstraction permet à votre code d’être portable:

// Résultats dans SELECT COUNT(*) count FROM ...
$query = $articles->find();
$query->select(['count' => $query->func()->count('*')]);

Un certain nombre de fonctions communément utilisées peut être créé avec la méthode func():

• sum() Calcule une somme. Les arguments sont traités comme des valeurs littérales.

• avg() Calcule une moyenne. Les arguments sont traités comme des valeurs littérales.

• min() Calcule le min d’une colonne. Les arguments sont traités comme des valeurs littérales.

• max() Calcule le max d’une colonne. Les arguments sont traités comme des valeurs littérales.

• count() Calcule le count. Les arguments sont traités comme des valeurs littérales.

• concat() Concatène deux valeurs ensemble. Les arguments sont traités comme des paramètres liés, à moins qu’ils ne soient marqués comme littéral.

• coalesce() Regroupe les valeurs. Les arguments sont traités comme des paramètres liés, à moins qu’ils ne soient marqués comme littéral.

• dateDiff() Récupère la différence entre deux dates/times. Les arguments sont traités comme des paramètres liés, à moins qu’ils ne soient marqués comme littéral.

• now() Prend soit “time”, soit “date” comme argument vous permettant de récupérer soit le time courant, soit la date courante.

• extract() Retourne la partie de la date spécifiée de l’expression SQL.

• dateAdd() Ajoute l’unité de temps à l’expression de la date.

• dayOfWeek() Retourne une FunctionExpression représentant un appel à la fonction SQL WEEKDAY.

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’identifaction/littéraux vous permettent de référencer les colonnes ou les autres valeurs littérales de SQL. Les paramètres liés peuvent être utilisés pour ajouter en toute sécurité les données d’utilisateur aux fonctions SQL. Par exemple:

$query = $articles->find()->innerJoinWith('Categories');
$concat = $query->func()->concat([
    'Articles.title' => 'identifier',
    ' - CAT: ',
    'Categories.name' => 'identifier',
    ' - Age: ',
    '(DATEDIFF(NOW(), Articles.created))' => 'literal',
]);
$query->select(['link_title' => $concat]);

En modifiant les arguments avec une valeur de literal, l’ORM va savoir que la clé doit être traitée comme une valeur SQL littérale. En modifiant les arguments avec une valeur d”identifier, l’ORM va savoir que la clé doit être traitée comme un identifieur de champ. 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.

En plus des fonctions ci-dessus, la méthode func() peut être utilisée pour créer toute fonction générique SQL comme year, date_format, convert, etc… Par exemple:

$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;

Vous devriez penser à utiliser le constructeur de fonctions à chaque fois que vous devez mettre des données non fiables dans des fonctions SQL ou des procédures stockées:

// Utilise une procédure stockée
$query = $articles->find();
$lev = $query->func()->levenshtein([$search, 'LOWER(title)' => 'literal']);
$query->where(function ($exp) use ($lev) {
    return $exp->between($lev, 0, $tolerance);
});

// Le SQL généré serait
WHERE levenshtein(:c0, lower(street)) BETWEEN :c1 AND :c2

Regroupements - Group et Having¶

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]);

Instructions Case¶

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()
    ->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 SMALL, MEDIUM, ou LARGE, nous pourrions faire ce qui suit:

$query = $cities->find()
    ->where(function ($exp, $q) {
        return $exp->addCase(
            [
                $q->newExpr()->lt('population', 100000),
                $q->newExpr()->between('population', 100000, 999000),
                $q->newExpr()->gte('population', 999001),
            ],
            ['SMALL',  'MEDIUM', 'LARGE'], # les valeurs correspondantes aux conditions
            ['string', 'string', 'string'] # type de chaque valeur
        );
    });
# WHERE CASE
#   WHEN population < 100000 THEN 'SMALL'
#   WHEN population BETWEEN 100000 AND 999000 THEN 'MEDIUM'
#   WHEN population >= 999001 THEN 'LARGE'
#   END

A chaque fois qu’il y a moins de conditions qu’il n’y a de valeurs, addCase va automatiquement produire une instruction if .. then .. else:

$query = $cities->find()
    ->where(function ($exp, $q) {
        return $exp->addCase(
            [
                $q->newExpr()->eq('population', 0),
            ],
            ['DESERTED', 'INHABITED'], # valeurs correspondantes aux conditions
            ['string', 'string'] # type de chaque valeur
        );
    });
# WHERE CASE
#   WHEN population = 0 THEN 'DESERTED' ELSE 'INHABITED' END

Récupérer des Tableaux plutôt que des Entities¶

Bien que les ensembles de résultats en objet de l’ORM soient puissants, créer des entities n’est parfois pas nécessaire. Par exemple, quand vous accédez aux données agrégées, la construction d’une Entity peut ne pas être utile. 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->hydrate(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' => 'First Article', 'body' => 'Article 1 body' ...],
    ['id' => 2, 'title' => 'Second Article', 'body' => 'Article 2 body' ...],
    ...
]

Créer automatiquement des Clauses IN¶

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[]']);

// Or include IN to automatically cast to an array.
$query = $articles->find()
    ->where(['id IN' => $ids]);

Ce qui est au-dessus va automatiquement créer id IN (...) plutôt que id = ?. Ceci 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 type. Ceci fonctionne aussi avec les types complexes. Par exemple, vous pourriez prendre une liste d’objets DateTime en utilisant:

$query = $articles->find()
    ->where(['post_date' => $dates], ['post_date' => 'date[]']);

Création Automatique de IS NULL¶

Quand une valeur d’une condition s’attend à être null ou tout autre valeur, vous pouvez utiliser l’opérateur IS pour créer automatiquement la bonne expression:

$query = $categories->find()
    ->where(['parent_id IS' => $parentId]);

Ce qui précède va créer parent_id` = :c1 ou parent_id IS NULL selon le type de $parentId.

Création Automatique de IS NOT NULL¶

Quand une valeur d’une condition s’attend à être null ou tout autre valeur, 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 qui précède va créer parent_id` != :c1 ou parent_id IS NOT NULL selon le type de $parentId.

Raw Expressions¶

Quand vous ne pouvez pas construire le code SQL, vous devez utiliser le constructeur de requête, vous pouvez utiliser les objets Expression pour ajouter des extraits de code SQL à vos requêtes:

$query = $articles->find();
$expr = $query->newExpr()->add('1 + 1');
$query->select(['two' => $expr]);

Les objets Expression peuvent être utilisés avec les méthodes du constructeur de requête comme where(), limit(), group(), select() et d’autres méthodes.

Retourner le Nombre Total des Enregistrements¶

En utilisant un objet query unique, il est possible d’obtenir le nombre total de lignes trouvées pour un ensemble de conditions:

$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 savoir la taille totale de l’ensemble des résultats en avance, sans avoir à construire un autre objet Query. De cette façon, tous les résultats formatés et les routines map-reduce sont ignorées quand vous utilisez la méthode count().

De plus, il est possible de retourner le nombre total pour une requête contenant des clauses group by sans avoir à réécrire la requête en aucune façon. Par exemple, considérons la requête qui permet de récupérer les ids d’article et leur nombre 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();

Parfois, vous voulez fournir une méthode alternative pour compter le nombre total d’enregistrements d’une requête. Un cas d’utilisation courante pour ceci est pour fournir une valeur mise en cache ou un nombre estimé total de lignes, ou pour 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 appelle la méthode count, elle va recevoir le nombre de lignes estimées écrit en dur.

Mettre en Cache les Résultats Chargés¶

Quand vous récupérez les 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 l’ensemble des résultats de la requête. Si un seul argument est fourni à cache() alors la configuration du cache “default” va être utilisée. Vous pouvez contrôler la configuration du 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é 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ènement.

Quand les résultats pour une requête mise en cache sont récupérés, ce qui suit va arriver:

1. L’évènement Model.beforeFind est déclenché.

2. Si la requête a des ensembles de résultats, ceux-ci vont être retournés.

3. La clé du cache va être déterminée et les données du cache vont être lues. Si les données du cache sont vides, ces résultats vont être retournés.

4. Si le cache n’est pas présent, la requête sera exécutée et un nouveau ResultSet sera créé. Ce ResultSet sera écrit dans le cache et sera retourné.

Passer des Conditions à Contain¶

Avec l’utilisation de contain(), vous pouvez restreindre les données retournées par les associations et les filtrer par conditions:

// Dans un controller ou une méthode de table.

$query = $articles->find()->contain('Comments', function ($q) {
   return $q
        ->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
});

Cela fonctionne aussi pour la pagination au niveau du Controller:

$this->paginate['contain'] = [
    'Comments' => function (\Cake\ORM\Query $query) {
        return $query->select(['body', 'author_id'])
        ->where(['Comments.approved' => true]);
    }
];

Il est aussi possible de restreindre les associations imbriquées profondément en utilisant la notation par point:

$query = $articles->find()->contain([
    'Comments',
    'Authors.Profiles' => function ($q) {
        return $q->where(['Profiles.is_published' => true]);
    }
]);

Dans l’exemple ci-dessus, 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 méthodes de finder personnalisées 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');
});

Si vous devez prendre le contrôle total d’une requête qui est générée, vous pouvez appeler contain() pour ne pas ajouter les contraintes foreignKey à la requête générée. Dans ce cas, vous devez utiliser un tableau en passant foreignKey et queryBuilder:

$query = $articles->find()->contain([
    'Authors' => [
        'foreignKey' => false,
        'queryBuilder' => function ($q) {
            return $q->where(...); // Full conditions for filtering
        }
    ]
]);

Si vous avez limité les champs que vous chargez avec select() mais que vous souhaitez aussi charger les champs enlevés des associations avec contain, vous pouvez passer l’objet association à select():

// Sélectionne id & title de articles, mais tous les champs enlevés pour Users.
$query = $articles->find()
    ->select(['id', 'title'])
    ->select($articles->Users)
    ->contain(['Users']);

D’une autre façon, si vous pouvez faire des associations multiples, vous pouvez utiliser enableAutoFields():

// Sélectionne id & title de articles, mais tous les champs enlevés de
// Users, Comments et Tags.
$query->select(['id', 'title'])
    ->contain(['Comments', 'Tags'])
    ->enableAutoFields(true) // Avant 3.4.0 utilisez autoFields(true)
    ->contain(['Users' => function($q) {
        return $q->autoFields(true);
    }]);

Ordonner les Associations Contain¶

Quand vous chargez des associations HasMany et BelongsToMany, vous pouvez utiliser l’option sort pour ordonner les données dans ces associations:

$query->contain([
    'Comments' => [
        'sort' => ['Comments.created' => 'DESC']
    ]
]);

Filtering by Associated Data¶

Un cas de requête couramment fait avec les associations est de trouver les enregistrements qui “matchent” les données associées spécifiques. Par exemple si vous avez “Articles belongsToMany Tags”, vous aurez probablement envie de trouver les Articles qui ont le tag CakePHP. C’est extrêmement simple à faire avec l’ORM de CakePHP:

// Dans un controller ou table de méthode.

$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 avec les articles récemment publiés en utilisant ce qui suit:

$query = $authors->find();
$query->matching('Articles', function ($q) {
    return $q->where(['Articles.created >=' => new DateTime('-10 days')]);
});

Filtrer des associations imbriquées est étonnamment facile, et la syntaxe doit déjà vous être familière:

// Dans un controller ou une table de méthode.
$query = $products->find()->matching(
    'Shops.Cities.Countries', function ($q) {
        return $q->where(['Countries.name' => 'Japan']);
    }
);

// Récupère les articles uniques qui étaient commentés par 'markstory'
// en utilisant la variable passée
// Les chemins avec points doivent être utilisés plutôt que les appels
// imbriqués de matching()
$username = 'markstory';
$query = $articles->find()->matching('Comments.Users', function ($q) use ($username) {
    return $q->where(['username' => $username]);
});

Les données des associations qui sont “matchés” (appairés) 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 à recevoir à la fois la propriété _matchingData et la propriété standard d’association dans vos résultats.

Utiliser innerJoinWith¶

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 vouliez utiliser matching() mais que vous n’êtes pas intéressé par le chargement des champs dans un ensemble de résultats. 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' => 'Japan']);
    }
);

De même, la seule différence est qu’aucune colonne supplémentaire ne sera ajoutée à l’ensemble de résultats et aucune propriété _matchingData ne sera définie.

Utiliser notMatching¶

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' => 'boring']);
    });

L’exemple ci-dessus va trouver tous les articles qui n’étaient pas taggés avec le mot boring. Vous pouvez aussi utiliser cette méthode avec les associations HasMany. Vous pouvez, par exemple, trouver tous les auteurs qui n’ont 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 les associations profondes. 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 avec aucun commentaire satisfont aussi la condition du dessus, vous pouvez combiner matching() et notMatching() dans la même requête. L’exemple suivant va trouver 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');

Gardez à l’esprit que le contraire de la fonction matching(), notMatching() ne va pas ajouter toutes les données à la propriété _matchingData dans les résultats.

Utiliser leftJoinWith¶

Dans certaines situations, vous aurez à calculer un résultat selon une association, sans avoir à charger tous les enregistrements. Par exemple, si vous voulez charger le nombre total de commentaires qu’un article a, ainsi que toutes les 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); // Avant 3.4.0 utilisez autoFields(true)

Le résultat de la requête ci-dessus va contenir 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 profondes. C’est utile par exemple pour rapporter le nombre d’articles taggés par l’auteur 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' => 'awesome']);
    })
    ->group(['Authors.id'])
    ->enableAutoFields(true); // Avant 3.4.0 utilisez autoFields(true)

Cette fonction ne va charger aucune colonne des associations spécifiées dans l’ensemble de résultats.

Ajouter des Jointures¶

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()
    ->hydrate(false)
    ->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()
    ->hydrate(false)
    ->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()
    ->hydrate(false)
    ->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 d’un tableau basé sur les conditions, 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
$query = $articles->find();
$query->leftJoin(
    ['Authors' => 'authors'],
    ['Authors.id = Articles.author_id']
);

// Jointure avec un alias, tableau de conditions, et de types
$query = $articles->find();
$query->innerJoin(
    ['Authors' => 'authors'],
    [
        'Authors.promoted' => true,
        'Authors.created' => new DateTime('-5 days'),
        'Authors.id = Articles.author_id'
    ],
    ['Authors.promoted' => 'boolean', 'Authors.created' => 'datetime']
);

Notez que si vous définissez l’option quoteIdentifiers à true quand vous configurez votre Connection, les conditions mettant en relation deux champs de tables différentes 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 sont bien quotés dans la requête générée, permettant d’éviter des erreurs avec certains drivers (PostgreSQL notamment).

Lier les Valeurs (Binding)¶

Il est possible de protéger vos requêtes en utilisant le « binding ». De la même manière que vous pouvez « binder » des valeurs pour les requêtes préparées, des valeurs peuvent être « bindées » aux requêtes en utilisant la méthode Cake\Database\Query::bind().

L’exemple ci-dessous est une alternative sûre par rapport à la version donnée plus haut, qui serait vulnérable à une injection SQL:

$query
    ->where([
        'MATCH (comment) AGAINST (:userData)',
        'created < NOW() - :moreUserData'
    ])
    ->bind(':userData', $userData, 'string')
    ->bind(':moreUserData', $moreUserData, 'datetime');

Unions¶

Les unions sont créées en composant une ou plusieurs requêtes select ensemble:

$inReview = $articles->find()
    ->where(['need_review' => 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(['need_review' => true]);

$unpublished = $articles->find()
    ->where(['published' => false]);

$unpublished->unionAll($inReview);

Sous-Requêtes¶

Les sous-requêtes sont une fonctionnalité puissante dans les bases de données relationnelles et les construire dans CakePHP est assez intuitif. En composant les requêtes ensemble, vous pouvez faire des sous-requêtes:

$matchingComment = $articles->association('Comments')->find()
    ->select(['article_id'])
    ->distinct()
    ->where(['comment LIKE' => '%CakePHP%']);

$query = $articles->find()
    ->where(['id' => $matchingComment]);

Les sous-requêtes sont acceptées partout où une expression query peut être utilisée. Par exemple, dans les méthodes select() et join().

Adding Locking Statements¶

Most relational database vendors support taking out locks when doing select operations. You can use the epilog() method for this:

// In MySQL
$query->epilog('FOR UPDATE');

The epilog() method allows you to append raw SQL to the end of queries. You should never put raw user data into epilog().

Exécuter des Requêtes Complexes¶

Bien que le constructeur de requêtes facilite la construction de la plupart des requêtes, les requêtes très complexes peuvent être fastidieuses et compliquées à construire. Vous voudrez surement vous référer à l’exécution directe du SQL souhaité.

Exécuter directement le SQL vous permet d’affiner la requête qui sera utilisée. Cependant, cela vous empêchera d’utiliser contain ou toute autre fonctionnalité de plus haut niveau de l’ORM.