Utiliser les vues SQL dans CakePHP

Une vue SQL est une sorte de table virtuelle construite à partir d’une requête SQL. Nous allons nous en servir pour présenter une liste globale des derniers contenus ajoutés dans plusieurs tables de la base de données.

Nous prendrons l’exemple d’une application contenant des articles, des vidéos et des fichiers audio. Ces trois entités ont chacune leur table dans la base, leur contrôleur et leur modèle. Comment faire pour récupérer facilement une liste des derniers contenus saisis dans ces trois tables classés par date de création décroissante et pouvant être paginée ? Un premier réflexe serait de récupérer les trois listes avec find(‘all’), puis de les réunir dans un seul tableau, puis de faire un tri en comparant leurs dates de créations… c’est compliqué, et Cake ne saura pas paginer cela.

Nous allons donc créer une requête SQL qui va faire ce travail de collecte et de tri des derniers contenus enregistrés sur les trois tables, et stocker le résultat de cette requête sous forme de vue SQL. Ensuite nous créerons un modèle CakePHP capable d’interroger cette « table virtuelle » comme n’importe quelle autre table, puis le contrôleur qui va appeler le modèle.

1. Quelles données collecter ?

Plusieurs possibilités sont à considérer dans le choix des colonnes à collecter : soit nous stockons directement les informations qui seront présentées telles quelles, comme un titre et une description ; soit nous stockons uniquement l’identifiant d’un enregistrement et le nom du modèle, et nous irons interroger chaque modèle pour récupérer les données nécessaires le moment venu.

Comme nos contenus sont très différents les uns des autres (une vidéo et un fichier audio ont une durée en secondes mais pas un article, une vidéo contient le code d’un lecteur embarqué alors qu’un fichier audio contient un lien vers un fichier mp3, etc.), notre présentation visuelle n’a pas besoin des mêmes informations pour chaque type de contenu. Nous choisissons de ne collecter que le strict minimum à savoir l’identifiant de l’enregistrement, le nom du modèle qui le gère, et la date de publication. Nous utiliserons ensuite ces informations pour aller chercher les autres champs directement dans les tables concernées au moment où nous en aurons besoin.

2. Création de la vue SQL

Voyons le code SQL à exécuter pour créer la vue proprement dite :

CREATE VIEW `latest_entries` AS 
	SELECT `articles`.`id` AS `id`, TRIM('Article') AS `model`, `articles`.`published` AS `published` 
	FROM `articles` WHERE (`articles`.`online` = 1) 
UNION 
	SELECT `videos`.`id` AS `id`, TRIM('Video') AS `model`, `videos`.`published` AS `published` 
	FROM `videos` WHERE (`videos`.`online` = 1) 
UNION 
	SELECT `audios`.`id` AS `id`, TRIM('Audio') AS `model`, `audios`.`published` AS `published` 
	FROM `audios` WHERE (`audios`.`online` = 0);

Nous devons utiliser une petite astuce pour le nom du modèle, car MySQL ne permet pas de donner une valeur simple à cet endroit, il n’accepte qu’un nom de colonne existant ou le résultat d’une fonction SQL, ici TRIM qui est censée enlever les espaces blancs autour d’une chaine de caractères.

Nous obtenons une table virtuelle contenant trois colonnes : id, model et published.

3. Utilisation dans CakePHP

Nous pouvons maintenant créer un modèle pour communiquer avec cette nouvelle « table » :

class LatestEntry extends AppModel {
 
}

Il peut rester vide, Cake s’occupe du reste !

Voyons le contrôleur :

class LatestEntriesController extends AppController {
	var $uses = array('LatestEntry', 'Article', 'Video', 'Audio');
 
	var $paginate = array('order'=>'LatestEntry.published DESC');
 
	function index() {
		$latest_entries = $this->paginate();
 
		$data = array();
 
		if (!empty($latest_entries)) {
			foreach ($latest_entries as $row) {
				$data[] = $this->{$row['LatestEntry']['model']}->read(null, $row['LatestEntry']['id']);
			}
		}
 
		$this->set('data', $data);
	}
}

La méthode index() commence par demander la liste paginée, qui ne va contenir que les id et les noms de modèle. Nous bouclons ensuite sur cette liste et appelons la méthode read() de chaque modèle pour constituer le tableau complet des données à envoyer à la vue views/latest_entries/index.ctp :

<h1>Derniers ajouts :</h1>
<?php foreach ($data as $row) {
	$model = key($row);
	echo $this->element('last_entry_'.strtolower($model), array('data'=>$row));
} ?>

Nous bouclons sur la liste des résultats et en fonction du nom du modèle, appellons un element, dans lequel la variable $data contiendra toutes les données nécessaires à l’affichage.

L’utilisation des vues SQL est donc toute indiquée dans la collecte de données issues de plusieurs tables (journal d’évènements par exemple) mais également dans le cas de requêtes compliquées à construire dans Cake avec les find() habituels.

Pierre-Emmanuel Fringant