Les requêtes SQL select avec Dotclear 2

Ce billet est un test de documentation pour Dotclear. Il traite des requêtes aux bases MySQL; avec PostgreSQL il faut utiliser ORDER BY RANDOM() à la place de ORDER BY RAND().

J'ai découvert ces fonctions dans ce thread du forum Dotclear.

Dotclear utilise son propre framework qui n'est pas un framework : Clearbricks. C'est lui qui apporte sa couche d'abstraction permettant d'accéder à la base de données du blog. Les fonctions que je détaille sont définies dans les fichiers de /dotclear/inc/clearbricks/dblayer/.

La première chose à noter est qu'une connexion à la base de données est déjà établie, elle est présente sous la forme de l'objet $core->con. Le préfixe des tables SQL de Dotclear est accessible ainsi : $core->prefix.

Ces exemples sont des fonctions qui montrent comment afficher le résultat d'une requête simple lorsque le nombre de requêtes est peu important. Ces fonctions sont à utiliser dans un plugin par exemple.

Nous verrons dans un prochain billet comment utiliser $core->con en POO.

Requête simple sur un champ

On veut l’id d’un billet du blog au hasard :

function sql_result()
{
	global $core;
	$rs = $core->con->select("SELECT post_id FROM ".$core->prefix."post WHERE blog_id = '".$core->con->escape($core->blog->id)."' ORDER BY RAND() LIMIT 1;");
 
	echo($rs->f('post_id'));
}

Explications :

global $core;

Il est impératif de faire référence à $core pour accéder à $core->con, et ce pour toutes les fonctions.

$rs = $core->con->select("SELECT post_id FROM ".$core->prefix."post WHERE blog_id = '".$core->con->escape($core->blog->id)."' ORDER BY RAND() LIMIT 1;");

La requête est réalisée et $rs est le résultat de la requête.

echo($rs->f('post_id'));

On affiche le résultat : la colonne post_id.

Requête simple sur plusieurs champs

On veut l'id et le titre d'un billet du blog au hasard :

function sql_results()
{
	global $core;
	$rs = $core->con->select("SELECT post_id, post_title FROM ".$core->prefix."post WHERE blog_id = '".$core->con->escape($core->blog->id)."' ORDER BY RAND() LIMIT 1;");
 
	echo($rs->f('post_id').' '.$rs->f('post_title'));
}

Explications :

$rs = $core->con->select("SELECT post_id, post_title FROM ".$core->prefix."post WHERE blog_id = '".$core->con->escape($core->blog->id)."' ORDER BY RAND() LIMIT 1;");

$rs est un recordset, constitué de résultats.

echo($rs->f('post_id').' '.$rs->f('post_title'));

On affiche l'id du billet (la colonne post_id) et le titre du billet (la colonne post_title).

Requête sur plusieurs champs et plusieurs enregistrements

On veut l'id et le titre de 5 billets du blog au hasard :

function sql_more_results()
{
	global $core;
	$rs = $core->con->select("SELECT post_id, post_title FROM ".$core->prefix."post WHERE blog_id = '".$core->con->escape($core->blog->id)."' ORDER BY RAND() LIMIT 5;");
 
	while ($rs->fetch())
	{
		echo($rs->f('post_id').' '.$rs->f('post_title'));
	}
}

Explications :

$rs = $core->con->select("SELECT post_id, post_title FROM ".$core->prefix."post WHERE blog_id = '".$core->con->escape($core->blog->id)."' ORDER BY RAND() LIMIT 5;");

Le recordset $rs contient les id et titre de 5 billets.

while ($rs->fetch())

On crée une boucle avec le résultat de $rs.

echo($rs->f('post_id').' '.$rs->f('post_title'));

On affiche les 5 id et titres grâce à la boucle.

Modifications apportées :

2007-06-27 : remplacement de

WHERE blog_id LIKE '".DC_BLOG_ID."'

par

WHERE blog_id = '".$core->con->escape($core->blog->id)."'

14-11-2007

utilisation de RANDOM() avec PostgreSQL

Commentaires

1. Le vendredi 23 novembre 2007, 10:31 par julien

Je sais que ce n'est pas l'objet de cet article, mais je ne peux pas m'empêcher de réagir : c'est une très mauvaise idée de faire un order by rand() limit 1. Ce genre de traitement est inutilement très lourd dès que la base devient de taille moyenne à grande.

En effet, le moteur de base de données face à ce genre de requête devra :

créer une table temporaire de même taille que la table source en y ajoutant une colonne supplémentaire contenant un nombre tiré aléatoirement ;
trier cette table temporaire selon la colonne aléatoire (en parcourant forcément toutes les lignes puisqu'aucun index ne peut être utilisé) ;
ne conserver que la première ligne du résultat et tout vider...

Tout le monde peut bien se rendre compte que cela demande énormément de travail (qui peut, selon la taille de la table, se matérialiser en quantité d'accès disque) pour pas grand chose.

Une méthode arrivant exactement au même résultat en étant beaucoup plus légère :

trouver le nombre total d'enregistrements dans la table via un select count(*) from ... (généralement très rapide car le moteur peut utiliser les index qui vont bien) ;
tirer au hasard (via PHP ou le langage dans lequel est écrit l'application) un nombre compris entre 0 et le nombre trouvé au point 1 (décrémenté d'une unité pour éviter de se trouver hors limites ensuite) ;
aller chercher l'enregistrement qui va bien via une requête SQL de type select ... limit 1 offset <le nombre trouvé au point 2> (très rapide car le moteur peut utiliser ici les index et qu'il n'a (généralement) pas besoin de créer une table temporaire pour mettre les résultats).

Un jour (je l'espère), on arrivera à éradiquer de la face du net tous les didacticiels conseillant la méthode order by rand pour tirer un enregistrement au hasard ! ;-)

2. Le dimanche 25 novembre 2007, 16:29 par Moe

Merci pour ton explication. Je ne peux pas intégrer tes optimisations au code de ces exemples sans l'alourdir. Mais le message est passé, les lecteurs pourront utiliser ta méthode s'ils ont besoin de performance.

gniark.net/blog/