Tri des titres avec ORDER BY (via requête SQL)

[Mirovinben]

Quand on veut exécuter une requête SQL sur la table dc_post avec un résultat trié par ordre alphabétique (genre "ORDER BY LOWER(post_title) ASC", les titres commençant par un ou plusieurs caractères accentués sont listés après les autres titres.

Ma proposition : ajouter dans la table des billets une colonne contenant les titres des billets convertis en minuscules non accentuées (via PHP car, à ma connaissance, mySQL n'a pas de méthode simple et peu chronophage pour le faire).

[franck]

Un moyen simple, en attendant une éventuelle prise en compte de ce ticket :

1. Convertir le recordset en recordset static étendu → $rs_static = new extStaticRecord($rs);
2. Trier le résultat avec la fonction de tri lexical → $rs_static->lexicalSort($field,$order);

Avec $field contenant le champ servant au tri, et $order contenant 'asc' (par défaut) ou 'desc'

Donc dans ton cas ça pourrait être :

$rs_static = new extStaticRecord($rs);
$rs_static->lexicalSort('post_title');

Après tu utilses $rs_static en le parcourant avec fetch(), … exactement comme avec le $rs auparavant.

Ça va consommer un peu plus de mémoire mais ça devrait faire le job (pas testé, tu me diras).

[Mirovinben]

Grâce à ton aide, Franck, j'ai fait quelques tests chronométrés, avec ou sans optimisations (cf  cette discussion sur le forum (notamment le commentaire #13).

Le résultat n'est pas concluant... Ma proposition reste donc pertinente.

[franck]

Et en oubliant le lexicalSort pour demander au driver de DB de trier selon un collate ?

$collate = ($con->driver() == 'mysql' || $con->driver() == 'mysqli' ? '"utf8_unicode_ci"' : $con->driver() == 'pgsql' ? '"C.UTF-8"': '"UNICODE"');
$params['order'] = 'post_title COLLATE '.$collate;
$rs = $core->blog->getPosts($params);

[franck]

Je viens de faire un test : côté MySQL ça dépote, mais ça plante côté SQLite (ce n'est pas aussi simple), cela dit on pourrait faire l'impasse avec ce driver.

Donc ça pourrait se limiter à :

		$collate = '';
		if (DC_DBDRIVER == 'mysqli' || DC_DBDRIVER == 'mysql') {
			$collate = ' COLLATE utf8_unicode_ci ';
		} elseif (DC_DBDRIVER == 'pgsql') {
			$collate = ' COLLATE "C.UTF-8" ';
		}
		$params['order'] = $sortby.$collate.' '.$order;
		$rs = $core->blog->getPosts($params);
		…

Parce que la colonne supplémentaire ça m'embête un peu vu qu'il va falloir s'occuper de l'existant d'une part, et que d'autre part supprimer les diacritiques et mettre ça en minuscules ne vaut que pour les langues latines. Quid des autres ?

Passer par le driver MySQL/PostgreSQL (voire SQLite si on peut) est de loin préférable (et optimisé pour).

[Mirovinben]

Replying to franck:

Je viens de faire un test : côté MySQL ça dépote, mais ça plante côté SQLite (ce n'est pas aussi simple), cela dit on pourrait faire l'impasse avec ce driver.

Je n'avais pas tout capté. Je viens de refaire des tests avec base mysqli, ça va super vite et mieux :

• avec ORDER BY LOWER(post_title) ASC dans la requête SQL : 0,20 secondes mais tri incomplet
• avec ORDER BY post_title COLLATE dans la requête SQL : 0.25 secondes mais tri complet

Parce que la colonne supplémentaire ça m'embête un peu vu qu'il va falloir s'occuper de l'existant d'une part, et que d'autre part supprimer les diacritiques et mettre ça en minuscules ne vaut que pour les langues latines. Quid des autres ?

Tu as raison, je n'avais pas songé à ça, trop préoccupé par mon pb de tri incomplet.

Passer par le driver MySQL/PostgreSQL (voire SQLite si on peut) est de loin préférable (et optimisé pour).

Je vais faire l'impasse pour l'instant en proposant un tri incomplet. C'est mieux que rien.

[franck]

Cela dit ça vaudrait peut-être le coup de se repencher la-desssus et d'intégrer un tri lexical, quand c'est possible, côté Clearbricks pour pouvoir en profiter partout (pas seulement dans les listes de billets).

[franck]

Pour SQLite, les doubles quotes sont apparemment en trop, donc ça devrait fonctionner avec un simple COLLATE UNICODE

En fait non, le support Unicode pour le tri est tout simplement absent dans SQLite, pas la peine de chercher plus loin.

[Gvx]

peut-être une piste pour SQLite:  http://php.net/manual/fr/pdo.sqlitecreatecollation.php

Reste a voir les performances.

[franck]

Oui j'ai vu pas mal de choses pour corriger ce problème côté SQLite, et comme tu dis, pas évident qu'on retrouve une perf' similaire à celles de MySQL/PgSQL !

[Gvx]

Je viens de faire des tests de performances avec le plugin mrvbToC en utilisant les fonctions class suivantes:

• PDO::sqliteCreateCollation  http://fr2.php.net/manual/fr/pdo.sqlitecreatecollation.php
• La classe Collator  http://www.php.net/manual/fr/class.collator.php

Les performances semblent correctes puisque sur un blog contenant 2198 billets et 8 pages le temps de génération passe de 0.31s a 0.37s

Par contre pour utiliser cela il faut minimum PHP 5.3.11 et l’extension intl (d’où les tests de class / methode)

if (DC_DBDRIVER == 'sqlite' && class_exists('Collator') && method_exists($core->con->link(),'sqliteCreateCollation') && !empty((string)$core->blog->settings->system->lang)) {
	$collate_locale = new Collator($core->blog->settings->system->lang);
	if ($core->con->link()->sqliteCreateCollation('collate_locale',array($collate_locale,'compare'))) {
		$collate = ' ORDER BY P.post_title COLLATE collate_locale ';
	}
}

Discussion sur le forum  https://forum.dotclear.org/viewtopic.php?pid=337893#p337893

[franck]

Merci pour les tests SQLite.

Faut que je réfléchisse au meilleur moyen d'intégrer ça mais je ne sais pas encore si ça sera côté Clearbricks ou côté Dotclear.

Maintenant si ça te dis de proposer une implémentation, c'est open, hein ? On peut en discuter si tu veux

[Gvx]

Après quelques tests avec le plugin mrvbToC, il apparait que cela n'est pas si simple également avec postgreSQL. (voir sur le forum  https://forum.dotclear.org/viewtopic.php?pid=337992#p337992)

En fait je pense qu'il faudrait ajouter a getLanguagesDefinitions dans inc/libs/clearbricks/common/lib.l10n.php une colonne avec les tags lang-region a la norme RFC 4646 ( https://fr.wikipedia.org/wiki/%C3%89tiquette_d%27identification_de_langues_IETF)

Car ce sont ces codes qui sont a la base de l'utilisation des collates dans postgreSQL et sqlite

[Gvx]

A la question que tu posais sur le forum,

Autre question : dans le code proposé, on s'appuie sur la langue du blog, ce qui va forcer un ordre de tri particulier, y compris sur des blogs multilingues, ça peut être gênant, c'est la raison pour laquelle je privilégiais entre autre pour pgsql le C.UTF8 plus générique.

j'ai trouvé la solution pour être indépendant de la langue du blog, quelque soit la base de donnée.

• sqlite: il faut utiliser le mot clé root a la place de la locale.
• pgsql: il suffit d'utiliser une locale quelconque en utf8

En fait dans ces cas le tri se fais suivant l’algorithme UCA ( http://unicode.org/reports/tr10/) tout comme avec mysql avec utf8_unicode_ci.

Je te propose une première implémentation possible dans clearbricks a voir ici  https://bitbucket.org/Gvx_/clearbricks/commits/56acbed367b89f91dab897d767a9f34dcab10291 avant un éventuel pull request

[franck]

Je viens de regarder ton code, c'est une 1re étape qui m'intéresse.

Après vu l'usage qu'on en fait dans Dotclear je pense que ce serait pas mal de trouver un moyen d'appliquer sans se poser de question le collate (pour les tri) dès lors qu'on est sur un champs string, vu le peu de différence sur les temps de réponse, ça ne devrait pas vraiment être pénalisant.

Maintenant je n'ai pas vraiment considéré toutes les conséquences et possibilité, faut que je remette tout ça à plat.

You're welcome pour un PR / Clearbricks, évidemment

[franck <carnet.franck.paul@…>]

(In [9d2d67662a68]) Update CB — Gvx PR : addresses Ticket 2198