SALT > Manuel > Tutoriel > Base de données > API Query >

Création d'une requête

Une requête SELECT se créé à partir de l'objet que l'on souhaite récupérer avec la méthode statique ::query().
<?php
$q = MonObjet::query(); Cela permettra à la requête de récupérer les méta-données de l'objet (nom de la table, nom et types des champs, etc...)

Choix des champs à récupérer

Lors de la création de la requête, on peut indiquer un paramètre TRUE pour dire qu'on souhaite récupérer tout les champs.
Le framework ne génèrera jamais de SELECT * pour éviter de récupérer des champs non mappés et pour contrôler la manière dont ils sont récupérés.
<?php
$q = MonObjet::query(TRUE); // créé une requête qui récupère tout les champs de l'objet Si on veux récupérer une liste de champs de l'objet, il suffit de les lister avec selectFields(array) : <?php
$q = MonObjet::query();
$q->selectFields(array('champ1', 'champ2', 'champ3')); Si on souhaite indiquer les champs à récuperer un à un ou si on veux donner un alias particulier à un champ, on doit appeler selectField($field, $alias = NULL) <?php
$q->selectField('champ1', 'champ1withAlias'); Enfin, pour les cas les plus complexes, si ce qu'on souhaite récupérer n'est pas un champ de l'objet par exemple, ou est une fonction d'un champ de l'objet, on doit utiliser select(SqlExpr $expr, $alias = NULL)
Dans ce cas, il faudra toujours spécifier un $alias, il n'est facultatif que dans le cas ou SqlExpr est un champ d'une autre requête, dans ce cas là le nom du champ sera utilisé si l'alias n'est pas fourni.
<?php
$q->select(SqlExpr::_MAX($q->champ1), 'maxChamp1'); // ajoute SELECT MAX(t0.champ1) as maxChamp1

Récupération de champs non mappés

Il est possible de récupérer des champs qui ne sont pas mappés dans l'objet
Par exemple, lorsqu'on récupère un COUNT ou un MAX, la colonne ne peut être mappé dans l'objet

Pour cela, il suffit de les ajouter à la clause SELECT, ils seront automatiquement mappés sur des propriétés dynamiques de l'instance des objets récupérés et accessibles normalement comme des champs déclarés.
Cependant, ils seront tous déclarés comme des champs de type TEXT pouvant être NULL. De plus, la méthode COLUMN() affichera le nom du champs au lieu d'un texte représentant le champ. Ce comportement peut être redéfini dans un DAOConverter
<?php
$qPerson = Person::query();
$qCity = City::query();
$qCity->join($qPerson, 'id', '=', $qPerson->city);

$qCity->selectField('city_name');
$qCity->select( // ajout d'un champ supplémentaire
        SqlExpr::_COUNT($qPerson->person_name->distinct()),
        'nbPerson' // alias du champ
        );

// lors de l'exécution, la propriété 'nbPerson' sera automatiquement créée et accessible
$result = $DBtest->execQuery($qCity);

$city = salt\first($result->data);
echo $city->city_name; // affiche le champ déclaré
echo $city->nbPerson; // affiche le champ supplémentaire non déclaré
echo City::COLUMN('city_name'); // affiche le nom du champ
echo City::COLUMN('nbPerson'); // affiche "nbPerson"

$city2 = new City();
echo $city2->nbPerson; // leve une exception : champ inexistant sur cette instance

Clause WHERE

Il y a 2 méthodes principales pour ajouter des conditions : whereAnd et whereOr et elles fonctionnent de la même manière.
whereAnd va ajouter une condition "AND ..."
whereOr va ajouter une condition "OR ..."
Il est déconseillé de mixer les deux dans une même instance de requête, les résultats étant imprévisibles. (voir Sous-requête plus bas pour cela)

Les méthodes whereAnd et whereOr prennent 3 paramètres :

• $fieldOrExpr
  Doit être un champ de l'objet ou un SqlExpr.
• $operator
  Est un texte littéral contenant l'opérateur.
  Oui, il est possible d'indiquer n'importe quoi sans contrôle, donc il ne faut pas utiliser de variable provenant de l'utilisateur ici, sinon il y a un risque d'injection SQL.
  Vous pouvez utiliser ce manque de contrôle pour détourner l'API si ca vous amuse ;o)
• $valueOrExpr
  Est une valeur ou un SqlExpr. La valeur peut être un tableau, dans ce cas chaque valeur du tableau sera bindé et la condition produite sera de la forme $fieldOrExpr $operator (valeur1, valeur2, etc...)

<?php
$q = MonObjet::query();

// exemple simple
$q->whereAnd('champ', '>=', 1);                   // AND champ >= :vX
// avec un tableau de valeurs, et un OR : éviter de mixer AND et OR comme ca
$q->whereOr('champ', 'IN', array('a', 'b', 'c')); // OR champ IN (:vX, :vY, :vZ)
// exemple d'injection SQL dans l'opérateur... à éviter ;o)
$q->whereAnd('champ', '=1 OR 1=1 OR 1=', 0);      // AND champ =1 OR 1=1 OR 1= :vX

// pour IS NULL / IS NOT NULL
$q->whereAnd('champ', 'IS', SqlExpr::value(null));        // AND champ IS NULL
$q->whereAnd('champ', 'IS', SqlExpr::value(null)->not()); // AND champ IS NOT NULL

// condition complexe : AND CONCAT(champ1, :v0) = CONCAT(:v1, champ2)
$q->whereAnd(
    SqlExpr::_CONCAT($q->champ1, '@'),
    '=',
    SqlExpr::_CONCAT('@', $q->champ2)); Le 1er et le 3ème paramètre ne sont pas interchangeable :

• Une chaine de caractère dans le 1er champ sera interprétée comme un champ de l'objet, et non comme une valeur.
  Si on veux utiliser une valeur à cet endroit, il faut utiliser un SqlExpr::value()
• Une chaine de caractère dans le 3eme champ sera interprétée comme une valeur, et non comme un champ de l'objet.
  Si on veux utiliser un champ d'un objet à cet endroit, il faut utiliser Query->champ

Clause WHERE - Objets

Il est possible d'ajouter automatiquement une condition sur le champ principal d'un objet que l'on a déjà récupéré.
Le champ principal est celui enregistré dans la méthode metadata() avec registerId. <?php
$q = MonObjet::query();

$q->whereAndObject($monObjet); // avec un objet simple, :v0 est la valeur du champ "id" de $monObjet
echo $q->toSQL(); // SELECT FROM mon_objet t0 WHERE t0.id = :v0

$q->whereAndObject(array($monObjet1, $monObjet2)); // avec une liste d'objets
echo $q->toSQL(); // SELECT FROM mon_objet t0 WHERE t0.id IN (:v0, :v1)

Clause WHERE - Sous-requête

Pour ajouter des parenthèses et ordonner les conditions, il faut créer des sous requêtes à l'aide de getSubQuery(). Si on utilise un nouvel appel à ::query(), l'alias sera différent et les champs ne correspondront pas.
Une fois la sous requête créée, on utilisera whereAndQuery ou whereOrQuery pour l'ajouter à la requête principale. <?php
$q = MonObjet::query();

// Création d'une sous requete pour ajouter des parenthèses autour des conditions OR
$subQuery = $q->getSubQuery();
$subQuery->whereOr('champ2', '=', '1');
$subQuery->whereOr('champ2', '=', '2');

// Conditions sur la requete principale
$q->whereAnd('champ1', '=', 'aze');
$q->whereAndQuery($subQuery);

echo $q->toSQL(); // SELECT FROM mon_objet t0 WHERE t0.champ1 = :v2 AND (t0.champ2 = :v0 OR t0.champ2 = :v1)

Clause WHERE - EXISTS

La méthode whereAndExists($otherQuery) permet de générer une condition de la forme : WHERE EXISTS (SELECT 1 FROM ...)
<?php
// Recherche des villes contenant au moins une personne
$q = City::query(TRUE);

// Construction de la requête sur les personnes
$q2 = Person::query();
$q2->whereAnd('city', '=', $q->id); // on référence le champ ID de $q, c'est à dire de City

$q->whereAndExists($q2); // on fait le lien entre les deux requêtes

echo $q->toSQL(); // affiche :
// SELECT t0.id as id, t0.city_name as city_name
// FROM city t0
// WHERE EXISTS (
//        SELECT 1
//        FROM person t1
//        WHERE t1.city = t0.id
// ) On peut également utiliser whereAndExists($otherQuery, FALSE) pour générer WHERE NOT EXISTS

Jointure

L'ajout d'une jointure avec une autre table se fait avec join()
Les paramètres sont :

• $other la 2ème requête avec laquelle faire un JOIN
  
• $fieldOrExpr le 1er champ de la condition de la clause ON
  
• $operator l'opérateur de la condition de la clause ON
  
• $valueOrExpr la valeur de la condition de la clause ON
  
• $type le type de jointure. Par défaut : 'INNER'
  

Il n'est pas possible d'ajouter une jointure sans avoir au moins une condition dans la clause ON. Maintenant, vous pouvez construire une condition du genre 1=1 si vous voulez.
Le paramètre $type peut être changé pour indiquer un autre type de jointure valide. Par exemple 'LEFT OUTER'

Une fois la jointure réalisée on peut utiliser la requête jointe pour récupérer un de ses champs avec ->champ et utiliser ce champ dans la requête principale, que ce soit pour le récupérer (clause SELECT), pour ajouter une condition dessus (clause WHERE) ou encore pour les clauses GROUP BY et ORDER BY
<?php
$qCity = City::query();
$qPerson = Person::query();

$qCity->join($qPerson, 'id', '=', $qPerson->city); // réalisation de la jointure

$qCity->selectField('city_name');
// Récupération d'un champ de la requête jointe
$qCity->select($qPerson->person_name);

// Utilisation d'un champ de la requête jointe dans une condition
$qCity->whereAnd($qPerson->id, 'IN', array(1, 2));

// Utilisation d'un champ de la requête jointe dans un ORDER BY
$qCity->orderAsc($qPerson->person_name);

echo $qCity->toSQL(); // affiche :
// SELECT t0.city_name as city_name, t1.person_name as person_name    // Le champ récupéré
// FROM city t0
// INNER JOIN person t1 ON t0.id = t1.city
// WHERE t1.id IN (:v0, :v1)                     // la condition WHERE avec le champ de la jointure
// ORDER BY t1.person_name ASC                   // l'ORDER BY avec le champ de la jointure

Jointure - Clause ON

Il est également possible de construire une clause ON un peu plus complexe avec les 4 méthodes joinOnAnd, joinOnAndQuery, joinOnOr, joinOnOrQuery

Ces méthodes prennent les mêmes paramètres que les méthodes classiques whereAnd, whereOr, whereAndQuery, whereOrQuery à l'exception du 1er paramètre supplémentaire qui doit être la requête jointe afin d'identifier à quelle jointure ajouter la clause en cas de jointure multiples.
<?php
$qCity = City::query();
$qPerson = Person::query();

$qCity->join($qPerson, 'id', '=', $qPerson->city); // jointure simple

$qCity->selectField('city_name');

// Création d'une sous requête pour ajouter à la clause ON
$qCity2 = $qCity->getSubQuery();                   // Uniquement pour l'exemple, il serait plus simple
$qCity2->whereOr('city_name', '=', 'Paris');       //   de faire un
$qCity2->whereOr('city_name', '=', 'Toulouse');    //   whereAnd('city_name', 'IN', array('Paris', 'Toulouse'))

// Ajout à la clause ON : on indique à nouveau $qPerson pour identifier la jointure.
$qCity->joinOnAndQuery($qPerson, $qCity2);

echo $qCity->toSQL(); // affiche :
// SELECT t0.city_name as city_name
// FROM city t0
// INNER JOIN person t1
//        ON t0.id = t1.city
//        AND (t0.city_name = :v0 OR t0.city_name = :v1) // sous requête ajoutée sur la clause ON

Jointure - SELECT

Enfin, il est possible de générer une jointure avec une table construite à la volée en utilisant joinSelect <?php
$qCity = City::query();
$qCity->selectField('city_name');

$qPerson = Person::query();
$qPerson->selectField('person_name');
$qPerson->selectField('city');

$qCity->joinSelect($qPerson, 'id', '=', $qPerson->city); // joinSelect

$qCity->selectField($qPerson->person_name); // ajout d'un champ de la requête jointe

echo $qCity->toSQL(); // affiche :
// SELECT t0.city_name as city_name, t1.person_name as person_name
// FROM city t0
// INNER JOIN (
//        SELECT t1.person_name as person_name, t1.city as city
//        FROM person t1
// ) t1
//        ON t0.id = t1.city

Order By, Group By

Les clauses ORDER BY et GROUP BY peuvent être ajoutées avec orderAsc(), orderDesc(), groupBy()
Les 3 méthodes prennent un champ ou un SqlExpr en paramètre.

Utilisation d'une autre base de données

Il est possible de spécifier un préfixe sur la table à utiliser afin de référencer une table présente sur une autre base de données du même schéma et auquel l'utilisateur a accès. <?php
MonObjet::query()->absoluteDatabase('NomDatabase'); // va générer une requête SELECT ... FROM NomDatabase.mon_objet t0 ...

Exécution d'une requête

Une fois la requête construite, on peut procéder à son exécution sur une base de données en utilisant une instance de DBHelper avec la méthode execQuery()

Les paramètres de execQuery sont :

• $query : La requête à exécuter
• $pagination Optionnel, par défaut NULL : Un objet Pagination permettant de gérer automatiquement une liste importante de résultats en les regroupant par page
• $bindingObject Optionnel, par défaut NULL : Un objet Base permettant de mapper les objets remontés sur un autre type d'objet que celui utilisé pour construire la requête. Un exemple d'utilisation est présenté sur la page des requêtes INSERT

L'objet Pagination est décrit dans la section suivante.

Pagination

Pour utiliser une pagination des résultats, il faut commencer par créer l'objet Pagination en lui donnant l'indice de début d'affichage. Lors de la 1ère requête, cet indice (offset) sera à 0 ou à NULL, mais pour les requêtes suivantes, il prendra la valeur adéquate correspondant à la page consultée.

En général on va donc créer l'objet pagination en lui passant en paramètre une variable récupérée de la requête. Par exemple : <?php
$pagination = new Pagination($Input->G->RAW->offset); La taille de la page est par défaut de 100 éléments.
On peut changer cette taille en indiquant un 2ème paramètre au constructeur de Pagination <?php
$pagination = new Pagination($Input->G->RAW->offset, 50); // pages de 50 éléments seulement Lorsqu'on va passer cet objet à la méthode execQuery, il va être modifié afin de contenir le nombre total de résultats (calculés par une requête COUNT générée par le framework). Ce nombre est accessible via getCount() après l'exécution de la requête.

La requête exécutée sera modifiée avec l'ajout d'une clause LIMIT $offset, $taillePage

L'objet contient des méthodes permettant de construire les liens pour aller d'une page à l'autre :

• getCount() : Renvoi le nombre total de résultats
• getLimit() : Renvoi la taille d'une page
• getMaxPages() : Renvoi le nombre total de page
• getOffset() : Renvoi l'index courant
• getOffsetFromPage($page) : Permet de convertir un numéro de page en index
• getPage() : Renvoi le numéro de la page courante

Enfin, si l'on souhaite exécuter une requête contenant une clause LIMIT a,b mais sans gérer de pagination (par exemple pour récupérer le 1er résultat d'une requête, on peut utiliser LIMIT 0, 1), on doit spécifier un 3ème paramètre au constructeur comme dans l'exemple ci-dessous. Cela permet d'éviter d'exécuter la requête de COUNT, inutile ici. <?php
$q = MonObjet::query(TRUE);

$pagination = new Pagination(0, 50);
$r = $db->execQuery($q, $pagination);
// Va produire les requêtes suivantes :
// SELECT count(*) as nb FROM mon_objet t0
// SELECT t0.... FROM mon_objet t0 LIMIT 0,50

$pagination = new Pagination(0, 1, TRUE); // avec le 3eme paramètre à TRUE, $pagination->getCount() ne sera pas renseigné
$r = $db->execQuery($q, $pagination);
// Va produire les requêtes suivantes :
// SELECT t0.... FROM mon_objet t0 LIMIT 0,1

Utilisation des résultats

La méthode execQuery renvoi un objet DBResult
Cet objet contient :

• columns: Liste des champs récupérés par la requête (en réalité liste des alias)
• data: Liste des objets récupérées par la requête. Chaque objet contient les champs de columns et est modifiable

Un cas classique d'utilisation est l'affichage des résultats sous forme de tableau :
<?php
$q = MonObjet::query(TRUE);
...
$result = $DB->execQuery($q);
?>
<table>
    <tr>
<?php // Affichage du nom des colonnes au debut du tableau ?>
<?php foreach($result->columns as $col) { ?>
        <th><?= MonObjet::COLUMN($col) ?></th>
<?php } ?>
    </tr>
<?php // Affichage de chaque ligne ?>
<?php foreach($result->data as $row) { ?>
    <tr>
    <?php // Pour chaque ligne on boucle sur l'ensemble des colonnes pour les afficher ?>
    <?php foreach($result->columns as $col) { ?>
        <td><?= $row->VIEW->$col ?></td>
    <?php } ?>
    </tr>
<?php } ?>
</table> Un autre cas d'utilisation est la récupération du 1er objet d'une requête, par exemple si la requête ne récupère qu'une seule ligne : <?php
$q = MonObjet::query();
...
$monObjet = salt\first($DB->execQuery($q)->data);
?>