sql ~ exécuter et afficher des requêtes SQL

L'idée de cette action m'est vue lors de la création des actions pour récupérer des informations pour la documentation d'un site.
Pourquoi ne pas avoir une action qui permette d'exprimer simplement une requête SQL SELECT et de mettre en forme le résultat.

Outre la présentation de données, elle permettrait de répondre simplement à des questions que tous webmasters se posent comme "Quels sont les articles avec un titre trop court pour la SEO"

Voici un exemple obtenu avec cette action. Il s'agit de la liste des articles du site dont la longueur du titre est inférieure à 10 caractères.

Nous allons découvrir étape par étape comment obtenir ce résultat. Les impatients peuvent découvrir les shortcodes utilisés en cliquant ICI

{up sql=modules
 | select=id, title,client_id, LENGTH(title) AS len
 | template=##id## ##len## ##title## ##client_id##
 | where= client_id = 0 AND LENGTH(title) < 10
}

La requête

Le plus simple

Obtenir le nombre d'articles d'une catégorie. Je l'utilise sur la page d'acceuil de ce site comme argument de l'action counter pour afficher le nombre d'actions.

{up counter=0,{up sql=content | select=count(id) | where=catid=8 and state=1}}

Les fonctions SQL qui retourne une valeur unique (count, min, max, sum, avg) ne sont pas mise en forme.

Egalement très simple

{up sql=languages}

Il me semble difficile de faire plus simple pour connaitre le contenu d'une table sans avoir besoin d'aller jouer avec PhpMyAdmin !

Avec des options simples

Il n'est pas toujours nécessaire ni souhaitable d'afficher toutes les colonnes et lignes d'une table.

Pour la démo, affichons les 5 derniers articles du site sous la forme d'une liste.

• 17 : Version 5 crée le : 2024-01-21 07:45:32
• 17 : Version 3.1.2 crée le : 2023-11-08 07:45:32
• 17 : Version 3.1 crée le : 2023-09-28 07:45:32
• 8 : random ~ choix aléatoire crée le : 2023-09-25 00:00:00
• 8 : snippet crée le : 2023-09-25 00:00:00

{up sql=content | setlimit=5 | order=created DESC
  | presentation=list | template=##catid## : ##title## [small]crée le : ##created##[/small] | header}

La première ligne contient les options pour définir la requête SQL. Vous noterez que l'action respecte la terminologie JDatabase et SQL. Ce shortcode va créer la requête :

SELECT * FROM #__content ORDER BY created DESC LIMIT 5

La seconde ligne contient les options pour la mise en forme. Présentation demande un affichage sous forme de liste et template est un modèle au format contenant le nom des champs (colonnes) entre un double signe dièse : ID de la catégorie, titre de l'article et date de création. Il est possible de styler cette liste en utilisant le simili-HTML de UP que j'appelle 🆙 BB-Code by UP

Avec jointure

Dans l'exemple précédent, il aurait été préférable d'avoir le nom de la catégorie plutôt que son ID.
Pour cela, il faut faire une jointure entre la table content et categories

{up sql=#__content AS art
 | select=art.title AS title, cat.title AS catname, art.created AS date
 | innerjoin=#__categories AS cat ON cat.id = art.catid
 | setlimit=5
 | order=created DESC
 | template=##catname## ##title## ##date##
}

Les données JSON

Joomla utilise des chaines JSON pour stocker des informations. C'est le cas, par exemple, pour les images  d'un article.

Présentation

{up sql=content | where=id='2' 
 | template=[img src="##images.image_intro##"][br]##title##
 | presentation=div | main-class=tc}

Pour accéder à une donnée JSON, il suffit d'indiquer le nom de la colonne, un point et la clé JSON.

J'ai également utilisé une clause where pour avoir un résultat qui a une image 😀.

Pour mon unique fiche, une présentation dans une balise div est judicieuse ainsi que la classe pour centrer le tout. Noter l'utilisation du bbcode pour éviter leur interprétation par les éditeurs wysiwyg.

Trier la table

En présentation table, il est possible de cliquer sur les titres des colonnes pour trier le contenu.

{up sql=modules | select=id,position,published,title,publish_up,module | where=client_id=0 | order=position
| template=##id## ##published## ##title## ##publish_up## ##position## ##module##
| header=ID,Etat,Titre,Publié le,Position,Module
| col=d5-c5
| published-format=list[-2:icon-trash, 0:icon-unpublish t-rouge, 1:icon-publish t-vert
| published-model=<span class="%s"></span>
| sort=i-s,3-s-s-s}

L'argument de sort est une liste des critères par colonnes.
Dans notre exemple, i pour la première colonne spécifie que le contenu est un entier pour utiliser la méthode adéquate.
Les autres types sont s (string) pour une chaine ou f (float) pour les nombres

Tri multi-colonnes

s,3 pour la deuxième colonne permet de demander un premier tri alphanumérique de la colonne suivi d'un tri de la troisième colonne (le titre).

Valeur de tri dissociée de l'affichage

La colonne 'Etat' montre également que la colonne bien que vide (juste une classe) est triée selon sa valeur originale passée comme attribut de la cellule du tableau. C'est d'ailleurs pour cela que l'on doit faire un tri alphanumérique qui est le format de cet attribut.
UP spécifie automatiquement la valeur de tri pour toutes les colonnes ayant une définition de format de type list ou date.

Ordre de tri initial

Au chargement de la page, la table est triée selon l'ordre de la requête SQL. Vous pouvez le voir grâce au petit triangle dans son titre.

La mise en forme

Les données brutes fournies par la requête méritent d'être enrichies pour un meilleur aspect visuel :  un état corbeille est plus parlant que -2, 2018-10-26 16:27:55 n'est pas très joli pour une date, ...

Dans une vue liste, il est souvent utile de préfixer le résultat. "Accès admin" informe plus que "admin" seul.

Pour mettre en évidence certains résultats, pouvoir styler une ligne ou une cellule serait également bienvenue.

Format des données

S'il est facile d'afficher du texte simple, certaines données nécessitent une interprétation.

Nous pouvons l'indiquer à l'aide d'une option qui reprend le nom de la colonne suivi de '-format'. Exemple : title-format.
L'argument de cette option peut être un des types ci-dessous, suivi d'un argument entre crochets.

text

Il n'est pas toujours souhaitable d'afficher du contenu HTML tel quel. Le type text va supprimer toutes les balises HTML avec en option la possibilité de limiter la longueur du texte.

Exemple : introtext-format=text[100] va afficher les 100 premiers caractères non formatés de l'introtext

replace

Ce type permet de remplacer du texte par un autre. La recherche ne tient pas compte de la case.

La syntaxe complète (proche de la fonction php str_ireplace) est : xxx-format=replace[old1,old2:new1,new2]

 Exemple pour supprimer le préfixe d'un nom de module : module-format=replace[mod_]

Pour remplacer up par Universal Plugin: xxxx-format=replace[up:Universal PLugin]

liste de valeurs

C'est le cas des statuts published ou des niveaux d'accès.

L'option published-format=list[-2:Corbeille,0:Dépublié,1:Publié] va indiquer à l'action qu'il faut remplacer les valeurs retournées par la requête par celles de l'option. Donc pour la valeur -2, le champ ##published## affichera Corbeille.

Une variante est d'indiquer des noms de classe (iconfont) qui seront utilisés dans le template ou le modèle (voir ci-après) avec ces options.

| published-format=list[-2:icon-trash, 0:icon-unpublish t-rouge, 1:icon-publish t-vert
| template=<span class="##published##"></span>
OU
| published-model=<span class="%s"></span>

min

max

Affiche l'argument si la valeur est supérieure à (min) ou inférieure à (max).

date

Précise le format à appliquer à une date

Exemple : created-format=date[%e %B %Y] le format est celui de la fonction php strftime

Code : %y ➜ AAAA, %Y ➜ AA, %m ➜ 01-12, %M ➜ Jan-Dec, %F ➜ Janvier-Décembre, %d ➜ 01-31, %j ➜ 1-31, %D ➜ Lun-Dim, %l ➜ Lundi-Dimanche,
%g ➜ 1-12, %h ➜ 01-12, %G ➜ 0-23, %H ➜ 00-23, %i ➜ 00-59, %s ➜ 00-59 (secondes),

image

On considère que la valeur de la colonne est une image.  xxx-format=img[60]  retourne le code HTML pour afficher l'image inscrite dans un carré de 60px.

Si la colonne xxx retourne 'mon-image.jpg', le code généré sera <img src="/mon-image.jpg" alt="Mon image" width="60px">

regex

Affiche l'argument si la valeur répond à la recherche regex. Ce type est surtout utilisé pour affecter des styles en fonctions du contenu. Voir ci-après.

xxx-format=regex[#^joomla#:texte

Modèle d'affichage

Il permet d'ajouter du texte avant/après la valeur  et de formater les nombres selon les règles pour la fonction php sprintf.

On utilise une option qui reprend le nom de la colonne suivi de '-model'. Exemple : title-model.

Exemple : tva-model=TVA: %0.2f € pour une valeur de 18.1 affichera : TVA: 18,10 €

Par défaut, si la valeur est vide ou égale à zéro, rien ne sera affiché.
Il est possible de changer ce comportement en ajoutant le modèle à la suite :  tva-model=TVA: %0.2f € ; EXPORT

Attention  si vous utilisez un point-virgule, il faut mettre entre guillemets les 2 alternatives et doubler les éventuels guillemets doubles à l'intérieur

Exemple : published-model="[span class=""%s tc""]■[/span]"

bien que le HTML soit autorisé dans les options acceptant des balises, j'utilise du bbcode pour me protéger d'une interprétation par les éditeurs wysiwyg

Style sous conditions

Pour mettre en évidence certaines données, il est possible d'attribuer des classes à des lignes et/ou colonnes en fonction de leur contenu.

Pour la démonstration, je vais reprendre la table utilisée pour le tri. Les lignes seront colorées selon leur état et les cellules seront mises en évidence si elles contiennent un mot.

{up sql=modules | select=id,position,published,title,publish_up,module | where=client_id=0 | order=position
| header=ID,Etat,Titre,Publié le,Position,Module
| template=##id## ##published## ##title## ##publish_up## ##position## ##module##
| col=d5-c5
| sort=i-s,3-s-s-s
| published-format=list[-2:icon-trash, 0:icon-unpublish t-rouge, 1:icon-publish t-vert
| published-model=[span class="%s"][/span]
| publish_up-format=date[%e %B %Y]
| published-rowclass=list[-2:bg-gris, 0:bg-rougeClair
| module-colclass=regex[#mod_lm#:bg-rouge t-jaune,#login#:bg-vert
}

Aide

 Conscient de la difficulté d'utiliser cette action, j'ai mis en place plusieurs mécanismes d'aide.

Debug

En plus des informations habituelles, l'ajout d'une option debug au shortcode va afficher la requête SQL.
Un bon moyen pour comprendre pourquoi le résultat n'est pas celui attendu.

Exemple pour la démo requête avec jointure

Erreur SQL

Lors d'une erreur "fatale" SQL, le message est intercepté et affiché au début de la page

Exemple d'utilisation d'une table inexistante

Erreur de saisie

Bien qu'il soit possible d'utiliser des options non prévues, un contrôle de cohérence est réalisé.

Si vous utilisez un nom qui ne correspond pas à une colonne ou un suffixe (-format, -model) non autorisé, vous serez prévenu.

Exemple : les erreurs détectées dans le shortcode ci-dessous

{up sql=content | template=##idd## ##title##| title-xxx=text | xxxtitle-model=titre: %s | title-format=badtype[12]}

Exemple de code

Retourner une valeur calculée (v2.5)

Le langage SQL permet d'aggréger des données pour faire des calculs. J'utilise cette possibilité pour afficher le nombre d'actions de UP sur la page d'accueil de ce site.

une action parmi les {up counter=0,{up sql=content | select=count(id) | where=catid=8 and state=1}
 | width=40px
 | style=font-size:24px;font-weight:bold} proposées à ce jour.

Le shortcode sql retourne le nombre d'articles (count(id)) publiés (state=1) de la catégorie démos (catid=8)

Aide à la saisie

Il est délicat de se souvenir du nom exact des tables et de ses colonnes.

Pour éviter d'aller fouiller dans PhpMyAdmin, cette action dispose d'une option dbinfos qui affiche des informations sur la base de données courante dans le bloc des messages Joomla en haut de la page.

{up sql | dbinfos}  affiche la liste des tables

{up sql=#_nom_table | dbinfos}  affiche le nom et le type des colonnes de la table indiquée. Les clés sont soulignées.