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

Dans les différentes requêtes de l'API Query, les paramètres pourront être de type SqlExpr, aussi bien à la place d'un champ qu'à la place d'une valeur.
Cette classe permet de représenter une expression plus ou moins complexe qui pourra éventuellement correspondre à une colonne.
Par exemple, dans la requête suivante :
SELECT 1, sysdate() FROM dual WHERE 'azerty' = CONCAT('aze', 'rty')
on a 4 SqlExpr, voir 6 :

• 1
• sysdate()
• 'azerty'
• CONCAT('aze', 'rty') qui est en fait composé des SqlExpr 'aze' et 'rty'

Pour construire un SqlExpr, on doit utiliser les méthodes statiques de SqlExpr :

• SqlExpr::value($valeur)
  Correspond à une valeur simple. A noter que la valeur n'apparaitra pas en dur dans la requête préparée : on utilisera le mécanisme de binding.
  Si la valeur est NULL (PHP) on la remplacera par la valeur spéciale NULL (SQL)
• SqlExpr::_FUNCTION($args)
  Correspond à l'appel d'une fonction MySQL. Par exemple _IF, _COUNT, _CONCAT, etc...
• Query->fieldName (ou SqlExpr::field())
  Correspond à un champ provenant d'un autre objet. En principe on n'appelle pas directement cette méthode de SqlExpr, mais on appelle la propriété Query->nom_colonne sur une requête de l'objet souhaité.
• SqlExpr::text($texte)
  Correspond à un text simple. La valeur apparaît en dur dans la requête préparée, c'est donc au développeur de s'assurer que la valeur est correctement échappée pour une utilisation SQL. On conseille de ne pas utiliser de variables venant de l'utilisateur avec cette méthode, mais uniquement des valeurs en dur.
• SqlExpr::tuple($args)
  Renverra la liste des paramètres, séparés par des virgules et entourés de parenthèses : (arg1, arg2, ...)
• SqlExpr::implode($separator, $args)
  Renverra la liste des paramètres, séparés par le séparateur : arg1 separator arg2 separator ...

La requête précédente pourrait être écrite de cette manière (à condition d'avoir mappé la table "dual" sur l'objet "Dual"):
<?php
$q = Dual::query();
$q->select(SqlExpr::value('1'), 'v1');   // on doit donner des alias car "1" n'est pas un nom de property valide
$q->select(SqlExpr::_SYSDATE(), 'v2');   // idem pour sysdate()
// la clause WHERE
$q->whereAnd(SqlExpr::value('azerty'),   // "field" de gauche
        '=',                             // opérateur
        SqlExpr::_CONCAT(                // "field" de droite qui est une fonction prenant des paramètres
                SqlExpr::value('aze'),   // qui sont des valeurs simples
                SqlExpr::value('rty')
        )
);
echo $q->toSQL(); // affiche : SELECT :v3 as v1, sysdate() as v2 FROM dual t0 WHERE :v0 = CONCAT( :v1, :v2)

// ou plus simple :
$q->whereAnd(SqlExpr::value('azerty'),   // "field" de gauche
        '=',                             // opérateur
        SqlExpr::_CONCAT('aze', 'rty')
);

// ou encore :
$q->whereAnd(SqlExpr::value('azerty'),   // "field" de gauche
        '=',                             // opérateur
        SqlExpr::_CONCAT(SqlExpr::text("'aze'"), SqlExpr::text("'rty'")) // ne pas oublier les '...' car le texte est inséré tel quel dans la requête
);
// dans ce dernier cas, la requête générée ne contiendra pas de bind pour les 2 derniers paramètres :
// ... WHERE :v0 = CONCAT('aze', 'rty') Un autre exemple plus complexe qui montre une jointure, un group by et un distinct

Une SqlExpr a un type et si ce type est DATE, elle a aussi un format.
Le type est utilisé pour binder les variables.
Par exemple si on a une clause WHERE du genre : SqlExpr opérateur variable, le type de SqlExpr sera utilisé pour binder la variable associée.
Il est possible de forcer le type d'une SqlExpr en appelant l'une des méthodes suivantes après sa construction : asBoolean(), asNumber(), asText(), asDate($format), asTimestamp($format)

Template

Lorsqu'on cherche à générer des expressions complexes, on peut en général utiliser implode, mais si l'on souhaite conserver de la lisibilité il est conseillé d'utiliser un template.
Par exemple, si on veux générer champ1 + champ2 - champ3 * 4 : <?php
$query = MonObjet::query();

// On commence par construire un SqlExpr d'un des champ, par exemple champ1.
// Il sera considéré comme le champ principal (MAIN). L'expression finale sera de même type que ce champ.
$expr = $query->champ1;

$expr->template(
    SqlExpr::TEMPLATE_MAIN.' + '.SqlExpr::TEMPLATE_PARAM.' - '.SqlExpr::TEMPLATE_PARAM.' * '.SqlExpr::TEMPLATE_PARAM,
    $query->champ2,
    $query->champ3,
    4
);

echo $expr->toSQL(); // affiche t0.champ1 + t0.champ2 - t0.champ3 * :v0

// ou encore, avec implode (moins lisible) :
$expr = SqlExpr::implode(' + ',
            $query->champ1,
            SqlExpr::implode(' - ',
                $query->champ2,
                SqlExpr::implode(' * ', $query->champ3, 4)
            )
        );
echo $expr->toSQL();
La syntaxe de déclaration d'un template est assez lourde, et ne devrait être utilisée que si on ne peux pas faire autrement.
Il est possible d'appeler des fonctions SQL avec un template du genre 'FUNC(_main_, _param_)' mais c'est à éviter : SqlExpr::_FUNC est fait pour ca.

Enfin, il existe quelques templates prédéfinis :

• distinct(): Ajoute DISTINCT avant le champ principal. <?php
$expr = $query->champ1->distinct();
echo $expr->toSQL(); // affiche : DISTINCT t0.champ1
• not(): Ajoute NOT avant le champ principal. <?php
$expr = SqlExpr::value(NULL)->not();
echo $expr->toSQL(); // affiche : NOT NULL
• plus($valeur): Incrémente ou décrémente le champ principal de la $valeur passée en paramètre <?php
$expr = $query->champ1->plus();
echo $expr->toSQL(); // affiche : t0.champ1 + :v0 // avec v0 qui sera remplacé par 1 (valeur par défaut)
$expr = $query->champ1->plus(-3);
echo $expr->toSQL(); // affiche : t0.champ1 - :v0 // avec v0 qui sera remplacé par 3
• after($valeur): Ajoute quelque chose après le SqlExpr <?php
$expr = $query->champ1->after(SqlExpr::text(' * 3')); // si ce qu'il faut ajouter est un texte, il faut utiliser SqlExpr::text()
echo $expr->toSQL(); // affiche : t0.champ1 * 3       //   sinon il sera transformé en placeholder et on aura t0.champ1:v0
• before($valeur): Ajoute quelque chose avant le SqlExpr <?php
$expr = $query->champ1->before(SqlExpr::text('- '));
echo $expr->toSQL(); // affiche : - t0.champ1
• parenthesis(): Ajoute des parentheses autour de l'expression <?php
$expr = $query->champ1->parenthesis();
echo $expr->toSQL(); // affiche : (t0.champ1)

Date et SqlExpr

L'utilisation d'une date dans une expression peut être complexe. On a essayé dans le framework SALT de cacher cette complexité en ne demandant au développeur que le format de stockage de la date lors de la déclaration de la classe... mais cela ne suffit pas ici.

SqlExpr est capable de transformer une date (c'est à dire une SqlExpr de type DATE) en type MySQL DATE, en timestamp (INT) ou encore dans le format de stockage MySQL déclaré dans l'objet

Le tableau ci-dessous résume le formatage par défaut des différents champs de type DATE en fonction de leur utilisation.

	DATE	INT (timestamp)	Format de stockage (Set)
Champs SELECT		X
Champs SET/INSERT			X
Autres (WHERE)	X

• SELECT: Les champs récupérés dans la clause SELECT sont transformés en INT car en interne les objets PHP manipulent des TIMESTAMP unix.
• SET/INSERT: Lorsque le champ est inséré ou modifié en base, il faut utiliser le format de stockage déclaré dans l'objet.
• Autres: Dans tout les autres cas, le champ est converti en DATE car MySQL propose de nombreuses fonctions travaillant sur ce type, il a donc paru plus approprié.

Il est possible de changer le comportement par défaut en appelant explicitement les méthodes asDate(), asTimestamp() ou asSetter() sur SqlExpr. Si SqlExpr est déjà de type DATE, le paramètre $format est optionnel et ne pas le renseigner conservera le format d'origine.

Si une expression complexe est utilisée pour un setter, il y aura une conversion qui dépendra des types déclarés et indiqués
Par exemple si on a :

• un champ dont le format de stockage est '%d%m%Y'
• une requête qui affecte à ce champ la valeur NOW()

<?php
$q = ExamplesDates::updateQuery(); // Objet ayant un champ 'dmy' au format %d%m%Y
$q->whereAnd('id', '=', 2);

$q->set('dmy', SqlExpr::_NOW()); // Utilisation d'une SqlExpr pour modifier le champ
echo $q->toSQL(); // Affiche : UPDATE test_dates t0 SET dmy = FROM_UNIXTIME(NOW(),'%d%m%Y') WHERE t0.id = :v0 La requête échouera car la fonction NOW() renvoi un type MySQL DATETIME, ce que le framework ne peut pas déterminer tout seul.
Lorsqu'on passe cette date à la fonction de mise en forme pour stockage FROM_UNIXTIME, qui attend un timestamp UNIX, la fonction renvoi NULL

La solution ici est de décrire le type de retour de l'expression, ici la fonction NOW(), avec asDate : <?php
$q->set('dmy', SqlExpr::_NOW()->asDate(SqlDateFormat::DATETIME));
echo $q->toSQL(); // Affiche : UPDATE test_dates t0 SET dmy = DATE_FORMAT(NOW(), '%d%m%Y') WHERE t0.id = :v0 En décrivant le type de SqlExpr, le framework est capable d'utiliser la fonction DATE_FORMAT() à la place de FROM_UNIXTIME() pour convertir le retour de NOW() dans le format de stockage attendu.

Un autre exemple plus complexe :
Si on souhaite affecter le champ avec une variable PHP dont la valeur est "La date est 2016/06/08"
<?php
$q = ExamplesDates::updateQuery(); // Objet ayant un champ 'dmy' au format %d%m%Y
$q->whereAnd('id', '=', 2);

$valeur = 'La date est 2016/06/08';
$q->set('dmy', SqlExpr::value($valeur)); // Utilisation d'une SqlExpr pour modifier le champ
echo $q->toSQL(); // Affiche : UPDATE test_dates t0 SET dmy = FROM_UNIXTIME(:v1, '%d%m%Y') WHERE t0.id = :v0 Bien évidemment, la requête échoue à nouveau, :v1 étant remplacé par La date est 2016/06/08
<?php
$q->set('dmy', SqlExpr::value($valeur)->asDate('La date est %Y/%m/%d')); // On défini le format de $valeur
echo $q->toSQL(); // Affiche : UPDATE test_dates t0 SET dmy = DATE_FORMAT(STR_TO_DATE(:v2, 'La date est %Y/%m/%d'), '%d%m%Y') WHERE t0.id = :v0 Ici on a une double conversion :

• STR_TO_DATE permet de transformer notre texte spécifique en DATE mysql à l'aide du format
• DATE_FORMAT permet de formater cette date dans le format de stockage attendu

A noter que dans le cas où le SqlExpr est utilisé pour modifier un champ avec set() il est identique d'utiliser asDate() et asTimestamp(). La différence contrôle le type dans lequel l'expression va être convertie, mais dans le cas d'un set, ce type est celui du champ setté.

Si l'expression est utilisé dans une clause WHERE, la différence est importante :
<?php
$q->whereAnd('date', '=',      SqlExpr::value('2016-08-06')->asDate(     '%Y-%m-%d')); // formate le champ pour comparaison avec une date
$q->whereAnd('timestamp', '=', SqlExpr::value('2016-08-06')->asTimestamp('%Y-%m-%d')); // formate le champ pour comparaison avec un timestamp

echo $q->toSQL(); // affiche :
// WHERE ...
// AND t0.date = DATE_FORMAT(STR_TO_DATE(:v3, '%Y-%m-%d'), '%Y-%m-%d %H:%i:%s')
// AND t0.timestamp = UNIX_TIMESTAMP(STR_TO_DATE(:v4, '%Y-%m-%d'))