Quelques astuces pour la génération des requêtes SQL

Nous allons voir comment contrôler finement les requêtes SQL préparées par CakePHP sans nous écarter des méthodes habituelles, c’est à dire le passage d’un tableau d’options à la méthode Model::find();.

Exclure des enregistrements

Si le paramètre d’exclusion est unique, le signe différent ‘!=‘ doit se trouver dans la partie ‘champ’ de la condition et non dans la partie ‘valeur’ :

$conditions = array('champ !=' => $valeur);

Exemple : le champ disabled ne doit pas valoir 0 :

$conditions = array('disabled !=' => 0);

Si l’exclusion se fait sur une liste de valeurs, alors on peut ajouter une clé 'NOT' dans notre tableau de conditions :

$conditions = array('NOT' => array('champ' => $valeurs));

Exemple : le champ id ne doit pas valoir 3, 9 ou 14 :

$conditions = array('NOT' => array('id' => array(3, 9, 14)));

Conserver l’ordre dans le cas d’un SELECT … WHERE … IN()

Lorsque l’on exécute une requête SQL qui contient l’instruction WHERE 'champ' IN(valeur1, valeur2, etc.) sans avoir défini de clause ORDER BY, les résultats retournés sont classés par clé primaire ascendante.

Exemple : récupérer la liste des enregistrements dont l’id est 2, 15, 9, 7 ou 3 :

$conditions = array('id' => array(2, 15, 9, 7, 3));

La requête renvoie les enregistrements classés par id croissant.

Or il est parfois nécessaire de récupérer ces résultats dans l’ordre exact des valeurs passées à IN(). On utilise dans ce cas la clause ORDER BY suivante : ORDER BY FIELD('champ', valeur1, valeur2, etc.). Ce qui se fait comme suit en CakePHP :

$valeurs = array(2, 15, 9, 7, 3);
 
$conditions = array('id' => $valeurs);
$order = 'FIELD(id, ' . join(', ', $valeurs) . ')';
 
$data = $this->Model->find('all', compact('conditions', 'order'));

Cette fois, la requête renvoie les enregistrements classés dans le même ordre que le tableau de valeurs.

Inclure une clause HAVING

L’instruction HAVING permet de faire des restrictions sur les agrégats, il est donc utilisé avec l’instruction GROUP BY. Il suffit de passer l’instruction HAVING à la clé ‘group‘ de notre tableau d’options.

Exemple : nous recherchons les catégories d’articles qui ont au moins un article, sachant que nous avons une association Category hasMany Article :

class CategoriesController extends AppController
{
	function having()
	{
		$fields = array('Category.id', 'Category.name');
		$group = array('Category.id HAVING COUNT(Article.id) > 0');
 
		$data = $this->Category->Article->find('all', compact('fields', 'group'));
 
		$this->set('data', $data);
	}
}

Attention à la subtilité de ce cas : nous appelons non pas la méthode find() du modèle Category mais la méthode find() du modèle Article (grâce au chainage entre les modèles découlant de l’association Category hasMany Article) car sinon CakePHP ne va pas chercher à associer les deux modèles nécessaires à l’exécution de la requête (et ce quel que soit le niveau de récursivité).

Pierre-Emmanuel Fringant

Articles connexes

Commentaires

N’hésitez pas à poster vos astuces SQL dans les commentaires !

[...] Quelques astuces pour la génération des requêtes SQL [...]

$conditions = array( ‘champ’ => $valeur, ‘champDate < DATE_ADD(now(), Interval -1 day)’ );

une condition sur les dates de plus d’un jour je l’ai testé sur des DATETIME et des TIMESTAMP, ça marche bien

d’autres exemples sur les critères de dates : http://www.fbollon.net/node/151

Juste une petite question annexe, comment peut on récupéré le nombre de requête effectuer par CakePHP et le temps de génération d’une page.

Je vois que dans le debug on a ses informations, il est surement possible de les récupéré mais comment?

comment faire pour voir les requêtes SQL exécutées par cakePHP ? Je les vois bien dans le debug en bas de page, mais j’aimerais les récupérer dans mon code. Comment faire ? Merci.

Participez

Pour insérer une portion de code, utilisez <pre lang="php">...</pre>