Come recuperare argomenti ordinati per valore del campo personalizzato iso8601?

Sviluppatore
1

Ciao,

Mi sono imbattuto in una query strana su cui non riesco proprio a mettere la testa, realizzabile solo con una query SQL/Active Record.

Ho argomenti con campi personalizzati e devo ordinare i dati in base al valore del campo personalizzato ‘importedSortDate’.

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

Come posso modificare o aggiungere a questa query in modo da caricare in memoria solo quei 10 argomenti e non di più?

Dato che il modello della tabella dei Campi Personalizzati è un po’ strano, nel senso che hai una colonna ‘name’ e una colonna ‘value’, sono un po’ bloccato su come gestire esattamente tutto questo tramite una query al database.

Il mio problema nel caricare di più in memoria è il fatto che per ogni argomento caricato in memoria vengono eseguiti anche dei fetch per la categoria, l’utente e il post.

Sto lavorando con 1000-2000 argomenti per categoria e ho 4 categorie da cui recuperare i dati.
Il recupero viene effettuato da tutti gli utenti, non è un’operazione una tantum.

Come vedo le cose, finora ho due modi per farlo (il primo non sembra abbastanza veloce per me):

  1. Ottenere solo gli ID di tutti gli argomenti che mi interessano in un’unica fetch e poi usare quelle informazioni per unire in qualche modo il valore di ‘importedSortDate’, quindi fare un ordinamento in memoria e successivamente eseguire di nuovo una fetch per gli ID nello stesso ordine in cui li ho (ancora non so esattamente come funzionerebbe)
  2. In qualche modo creare una query che fa un join con la tabella dei campi personalizzati (ho visto alcuni esempi, ma erano per valori fissi nelle colonne ‘name’ e ‘value’) e ordinare tutto in base alle colonne ‘name’ === ‘importedSortDate’ e alla colonna ‘value’ (stringa ISO8601)

Come posso ottenere questo risultato?

PS Alcuni argomenti potrebbero non avere un valore nel campo personalizzato ‘importedSortDate’, quindi potrei semplicemente recuperarli e posizionarli dove voglio, ma è un caso limite con cui posso gestire. Se non hanno un valore, dovrebbero essere le prime voci.

PPS In seguito voglio usare questa query come mia query di paginazione; la logica è già implementata da quella parte, devo solo cambiare la query principale per il recupero degli argomenti.

2

Il limit(10) dovrebbe già farlo :slight_smile:

Una cosa del genere dovrebbe funzionare: (ma non testata)

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)

Il problema è che chiedere a PostgreSQL di convertire la stringa della data in un timestamp sarà molto inefficiente. Dovrà eseguire la conversione per ogni singolo argomento prima di selezionare i primi 10.

Ci sono alcune opzioni a cui posso pensare: potresti memorizzare le date come “secondi dall’epoca” invece che in formato ISO8601. Questo le renderà molto più facili da ordinare per PostgreSQL. Oppure potresti essere in grado di utilizzare una migrazione del database per aggiungere un indice alla tabella topic_custom_fields per TIMESTAMP value.

3

Oh…

Hmm, ok, quindi questo significa che invece dell’iso8601, devo salvare i secondi dall’epoca, in modo che il confronto delle stringhe sia corretto e l’ordinamento funzioni bene.

Ero così preoccupato di non riuscire a ottenere il valore di import_tcf che ho completamente dimenticato di usare raw sql…

4

Bene… sono arrivato qui:

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

Con l’output:

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

Probabilmente devo eseguire uno script per convertire tutto in timestamp

6

Dovresti trasformarlo in:

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

per una migliore leggibilità.

Ordini per sortDate e, quando manca, usi createdAt. Tutto in ordine decrescente. La query restituisce i dati esattamente come richiesto, poiché il 2020 è “maggiore” del secondo posto del 2011.

Se desideri che i valori null appaiano per ultimi, devi usare:

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

Scusa @Falco, ho capito che il codice era corretto e l’ho modificato il più velocemente possibile.

Modifica:
Ho semplificato il mio codice in:
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

Solo per sicurezza..

@david Riguardo all’idea di convertire in timestamp. Il mio problema è che non tutte le categorie hanno lo stesso modello di campi personalizzati per i topic. Questo significherebbe che dovrei creare un sort_date per ogni topic, oppure mantenerlo così com’è e ordinare semplicemente per sort_date || t.created_at.

Con i timestamp deve essere tutto o niente. :frowning:

9

@Falco @david avete idea di quali siano le implicazioni sulle prestazioni dell’uso del casting in ORDER BY?

In questo thread, l’autore afferma che le prestazioni peggiorano all’aumentare del numero di elementi restituiti. Non sono un guru del SQL, quindi non so se sia vero. (https://stackoverflow.com/a/491240/4020131)

Il motivo è che, con un tipo di dato char, le righe vengono ordinate come stringhe.

L'idea di usare ORDER BY CAST() è corretta, tuttavia le prestazioni diminuiranno all'aumentare del numero di risultati restituiti.

Se in questa colonna ci sono solo dati numerici, la migliore pratica sarebbe trovare un tipo di dato numerico adatto e modificarlo.

Se davvero non potete modificare la colonna e riscontrate problemi di prestazioni, vi consiglio di aggiungere una colonna per l'ordine di ordinamento che contenga il valore castato a intero (con i valori null convertiti in un valore appropriato).

Create un indice sulla colonna di ordinamento e, idealmente, aggiungete un trigger alla colonna CHAR in modo che gli inserimenti o gli aggiornamenti del valore CHAR attivino un aggiornamento del valore intero.

Da quanto dice, capisco che se voglio solo 10-20 argomenti, le prestazioni rimarranno invariate, indipendentemente da quanti argomenti ci sono nel database.

Per me questo è controintuitivo: come fa a sapere di ordinare tutti gli argomenti e restituirmene 10-20 se non esegue il casting in anticipo?

Ho anche trovato questo thread thread MSDN, ma non capisco esattamente come si applichi al mio caso: CAST in ORDER BY

10

Negative. Se prevedi di eseguire query su questo in un percorso critico, potresti anche aggiungere una nuova tabella con un tipo di colonna appropriato e un indice in una migrazione del tuo plugin.

11

E per quanto riguarda l’unione della tabella dei campi personalizzati, dove cerco solo le voci che hanno topic_id? @Falco, non influisce anche questo sulle prestazioni?

12

Stavo pensando… l’obiettivo qui è ottenere gli elementi in ordine cronologico, giusto? Considerando il formato YYYY-MM-DDTHH:MM:SS delle date ISO8601, penso che si possa effettivamente ordinare in modo “alfabetico” mantenendo comunque l’ordine cronologico.

Senza il casting, credo che PostgreSQL dovrebbe essere in grado di utilizzare l’indice che abbiamo su (name, value), rendendo l’operazione molto più efficiente.

13

Ma che dire della parte create_at? Vorrei che usasse sortDate quando è disponibile e created_at quando non lo è.

14

Aspetta, ma ISO8601 non confronta già correttamente le stringhe? Non è questa una delle sue caratteristiche principali?

Sì.

15

Il problema è che voglio eseguire un ordinamento incrociato utilizzando sortDate (dove ce l’ho) e created_at (dove non ce l’ho), senza perdere prestazioni.

Il mio problema è che il valore di un campo personalizzato è una STRINGA, mentre created_at è di tipo DATA.

16

Potresti provare questo.
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=La%20funzione%20TO_DATE%20in%20PostgreSQL,to_timestamp(testo,%20testo).

17

Ho trovato un modo per farlo, ma comporta un casting, sia da stringa a data (poiché sortDate è un valore di campo personalizzato in formato stringa) sia convertendo created_at da data a stringa.

Stavo cercando una soluzione meno onerosa che non richiedesse lavoro aggiuntivo come script di migrazione del database.

Non sono sicuro di come Discourse gestisca le tabelle personalizzate in termini di rebase e aggiornamento del database.

18

Rivederò questo in futuro, molto probabilmente compilando la sortDate per tutti gli elementi. Sarebbe una soluzione migliore se potessi impostarla direttamente sul Topic, ma non so ancora come questo potrebbe influenzare gli aggiornamenti di Discourse.

Hai qualche link utile o consiglio su come creare tabelle personalizzate e come evitare problemi quando si utilizzano e si aggiornano le versioni di Discourse?

19

Le tabelle personalizzate sono molto più sicure rispetto all’aggiunta o modifica di campi nelle tabelle esistenti. Discourse non interferirà con le tue tabelle, ma potresti dover svolgere alcune attività per stare al passo con le modifiche nel core.

discourse-subscriptions e discourse-calendar sono ottimi esempi di plugin ufficiali che dispongono delle proprie tabelle.

20

@fzngagan Ho iniziato a usare Discourse 3-4 mesi fa, insieme a Ruby :slight_smile:. Quando dici “fare un po’ di lavoro”, a quali passaggi esatti ti riferisci? Si tratta di scrivere, ad esempio, uno script di migrazione che crea la tabella se non esiste? Se puoi aiutarmi con una breve lista, mi semplificherebbe molto il lavoro di documentazione :expressionless:

Grazie in anticipo

21

Se noti qualcosa che si rompe (e dovrai controllarlo regolarmente), dovrai continuare a risolverlo. Inoltre, puoi scrivere alcuni test unitari che ti aiuteranno a individuare facilmente i problemi.