Liste des billets similaires en SQL (sans plugin Wordpress)

Novembre 2012

Beaucoup de blogs Wordpress et autres sites internet de contenu proposent souvent une liste de billets similaires (sur le même sujet, ayant une thématique proche) qu'on appelle aussi "fil de lecture".

C'est par exemple le cas sur PhotoshopTuto.

Sur Wordpress, il existe de nombreux plugins pour faire ça (comme YARPP). Il est aussi possible d'utiliser l'API wordpress pour récupérer une liste d'articles similaires :

<?php
$args = array(
	'post__not_in'	=> array($post->ID),
	'showposts'		=> 10,
	'orderby'		=> rand
);
$tuto_more = new WP_Query($args);
?>

Les 2 points importants auquel il faut porter attention sont :

La pertinence
Il faut proposer les billets dont le sujet le plus proche possible de l'article courant.
Les performances
Cette fonctionnalité implique d'interroger la base de données (MySQL) et de faire un traitement PHP.
Elle a donc un impact non négligeable sur les performances (et donc sur le coût de votre hébergement, et sur le SEO, puisque google n'aime pas les sites lents, et les visiteurs non plus d'ailleurs)

Donc dans ce tuto on va voir comment récupérer une liste pertinente et avec un code le plus léger possible.

Pour cela, l'idée c'est d'utiliser la taxonomie (les tags et les catégories) qui sont attribués aux posts de manière à calculer un "score" et ainsi être en mesure de ne récupérer que le haut du classement, i.e. les articles dont la thématique est la plus proche.

J'ai donc écrit cette requête qui ne prend que 2 paramètres : l'identifiant du post (le post courant), et le nombre de billets à remonter :

SELECT p.ID, post_date, post_title, post_name
FROM wpst_posts p
INNER JOIN (
	SELECT tr.object_id, COUNT(tr.object_id) AS _score
	FROM wpst_term_relationships tr
	INNER JOIN wpst_posts p
		ON  p.ID = tr.object_id
		AND p.ID != 777
		AND p.post_type = 'post'
		AND p.post_status = 'publish'
	WHERE tr.term_taxonomy_id IN (
		SELECT term_taxonomy_id FROM wpst_term_relationships WHERE object_id = 777
	)
	GROUP BY tr.object_id
	ORDER BY _score DESC, tr.object_id DESC
	LIMIT 0,10
) tmp
ON tmp.object_id = p.ID

Alors comme ça elle a l'air un peu compliqué, mais en réalité c'est assez simple :

Je commence par récupérer la liste des posts (object_id) qui sont affectés à des posts (post) publiés (publish), en excluant le post courant (car ça n'a aucun sens d'afficher le post en cours dans la liste des billets similaires... !)
Ensuite j'ajoute une condition en demandant uniquement les termes (catégories et tags) qui sont affectés au post courant (c'est à ça que sert la clause IN)
J'utilise la clause d'agrégat GROUP BY pour enlever les doublons dans les identifiants de post, et au passage je compte le nombre de termes communs pour chaque post (avec COUNT)
Tout ça me donne une liste d'identifiants de posts qui sont similaires au post courant, je n'ai plus qu'à me servir de cette liste d'identifiants pour faire une jointure sur la table posts, et bingo, ça me donne une liste de posts (avec tous les attributs nécessaires)

La page du jour est différence entre format xls et xlsx, bonne lecture.

Y'a plus qu'à afficher le tout avec un foreach et l'affaire est dans le sac (il faut mettre ça dans votre thème, dans le fichier single.php) :

<?php
# Sélection des posts similaires triés par pertinence, avec une requête SQL maison
$nombreArticles = 10;
$related = $wpdb->get_results("
SELECT p.ID, post_date, post_title, post_name
FROM wpst_posts p

INNER JOIN (
	SELECT tr.object_id, COUNT(tr.object_id) AS _score
	FROM wpst_term_relationships tr
	INNER JOIN wpst_posts p
		ON  p.ID = tr.object_id
		AND p.ID != {$post->ID}
		AND p.post_type = 'post'
		AND p.post_status = 'publish'
	WHERE tr.term_taxonomy_id IN (
		SELECT term_taxonomy_id FROM wpst_term_relationships WHERE object_id = {$post->ID}
	)
	GROUP BY tr.object_id
	ORDER BY _score DESC, tr.object_id DESC
	LIMIT 0, {$nombreArticles}
) tmp
ON tmp.object_id = p.ID");

# Affichage des posts similaires
if($related):
foreach( $related as $key => $val ){
	$titleClean = htmlspecialchars($val->post_title);
	$postURL = "/tuto/".$val->post_name.'-'.$val->ID;
	?>
	<li><a href="<?php echo $postURL; ?>" title="Tuto : <?php echo $titleClean; ?>"><?php echo $titleClean; ?></a></li>
	<?php
}
endif;
?>

Cette méthode n'a que des avantages :

Résultats pertinents (pour peu que vous ayez correctement tagué et classé vos articles)
Performances optimales (une seule requête SQL)
Sur PhotoshopTuto, cette astuce m'a permis de passer à 30ms / 6 requêtes SQL à moins de 1.5ms / 1 requête SQL, radical !
Pas de plugin supplémentaire (qui alourdit le chargement de wordpress)

Le seul "inconvénient" est qu'il faut éditer son thème wordpress, mais ça c'est vraiment facile, vous n'avez qu'à faire copier-coller ! (et modifier la variable $postURL, pour que ça corresponde au format de permalien configuré sur votre wordpress.

Allez donc jeter un oeil sur cette page : ange allumeuse.

Fil de lecture

0 commentaire