Comment récupérer les sujets triés par la valeur du champ personnalisé iso8601 ?

Dev
1

Bonjour,

Je suis tombé sur une requête étrange dont je ne sais pas vraiment comment m’acquitter uniquement avec une requête SQL ou Active Record.

J’ai des sujets avec des champs personnalisés, et je dois trier les données en utilisant la valeur du champ personnalisé ‘importedSortDate’.

Code initial
Topic.where(‘category_id in (?)’, [cat_id])
.where(‘closed = ?’, false)
.order(‘created_at desc’)
.limit(10)

Comment puis-je modifier ou ajouter à cette requête pour charger en mémoire uniquement ces 10 sujets, et pas plus ?

Étant donné que le modèle de la table des champs personnalisés est un peu étrange, dans le sens où vous avez une colonne nom et une colonne valeur, je suis un peu bloqué sur la façon exacte de réaliser cela tout en utilisant la requête de base de données.

Mon problème avec le chargement de plus de données en mémoire est le fait que pour chaque sujet que je charge en mémoire, un fetch de catégorie, d’utilisateur et de publication est également effectué.

Je travaille avec 1000 à 2000 sujets par catégorie, et j’ai 4 catégories à récupérer.
Le fetch est effectué par tous les utilisateurs, ce n’est pas une opération ponctuelle.

À ma connaissance, j’ai jusqu’à présent deux façons de procéder (la première ne me semble pas assez rapide) :

  1. Récupérer uniquement les IDs de tous les sujets qui m’intéressent en un seul fetch, puis utiliser ces informations pour les joindre d’une manière ou d’une autre à la valeur de ‘importedSortDate’, effectuer un tri en mémoire, puis à nouveau récupérer les IDs dans le même ordre (je ne sais pas exactement comment cela fonctionnerait).
  2. D’une manière ou d’une autre, créer une requête qui joint la table des champs personnalisés (j’ai vu quelques exemples, mais ils concernaient des valeurs fixes pour les colonnes nom et valeur) et trier le tout en fonction des colonnes ‘name’ === ‘importedSortDate’ et de la colonne ‘value’ (chaîne iso8601).

Comment puis-je réaliser cela ?

PS : Certains sujets peuvent ne pas avoir de valeur dans le champ personnalisé importedSortDate, je pourrais donc simplement les récupérer et les placer où je veux, mais c’est un cas limite que je peux gérer. S’ils n’ont pas de valeur, ils doivent être les premières entrées.

PPS : Je souhaite ensuite utiliser cette requête comme requête de pagination, la logique étant déjà implémentée de ce côté-là. J’ai simplement besoin de modifier la requête principale pour récupérer les sujets.

2

Le limit(10) devrait déjà faire cela :slight_smile:

Ce genre de solution devrait fonctionner (mais non testé) :

Topic.where("category_id in (?)", [cat_id])
         .where("closed = ?", false)
         .joins("LEFT JOIN topic_custom_fields import_tcf ON import_tcf.topic_id = topic.id AND topic_custom_fields.name = 'importedSortDate'")
         .order("TIMESTAMP import_tcf.value")
         .order("created_at desc")
         .limit(10)

Le problème est que demander à PostgreSQL de convertir une chaîne de date en horodatage sera très inefficace. Il devra effectuer la conversion pour chaque sujet individuellement, avant de sélectionner les 10 premiers.

Il y a quelques options qui me viennent à l’esprit : vous pourriez stocker les dates en « secondes depuis l’époque » plutôt qu’en format ISO8601. Cela les rendra beaucoup plus faciles à trier pour PostgreSQL. Ou vous pourriez être en mesure d’utiliser une migration de base de données pour ajouter un index à la table topic_custom_fields pour TIMESTAMP value.

3

Oh…

Hmm, d’accord, donc cela signifie qu’au lieu de l’iso8601, je dois enregistrer le nombre de secondes depuis l’époque, afin que la comparaison de chaînes soit correcte et que le tri fonctionne comme prévu.

J’étais tellement préoccupé par l’impossibilité d’obtenir la valeur de import_tcf que j’ai complètement oublié de passer par du SQL brut…

4

Eh bien … j’ai obtenu ceci :

 topics = Topic.where("topics.category_id in (?)", [7])
    .where("topics.closed = ?", false)
    .joins("LEFT JOIN topic_custom_fields custom_fields ON custom_fields.topic_id = topics.id AND custom_fields.name = '#{Constants::TOPIC_SORT_DATE}'")
    .order("coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc") # Merci @falco
  # .limit(10)

  array = topics.to_a.map do |t|
    next { id: t.id, createdAt: t.created_at, sortDate: t.custom_fields[Constants::TOPIC_SORT_DATE] }
  end
  puts array

Avec le résultat suivant :

{:id=>25, :createdAt=>Thu, 14 May 2020 09:26:47 UTC +00:00, :sortDate=>nil}
{:id=>7017, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7016, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7058, :createdAt=>Tue, 06 Oct 2020 15:39:49 UTC +00:00, :sortDate=>"2010-02-02T00:00:00Z"}
{:id=>7008, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7010, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7011, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7012, :createdAt=>Tue, 06 Oct 2020 07:49:07 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7013, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7014, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7015, :createdAt=>Tue, 06 Oct 2020 07:49:09 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7003, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7004, :createdAt=>Tue, 06 Oct 2020 07:49:02 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7005, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7006, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7007, :createdAt=>Tue, 06 Oct 2020 07:49:04 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7009, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7059, :createdAt=>Tue, 06 Oct 2020 15:49:16 UTC +00:00, :sortDate=>"2009-02-02T00:00:00Z"}
{:id=>7002, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7001, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6999, :createdAt=>Tue, 06 Oct 2020 07:48:59 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6998, :createdAt=>Tue, 06 Oct 2020 07:48:58 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7000, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
5

Je devrai probablement exécuter un script pour tout convertir en horodatages.

6

Vous voudrez transformer ceci en :

.order("COALESCE(CAST(custom_fields.value AS timestamp), custom_fields.value) DESC")

pour plus de lisibilité.

Vous triez par sortDate, et lorsque celle-ci est absente, vous utilisez createdAt. Le tout dans l’ordre décroissant. La requête renvoie les données exactement comme demandé, car 2020 est « supérieur » à la deuxième place de 2011.

Si vous voulez que les valeurs NULL soient placées en dernier, vous devez utiliser :

.order("CAST(custom_fields.value AS timestamp) DESC NULLS LAST")
8

Désolé @Falco, j’ai compris que le code était correct et je l’ai édité le plus rapidement possible.

Édition :
J’ai simplifié mon code pour :
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

Juste pour être prudent..

@david Concernant l’idée de convertir en horodatages. Mon problème avec cela est que toutes les catégories n’ont pas le même modèle de champs personnalisés pour les sujets. Cela signifierait que je devrais soit créer une date de tri pour chaque sujet, soit le laisser tel quel et simplement trier par sort_date || t.created_at.

Avec les horodatages, il faut que ce soit tout ou rien. :frowning:

9

@Falco @david, avez-vous une idée des implications sur les performances du cast dans une clause ORDER BY ?

Dans ce fil de discussion, l’auteur indique que les performances se dégradent avec le nombre d’éléments retournés. Je ne suis pas un expert SQL, donc je ne sais pas si c’est vrai. (https://stackoverflow.com/a/491240/4020131)

La raison en est que, avec un type de données char, vous triez les lignes comme des chaînes de caractères.

L'idée d'utiliser ORDER BY CAST() est correcte, mais les performances de cette méthode diminueront à mesure que le nombre de résultats retournés augmente.

Si cette colonne ne contient que des données numériques, la meilleure pratique consiste à trouver un type de données numérique approprié et à le modifier.

Si vous ne pouvez vraiment pas modifier la colonne et que vous rencontrez des problèmes de performances, je suggère d'ajouter une colonne d'ordre de tri contenant la valeur castée en entier (avec les valeurs NULL converties en une valeur appropriée).

Indexez cette colonne d'ordre de tri et, idéalement, ajoutez un déclencheur sur la colonne CHAR afin que les insertions ou mises à jour de la valeur char déclenchent une mise à jour de la valeur entière.

D’après ce qu’il dit, je comprends que si je ne veux récupérer que 10 à 20 sujets, les performances resteront stables, peu importe le nombre de sujets présents dans la base de données.

Pour moi, c’est contre-intuitif : comment saura-t-il trier tous les sujets et me renvoyer 10 à 20 si le cast n’est pas effectué au préalable ?

J’ai également trouvé ce fil fil MSDN, mais je ne comprends pas exactement comment cela s’applique à mon cas : le CAST dans ORDER BY.

10

Négatif. Si vous prévoyez d’interroger cela dans un chemin critique, vous feriez mieux d’ajouter une nouvelle table avec un type de colonne approprié et un index lors d’une migration de votre plugin.

11

Qu’en est-il de la jointure de la table des champs personnalisés, où je ne recherche que les entrées ayant un topic_id ? @Falco, cela n’affecte-t-il pas également les performances ?

12

Je me disais… l’objectif ici est bien d’obtenir les éléments dans l’ordre chronologique, n’est-ce pas ? En regardant le format YYYY-MM-DDTHH:MM:SS des dates ISO8601, je pense qu’on peut tout à fait se contenter de les trier « alphabétiquement », et cela restera chronologique.

Sans le cast, je pense que Postgres devrait pouvoir utiliser l’index que nous avons sur (name, value), ce qui devrait être beaucoup plus efficace.

13

Mais qu’en est-il de la partie create_at ? J’aimerais qu’il utilise sortDate lorsqu’il est disponible, et created_at dans le cas contraire.

14

Attends, l’ISO8601 ne permet-elle pas déjà une comparaison de chaînes correcte ? N’est-ce pas là tout l’intérêt ?

Oui.

15

Le problème est que je veux effectuer un tri croisé en utilisant sortDate (là où je l’ai) et created_at (là où je ne l’ai pas), sans perte de performance.

Mon problème est que la valeur d’un champ personnalisé est une chaîne de caractères (STRING) tandis que created_at est de type DATE.

16

Vous pouvez probablement essayer ceci.
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=The%20TO_DATE%20function%20in%20PostgreSQL,to_timestamp(text%2C%20text).

17

J’ai trouvé une façon de faire, mais cela implique un cast, soit de string vers date (sortDate étant une valeur de champ personnalisé de type string), soit de created_at de date vers string.

Je cherchais une solution moins exigeante qui ne nécessitait pas de travail supplémentaire comme des scripts de migration de base de données.

Je ne suis pas sûr de la façon dont Discourse gère les tables personnalisées en ce qui concerne le rebase et la mise à niveau de la base de données.

18

Je reviendrai là-dessus dans le futur en remplissant très probablement la date de tri pour tous les éléments. Ce serait une solution plus élégante si je pouvais le définir directement sur le sujet, mais je ne sais pas à nouveau comment cela affecterait les mises à jour de Discourse.

Des liens utiles ou des conseils sur les tableaux personnalisés et sur la manière d’éviter les problèmes lors de leur utilisation et des mises à jour de Discourse ?

19

Les tables personnalisées sont beaucoup plus sûres que l’ajout ou la modification de champs dans les tables existantes. Discourse ne touchera pas à vos tables, mais vous devrez peut-être effectuer certains travaux pour suivre les évolutions du cœur du système.

discourse-subscriptions et discourse-calendar sont de bons exemples de plugins officiels disposant de leurs propres tables.

20

@fzngagan J’ai commencé à utiliser Discourse il y a 3 à 4 mois, ainsi que Ruby :slight_smile:. Quand tu dis « faire un peu de travail », quelles sont les étapes exactes auxquelles tu fais référence ? S’agit-il d’écrire un script de migration qui crée la table si elle n’existe pas ? Si tu peux m’aider avec une petite liste, cela me faciliterait vraiment la tâche pour la documentation :expressionless:

Merci d’avance

21

Si vous constatez un dysfonctionnement (et vous devrez vérifier régulièrement), vous devrez continuer à le corriger. De plus, vous pouvez écrire des tests unitaires qui vous aideront à localiser facilement les problèmes.