imgCinnk magazine Novembre 2015 - Numéro 4

Novembre

Trick or treat!

Du SQL pour pimenter Joomla!

Au cours d'un de mes précédents bavardages, j'ai eu l'occasion de vous présenter comment Joomla! gérait vos données et notamment : où et comment il stockait la majeure partie de l'information que vous affichez dans vos sites.

Je vous y indiquais le principe de stockage de l'essentiel de l'information et les raisons qui avaient amené les concepteurs à utiliser cette manière de procéder. Voyons aujourd'hui comment nous pouvons en tirer profit et "améliorer" Joomla! sans écrire une seule ligne de code PHP.

Du SQL pour pimenter Joomla!

Pas de code PHP, mais un peu de code SQL, voire même : beaucoup (passionnément, à la folie ?). Pour atteindre cet objectif, nous allons utiliser le composant phpMyAdmin fournit par quasiment tous les hébergeurs dans leurs outils accessibles du cPanel ou de leur propre interface d'administration.

Je n'ai ni la place ni le talent pour vous apprendre l'utilisation de phpMyAdmin et encore moins les bases du langage SQL. Il vous faudra donc, si vous n'êtes pas familiarisés avec ces deux ensembles complémentaires, assurer vous mêmes vos bases.

En guise de lecture je vous conseille :

  • si vous êtes vraiment trop peu à l'aise avec phpMyAdmin et SQL : l'excellent cours phpMyAdmin proposé par le site OpenClassRooms qui vous initiera aux techniques permettant de stocker des informations dans une base de données (et accessoirement comment les y retrouver).
  • Si vous êtes déjà très à l'aise avec phpMyAdmin mais que vous voulez tout savoir sur le sujet : l'ouvrage de Marc Delisle, qui est un incontournable sur ce sujet.
  • Enfin, si vous êtes déjà très à l'aise avec mySQL : le manuel de référence (en français) de celui-ci, livre de chevet indispensable pour aller jusqu'au bout des choses.

cinnk-magazine-halloween-8Ceci étant acquis, et votre connaissance de base étant assurée, nous allons commencer à mettre en œuvre quelques requêtes simples et nous familiariser avec une méthode utilisable pour toutes les requêtes qui nous permettrons de faire ce que Joomla! ne nous autorise pas de base (et ceci inclut la possibilité de réécrire les pages d'administration de Joomla! pour exporter les données et pouvoir les manipuler facilement avec des outils externes).

Pour nous échauffer un petit peu et apprendre à manipuler l'outil, nous allons créer une requête visant à afficher la liste des auteurs ayant contribué à l'écriture d'articles sur notre site. Cette première requête va être détaillée étape par étape, jusqu'au bout du processus.

ehuguenin

La méthodologie par la pratique

Pour commencer, nous allons lancer l'exécution de phpMyAdmin et y ouvrir la fenêtre requête SQL sur notre site préféré :

SQL pour Joomla! 101

Première requête : les bases pour s'établir

Une fois sélectionné la base de données de travail, commencer par :

  1. sélectionner la table de travail (optionnel)
  2. sélectionner l'onglet SQL
  3. taper le contenu de la requête en s'appuyant sur intellisense pour valider notre syntaxe
  4. exécuter la requête

Notre requête de départ est assez basique. Nous utiliserons la table ##_content. Pour accéder aux articles publiés, il nous faut utiliser les informations auteur (created_by), date de création (created) ainsi que le nom de l'article (title). La requête correspondante :

SELECT created_by, created, title
FROM gucl0_content

Le résultat n'est pas des plus brillants, il faut souvent sans se décourager, cent fois sur le métier mettre et remettre ...

SQL pour Joomla! 102

Première requête : résultat très décevant

Nous n'avons affiché que l'identifiant de l'auteur (le nom ou l'alias sont dans la table ##_users), la date est un timestamp unique, pas très pratique à manipuler ou visualiser. Bref, il nous faut de suite améliorer tout cela. Inutile de retaper toute la requête. Non seulement on peut l'éditer (après avoir cliqué sur [Modifier]), mais les versions récentes de phpMyAdmin gèrent une liste des dernières requêtes et l'on peut rappeler n'importe laquelle de celles-ci (listées en bas d'écran). Notre nouvelle mouture de code devient :

SELECT c.created_by, u.username, DATE(created), title
FROM gucl0_content c, gucl0_users u 
WHERE u.id = c.created_by AND state > 0

Une jointure avec la table ##_users en utilisant la relation id, un traitement d'affichage de la date pour éliminer les détails indésirables du timestamp et pour compléter le tout : l'élimination des articles non publiés (state = 0). L'adjonction de titres plus parlant pour les colonnes donnent un résultat plus satisfaisant :

SQL pour Joomla! 103

Un résultat déjà plus conforme à nos souhaits

Les versions les plus abouties de phpMyAdmin vous offrent en prime la réécriture de votre code, ainsi que la possibilité d'exporter correctement et avec une grande variété de formats, le résultat de vos travaux, pour les traiter localement en toute quiétude :

SQL pour Joomla! 101 106

le code beautifier de phpMyAdmin est très lisible et le mode Export particulièrement utile !

Pour en finir avec la méthodologie de base, sachez que les versions récentes de phpMyAdmin permettent de conserver vos requêtes dans une table interne de signets, accessible par un lien en bas de page. Cette gestion de signets permet de conserver, modifier, réexécuter à volonté vos requêtes préférées. Attention, il ne faut pas confondre les requêtes enregistrées par le système de signets avec des procédures stockées, dont nous aurons l'occasion de reparler ... plus tard.

SQL pour Joomla! 110

Les signets permettent de conserver vos requêtes récurrentes pour les rejouer ad libitum 

Quelques requêtes utiles pour démarrer

cinnk-magazine-halloween-11Nous avons tous une quantité de travaux récurrents sur nos sites pour lesquels Joomla! n'a, dans son interface native d'administration, aucune réponse préétablie. Nous avons vu ci-dessus un moyen simple de lister tous les articles d'un site en affichant l'auteur de l'article. Dans le même ordre d'idée on peut ajouter pour cette première prise de contact avec des extensions SQL pour Joomla! quelques requêtes utiles (en tout cas elles me sont utiles !) :

Liste des auteurs du site :

Combien d'articles écrits par auteur, pour votre site, en tout, pour une période donnée ou pour les six derniers mois  ?

SELECT u.username as 'Auteur', COUNT(u.username) as '# Contributions', u.email as '@Mail'
FROM `gucl0_content` c, gucl0_users u where u.id = c.created_by 
GROUP BY c.created_by
SELECT u.username as 'Auteur', COUNT(u.username) as '# Contributions', u.email as '@Mail'
FROM `gucl0_content` c, gucl0_users u
WHERE u.id = c.created_by AND DATE(c.created) BETWEEN '2015-01-01' AND '2015-06-30'
GROUP BY c.created_by
SELECT u.username as 'Auteur', COUNT(u.username) as '# Contributions', u.email as '@Mail'
FROM `gucl0_content` c, gucl0_users u
WHERE u.id = c.created_by AND DATE(c.created) BETWEEN CURDATE() - INTERVAL 6 MONTH AND CURDATE()
GROUP BY c.created_by

SQL pour Joomla! 111

Le nombre de contributions par auteur pour une période donnée

Les catégories orphelines

Trouver dans l'administration de Joomla! les articles qui n'ont pas de catégories attachées ou, plus exactement ceux qui sont attachés à la catégorie : "non-catégorisée" est facile et trivial, mais avez-vous déjà essayé de trouver vos catégories définies et créées qui, n'étant rattachées à aucun article, sont donc totalement inutiles ?

 Cette requête est déjà plus complexe. Pour démontrer le raisonnement, commençons par afficher la liste des catégories existantes avec le nombre d'articles rattachés à ces catégories :

SELECT ca.id as 'Id', ca.alias as 'Catégorie', ca.published as 'status', (SELECT count(*) FROM gucl0_content co
WHERE co.catid=ca.id) as 'Occurences' FROM gucl0_categories ca WHERE extension = 'com_content' ORDER BY 4

Il faut juste faire attention au fait qu'il existe des catégories pour définir les tables ##_users, ##_banners, ##contact, etc... et qu'il faut (même s'il existe d'autres méthodes) utiliser une sous-requête pour chacune des catégories. Le résultat est conforme à notre attente :

SQL pour Joomla! 101 113

La liste des catégories et le nombre d'articles associés

Reste maintenant, que seules les catégories qui ne sont associées à aucun article, nous intéresse. Nous obtenons ceci en modifiant la requête de cette manière :

SELECT ca.id as 'Id', ca.alias as 'Catégorie', ca.published as 'status'
FROM gucl0_categories ca
WHERE NOT EXISTS(SELECT co.catid FROM gucl0_content co where ca.id = co.catid) AND ca.extension = 'com_content'

SQL pour Joomla! 112

La liste des catégories non utilisées dans vos articles

A noter qu'il faut désormais faire preuve de raison ; la catégorie "non-catégorisé" n'est utilisée nulle part et c'est plutôt une bonne nouvelle ... La catégorie "notes-de-mise-a-jour-2016" est visiblement créée par anticipation, les catégories de codes "status 2" sont archivées et ont donc, en quelque sorte, déjà été mises à l'écart. Il reste à réfléchir aux autres catégories de "status 1". Faut-il les supprimer, les garder ? La réponse à cette question est du ressort du webmaster...

 Parlons un peu des tags...

Comme les catégories, les tags nécessitent quelques compléments à Joomla! Comme pour les catégories, il est possible de réécrire la page d'administration de Joomla! avec une requête simple, ce qui permet d'exporter le résultat et de le manipuler hors de l'administration Joomla!, la requête est simple :

SELECT t.id, t.title, t.alias, t.published as 'status'
FROM `gucl0_tags` t
ORDER BY t.title

Note : on peut en profiter pour regretter que les status des éléments Joomla!, quasiment identiques d'une table à une autre (publié, poubelle, dépublié, archivé), ne fassent pas l'objet d'une normalisation et que leur valeur d'une table à une autre dépende (semble-t-il) du bon vouloir du responsable de l'analyse de départ.

Etablissant avec la table des articles (##_content) une relation n à n, les tags utilisent une table intermédiaire (dite de relation) dont le nom est ##_contentitem_tag_map et alourdit pour le néophyte la compréhension du mécanisme, mais permet (par exemple) d'obtenir assez simplement, pour un tag spécifié, la liste des articles du site auquel il a été attaché :

SELECT tm.tag_id as 'Id Tag', t.title as 'Tag', c.title as 'Article', c.alias
FROM `gucl0_contentitem_tag_map` tm
LEFT OUTER JOIN `gucl0_tags` t ON tm.tag_id = t.id
LEFT OUTER JOIN `gucl0_content` c ON c.id = tm.content_item_id
WHERE t.title = 'abcdefgh'

Bien entendu, comme pour les catégories, il est facile d'obtenir la liste des tags déclarés et non encore utilisés :

SELECT t.id as 'Id', t.title as 'Tag'
FROM `gucl0_tags` t
WHERE t.id <> 1 AND t.published = 1 AND NOT EXISTS (SELECT tm.content_item_id FROM `gucl0_contentitem_tag_map` tm
WHERE tm.tag_id = t.id)

Et maintenant pour finir, la liste des tags classés par nombre d'utilisation dans les articles du site. Vous ne serez pas sans noter que l'on exclut les tags non publiés et qu'il existe un tag inamovible d'id = 1 servant de point de départ à la structure en arbre des tags.

SELECT  t.id, t.title as 'Tag', (SELECT count(tm.content_item_id) FROM `gucl0_contentitem_tag_map` tm WHERE tm.tag_id = t.id) as 'utilisés #'
FROM `gucl0_tags` t
WHERE t.id > 1 AND t.published = 1
ORDER BY 3

Nous pourrions continuer encore longtemps sans épuiser le sujet, mais le fait est que chacun doit adapter les solutions à ses besoins et mon objectif est simplement de vous montrer à quel point il est (relativement) facile d'adapter Joomla! à vos besoins ponctuels en utilisant phpMyAdmin.

Les différents exemples utilisés pourront être téléchargés à partir du fichier sqlsample.txt ci-joint.

Nous nous retrouverons très bientôt pour mettre en œuvre la facette complémentaire des requêtes SQL, facette qui vise à modifier massivement vos articles en les mettant à jour ou en les modifiant par l'intermédiaire de requêtes ciblées. Nous finirons ultérieurement, en abordant le thème de programmation SQL, en voyant comment mettre en œuvre des projets plus ambitieux que ces quelques requêtes, grâce à l'utilisation de procédures stockées.

Notez cet article:
5
Un titre d’article ou de menu… sur 2 lignes plutôt...
De l’informatique militante à la passion Joomla!

Commentaires 6

 
fynhooft le mardi 3 novembre 2015 21:01

Bonjour,

Intéressant tout cela. Mais, il est où le fichier "sqlsample.txt" ?
Ne serait-ce qu'un morceau (oublié) du gâteau ?

Bonjour, Intéressant tout cela. Mais, il est où le fichier "sqlsample.txt" ? Ne serait-ce qu'un morceau (oublié) du gâteau ?
PieceOfCake le mardi 3 novembre 2015 21:47

non, non pas oublié, juste le lien qui a 'sauté' à la publication, on va réparer ça :)

non, non pas oublié, juste le lien qui a 'sauté' à la publication, on va réparer ça :)
PieceOfCake le mercredi 4 novembre 2015 08:39

Voilà, c'est réparé et le lien est à nouveau disponible en bas d'article, merci de l'avoir signalé.

Voilà, c'est réparé et le lien est à nouveau disponible en bas d'article, merci de l'avoir signalé.
marie-louise le vendredi 20 novembre 2015 13:06

Super intéressant, par contre j'ai encore des progrès a faire !

Super intéressant, par contre j'ai encore des progrès a faire !
PieceOfCake le vendredi 20 novembre 2015 16:00

On a tous toujours des progrès à faire (heureusement : ça motive).

Si tout va bien, je reprends la suite en Janvier ....

On a tous toujours des progrès à faire (heureusement : ça motive). Si tout va bien, je reprends la suite en Janvier ....
marie-louise le vendredi 27 novembre 2015 14:59

Timing parfait, Php My admin fait partie de ce que j'ai prévu d’apprendre sur mes vacances de décembre. Ca m'évitera surement de bloquer après le premier exemple.

Timing parfait, Php My admin fait partie de ce que j'ai prévu d’apprendre sur mes vacances de décembre. Ca m'évitera surement de bloquer après le premier exemple.
Déjà inscrit ? Connectez-vous ici
Guest
dimanche 24 septembre 2017
Si vous souhaitez vous inscrire, veuillez saisir un nom d'utilisateur, mot de passe et nom.

Image Captcha

Sur ce site, nous utilisons des cookies.