Prado, SQLMap, TRepeater et TPager: requêtes SQL personnalisées avec pagination
En utilisant les classes métiers standards de Prado, je n’ai pas trouvé moyen d’exploiter correctement une relation de type “plusieurs à plusieurs” (many to many) avec la méthode “withXXXX($critere, $value)”. Mon problème était simple: la méthode “withXXXX()” utilise une sous-requête pour récupérer les éléments dépendants d’une relation à la place d’un “INNER JOIN”. Il m’est apparu impossible de récupérer seulement les éléments ayants une relation existante.
Pour illustrer ce cas, prenons mes 2 entités:
- VideoRecord
- CategorieRecord
Une vidéo peut être associée à plusieurs catégories, et une catégorie peut être associées à plusieurs vidéos. (many to many)
Si je désire récupérer toutes les vidéos appartenant à la catégorie avec l’id 2, je vais utiliser cette méthode:
VideoRecord::finder()->withCategories('id = ?', 2)->findAll()
Cette méthode va me retourner tous les objets vidéos, avec une valeur null sur la propriété category lorsque cette dernière ne correspond pas à l’id 2.
Afin de palier à cette problématique tout en optimisant mes requêtes SQL, j’ai découvert les SQLMap avec Prado, qui permettent de gérer des requêtes SQL entièrement personnalisées. L’unique difficulté réside dans la gestion de la pagination pour les requêtes stockées dans les SQLMaps en utilisant des contrôles de type TRepeater et TPager.
La marche à suivre ci-après résume la création des SQLMaps et l’adaptation d’un contrôle contenant un TRepeater et un TPager avec les entités décrites précédemment (VideoRecord et CategorieRecord).
- Dans le fichier protected/application.xml, ajouter entre <modules> et </modules>:
<module id="sqlmap" class="System.Data.SqlMap.TSqlMapConfig" EnableCache="false"(pensez à modifier la propriété ConnectionID en fonction de votre configuration)
ConfigFile="Application.database.sqlmap" ConnectionID="dbVod" />
- Créer un fichier protected/database/sqlmap.xml avec le contenu suivant:
<?xml version="1.0" encoding="UTF-8" ?>
<sqlMapConfig>
<sqlMap resource="maps/VideoRecord.xml" />
</sqlMapConfig> - Créer un fichier protected/database/maps/VideoRecord.xml avec le contenu suivant:
<sqlMapConfig>
<select id="SelectVideosByCategoryAndStatus" resultClass="VideoRecord"
parameterClass="SelectVideosByCategoryAndStatus_Param_Item">
select v.*
from video as v
inner join categorie_video as c on v.id = c.id_video
where c.id_categorie = #idCategorie#
and v.id_status = #idStatus#
order by #orderBy# #order#
</select>
<select id="SelectVideosByCategoryAndStatus_Pager" resultClass="VideoRecord"
parameterClass="SelectVideosByCategoryAndStatus_Param_Item">
select v.*
from video as v
inner join categorie_video as c on v.id = c.id_video
where c.id_categorie = #idCategorie#
and v.id_status = #idStatus#
order by #orderBy# #order#
limit #limit# offset #offset#
</select>
<select id="SelectVideosByCategoryAndStatus_Counter" resultClass="int"
parameterClass="SelectVideosByCategoryAndStatus_Param_Item">
select count(v.id)
from video as v
inner join categorie_video as c on v.id = c.id_video
where c.id_categorie = #idCategorie#
and v.id_status = #idStatus#
</select>
</sqlMapConfig>
Notes: 3 requêtes différentes sont utilisées, 1 pour récupérer tous les résultats, 1 pour récupérer une “page” de résultats et 1 pour déterminer le nb. total de résultats. - Editer le fichier protected/database/VideoRecord.php avec le contenu suivant:
(Note: la classe SelectVideosByCategoryAndStatus_Param_Item représente les paramètres passés à la requête SQL. ATTENTION: si cette dernière n’est pas reconnue, il est nécessaire de la déclarer directement dans le contrôle qui l’utilise (par exemple au début du fichier /pages/controls/CategoriesListVideo.php).)<?php class VideoRecord extends TActiveRecord { const TABLE=‘video’; public $id; public $title; public $description; public $date_added; public $duration_sec; public $views; public $id_publisher; public $id_status; public $status; public $publisher; public $files=array(); public $categories=array(); public $tags=array(); public $videos=array(); public static $RELATIONS=array ( ’status’ => array(self::BELONGS_TO, ‘VideoStatusRecord’), ‘publisher’ => array(self::BELONGS_TO, ‘UserRecord’), ‘files’ => array(self::HAS_MANY, ‘VideoFileRecord’, ‘id_video’), ‘categories’ => array(self::MANY_TO_MANY, ‘CategorieRecord’, ‘categorie_video’), ‘tags’ => array(self::MANY_TO_MANY, ‘TagRecord’, ‘tag_video’) ); public static function finder($className=__CLASS__) { return parent::finder($className); } } /* sqlMap classes, see ./maps/VideoRecord.xml */ class SelectVideosByCategoryAndStatus_Param_Item { public $idCategorie; public $idStatus; public $orderBy = ‘views’; public $order = ‘asc’; public $offset = 0; public $limit = 0; } ?> - Le modèle du contrôle dans le fichier /pages/controls/CategoriesListVideo.tpl contient:
<ul class="videoList">
<com:TRepeater ID="rpVideos"
ItemRenderer="Application.pages.videos.CategoriesListVideoRenderer"
AllowPaging="true"
AllowCustomPaging="true"
/>
</ul>
<com:TPager ControlToPaginate="rpVideos" OnPageIndexChanged="pageChanged" /> - Le code behind du contrôle dans le fichier /pages/controls/CategoriesListVideo.php contient:
(Note: c’est dans ce contrôle que la gestion de la pagination est implémentée.)<?php class CategoriesListVideo extends TTemplateControl { protected $ID_Category = 0; protected $sqlmap; protected $sqlmap_param; protected $PageSize = 30; protected $NbVideos = 0; public function onInit($param) { parent::onInit($param); $this->ID_Category = (int)$this->Request[‘id’]; $this->sqlmap = $this->Application->Modules[’sqlmap’]->Client; $this->sqlmap_param = new SelectVideosByCategoryAndStatus_Param_Item; $this->sqlmap_param->idCategorie = $this->ID_Category; $this->sqlmap_param->idStatus = 2; $this->NbVideos = $this->sqlmap->queryForObject(“SelectVideosByCategoryAndStatus_Counter”, $this->sqlmap_param); $this->rpVideos->PageSize=$this->PageSize; $this->rpVideos->VirtualItemCount = $this->NbVideos; $this->populateData(); } public function pageChanged($sender,$param) { $this->rpVideos->CurrentPageIndex=$param->NewPageIndex; $this->populateData(); } protected function populateData() { $offset=$this->rpVideos->CurrentPageIndex*$this->rpVideos->PageSize; $limit=$this->rpVideos->PageSize; if($offset+$limit > $this->rpVideos->VirtualItemCount) $limit=$this->rpVideos->VirtualItemCount-$offset; $this->rpVideos->DataSource=$this->getVideos($offset,$limit); $this->rpVideos->DataBind(); } protected function getVideos($offset, $limit) { $this->sqlmap_param->limit=$limit; $this->sqlmap_param->offset=$offset; if (($offset > 0) && ($limit > 0)) { $query_name = ‘SelectVideosByCategoryAndStatus_Pager’; } else { $query_name = ‘SelectVideosByCategoryAndStatus’; } return $this->sqlmap->queryForList($query_name, $this->sqlmap_param); } } ?>
Basé sur ce même modèle, il est possible d’adapter toutes requêtes SQL à un contrôle de type TRepeater.
Source (EN): http://www.pradosoft.com/forum/index.php?topic=12784
