Wie hole ich Themen, sortiert nach dem Wert des benutzerdefinierten Feldes iso8601?

Entwickler
1

Hallo,

ich bin auf eine seltsame Abfrage gestoßen, bei der ich nicht recht weiß, wie ich sie nur mit einer SQL-/Active-Record-Abfrage lösen soll.

Ich habe Themen mit benutzerdefinierten Feldern und muss die Daten anhand des Wertes des benutzerdefinierten Feldes ‘importedSortDate’ sortieren.

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

Wie kann ich diese Abfrage so ändern oder ergänzen, dass ich nur diese 10 Themen in den Speicher lade und nicht mehr?

Da das Template für die Tabelle der benutzerdefinierten Felder etwas ungewöhnlich ist – im Sinne von, dass es eine Spalte für den Namen und eine für den Wert gibt –, bin ich etwas ratlos, wie ich das alles genau über die Datenbankabfrage bewerkstelligen soll.

Mein Problem beim Laden mehrerer Datensätze in den Speicher ist, dass für jedes geladene Thema zusätzlich ein Abruf der Kategorie, des Benutzers und des Beitrags durchgeführt wird.

Ich arbeite mit 1.000 bis 2.000 Themen pro Kategorie und habe vier Kategorien, aus denen ich abrufen muss. Der Abruf wird von allen Nutzern durchgeführt, es ist also keine einmalige Aktion.

Soweit ich das sehe, habe ich bisher zwei Möglichkeiten (die erste scheint für mich nicht schnell genug zu sein):

  1. Nur die IDs aller Themen abrufen, die mich interessieren (in einem Abruf), dann diese Information irgendwie mit dem Wert von ‘importedSortDate’ verknüpfen, dann in den Speichersortieren und anschließend erneut die IDs in der gleichen Reihenfolge abrufen (ich weiß auch hier nicht genau, wie das funktionieren würde).
  2. Eine Abfrage erstellen, die mit der Tabelle der benutzerdefinierten Felder verknüpft wird (ich habe einige Beispiele gesehen, diese bezogen sich jedoch auf feste Werte in den Spalten ‘name’ und ‘value’) und alles basierend auf den Spalten ‘name’ === ‘importedSortDate’ und der Wertespalte (ISO8601-Zeichenkette) sortieren.

Wie kann ich das erreichen?

PS: Manche Themen haben möglicherweise keinen Wert im benutzerdefinierten Feld ‘importedSortDate’. In diesem Fall könnte ich diese einfach abrufen und beliebig platzieren, was ein Randfall ist, mit dem ich umgehen kann. Wenn sie keinen Wert haben, sollten sie die ersten Einträge sein.

PPS: Ich möchte diese Abfrage später als meine Paginierungsabfrage verwenden. Die Logik dafür ist bereits implementiert; ich muss nur die Hauptabfrage zum Abrufen der Themen anpassen.

2

Das limit(10) sollte das bereits bewirken :slight_smile:

[quote=“Stefan_Brighiu_M, post:1, topic:166343”]
Die Daten müssen anhand des Werts des benutzerdefinierten Felds „importedSortDate

3

Oh…

Hmm, ok, das bedeutet also, dass ich statt ISO8601 Sekunden seit dem Epoch speichern muss, damit der String-Vergleich korrekt funktioniert und die Sortierung stimmt.

Ich war so besorgt darüber, den Wert von import_tcf nicht zu erhalten, dass ich komplett vergessen habe, auf rohes SQL zurückzugreifen…

4

Also … ich habe Folgendes erreicht:

 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") # Danke @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

Mit der Ausgabe:

{: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

Ich muss wahrscheinlich ein Skript ausführen, um alles in Zeitstempel umzuwandeln.

6

Du solltest dies ändern in:

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

für bessere Lesbarkeit.

Du sortierst nach sortDate, und wenn dieser fehlt, verwendest du createdAt. Alles in absteigender Reihenfolge. Die Abfrage gibt die Daten genau so zurück, wie angefordert, da 2020 „größer

8

Entschuldigung @Falco, ich habe herausgefunden, dass der Code korrekt war, und ihn so schnell wie möglich bearbeitet.

Edit:
Ich habe meinen Code vereinfacht zu:
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

Nur zur Sicherheit..

@david Bezüglich der Idee, in Zeitstempel umzuwandeln: Mein Problem dabei ist, dass nicht alle Kategorien dasselbe Modell für benutzerdefinierte Themenfelder haben. Das würde bedeuten, dass ich entweder für jedes Thema ein sort_date erstellen müsste oder es so lassen und einfach nach sort_date || t.created_at sortieren müsste.

Bei Zeitstempeln muss es alles oder nichts sein. :frowning:

9

@Falco @david, habt ihr eine Ahnung, welche Auswirkungen das Casten in ORDER BY auf die Leistung hat?

In diesem Thread sagt der Autor, dass die Leistung mit der Anzahl der zurückgegebenen Einträge nachlässt. Ich bin kein SQL-Guru, daher weiß ich nicht, ob das stimmt. (https://stackoverflow.com/a/491240/4020131)

Der Grund dafür ist, dass du bei einem char-Datentyp die Zeilen als Zeichenkette sortierst.

Der Ansatz, ORDER BY CAST() zu verwenden, ist zwar korrekt, aber die Leistung nimmt mit steigender Anzahl der zurückgegebenen Ergebnisse ab.

Wenn in dieser Spalte nur numerische Daten enthalten sind, wäre es am besten, einen geeigneten numerischen Datentyp zu finden und diesen zu ändern.

Wenn du die Spalte wirklich nicht ändern kannst und du Leistungsprobleme feststellst, empfehle ich dir, eine Spalte für die Sortierreihenfolge anzulegen, die den Wert als Integer enthält (wobei NULL-Werte in einen geeigneten Wert umgewandelt werden).

Erstelle einen Index auf der Sortierreihenfolge-Spalte und füge idealerweise einen Trigger auf der CHAR-Spalte hinzu, sodass Einfügungen oder Aktualisierungen des CHAR-Werts eine Aktualisierung des Integer-Werts auslösen.

Aus dem, was er sagt, verstehe ich, dass die Leistung gleich bleibt, wenn ich nur 10–20 Themen abrufen möchte, unabhängig davon, wie viele Themen in der Datenbank vorhanden sind.

Für mich ist das kontraintuitiv, denn wie soll es wissen, alle Themen zu sortieren und mir 10–20 zurückzugeben, wenn es nicht vorher castet?

Ich habe auch diesen Thread gefunden MSDN-Thread, aber ich verstehe nicht genau, wie er auf meinen Fall anwendbar ist – CAST in ORDER BY.

10

Schlecht. Wenn du vorhast, dies in einem kritischen Pfad abzufragen, ist es besser, in einer Migration deines Plugins eine neue Tabelle mit einem geeigneten Spaltentyp und Index hinzuzufügen.

11

Was ist mit dem Join der Tabelle für benutzerdefinierte Felder, bei dem ich nur nach Einträgen suche, die eine topic_id haben? @Falco, betrifft das nicht auch die Leistung?

12

Ich habe gerade darüber nachgedacht… Das Ziel ist es doch, die Dinge in chronologischer Reihenfolge zu bekommen, oder? Wenn man sich das Format YYYY-MM-DDTHH:MM:SS von ISO8601-Daten betrachtet, denke ich, dass man sie tatsächlich „alphabetisch" sortieren kann und es trotzdem chronologisch bleibt.

Ohne das Casting sollte PostgreSQL meiner Meinung nach in der Lage sein, den Index zu verwenden, den wir auf (name, value) haben, und das sollte viel effizienter sein.

13

Aber was ist mit dem create_at-Teil? Ich möchte, dass sortDate verwendet wird, wenn es vorhanden ist, und created_at, wenn nicht.

14

Warte, vergleicht ISO8601 nicht bereits korrekt als String? Ist das nicht einer der Hauptgründe?

Ja.

15

Das Problem ist, dass ich eine Kreuzsortierung mit sortDate (wo ich es habe) und created_at (wo ich es nicht habe) durchführen möchte, ohne Performance-Verlust.

Mein Problem ist, dass der Wert für das benutzerdefinierte Feld ein STRING ist, während created_at vom Typ DATE ist.

16

Das könnte man wahrscheinlich versuchen.
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=The%20TO_DATE%20function%20in%20PostgreSQL,to_timestamp(text%2C%20text).

17

Ich habe eine Lösung gefunden, aber sie erfordert einen Cast – entweder vom String zum Datum (da sortDate ein benutzerdefinierter Feldwert als String ist) oder vom Datum zum String für created_at.

Ich suchte nach einer Lösung, die weniger aufwendig ist und keine zusätzlichen Arbeiten wie DB-Migrationsskripte erfordert.

Ich bin mir nicht sicher, wie Discourse benutzerdefinierte Tabellen im Hinblick auf Rebase-Operationen und Datenbank-Upgrades behandelt.

18

Ich werde dies in der Zukunft höchstwahrscheinlich nachholen, indem ich das sortDate für alle Einträge ausfülle. Es wäre eine elegantere Lösung, wenn ich dies direkt am Topic selbst einstellen könnte, aber ich weiß erneut nicht, wie sich das auf die Discourse-Updates auswirken würde.

Gibt es hilfreiche Links oder Tipps zu benutzerdefinierten Tabellen und dazu, wie man Probleme vermeidet, wenn man sie verwendet und Discourse aktualisiert?

19

Benutzerdefinierte Tabellen sind weitaus sicherer als das Hinzufügen oder Ändern von Feldern in den vorhandenen Tabellen. Discourse wird Ihre Tabellen nicht verändern, aber Sie müssen möglicherweise einige Arbeiten leisten, um mit den Änderungen im Kern Schritt zu halten.

discourse-subscriptions und discourse-calendar sind gute Beispiele für offizielle Plugins, die ihre eigenen Tabellen haben.

20

@fzngagan Ich habe Discourse vor 3–4 Monaten zusammen mit Ruby angefangen zu nutzen :slight_smile:. Wenn du von „ein bisschen Arbeit

21

Wenn du feststellst, dass etwas kaputtgeht (und du musst das regelmäßig prüfen), musst du es weiterhin reparieren. Außerdem kannst du einige Unit-Tests schreiben, die dir helfen, die Probleme leicht zu finden.