Hi, I’ve come across a weird query which i don’t really know how to wrap my head around doing only with a sql/active record query. I have topics with custom fields, and i need to sort data using the value of ‘importedSortDate’ custom field. Initial code Topic.where(‘category_id in (?)’, [cat_id]…

[immagine] Stefan_Brighiu_M: How can I modify or add to this query so I load into memory only those 10 topics, and not more. The limit(10) should do that already :slight_smile: [immagine] Stefan_Brighiu_M: need to sort data using the value of ‘importedSortDate’ custom field This kind …

Oh… Hmm, ok so this basically means instead of the iso8601, I need to save a seconds since epoch, so that the string compare is correct and sorts properly. I was so concerned about not being able to get the value of import_tcf that i totally forgot to go with raw sql…

Well … I got here: 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…

I probably need to run a script to convert everything to timestamps

[immagine] Stefan_Brighiu_M: .order("case when custom_fields.value IS NULL then topics.created_at else cast(custom_fields.value as timestamp) end desc") You will want to change this into: .order("COALESCE(CAST(custom_fields.value AS timestamp), custom_fields.value) DESC") for readabilit…

Sorry @Falco I figured out that the code was correct and edited as fast as I could. Edit: I’ve simplified my code to: .order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”) Just to be safe.. @david Regarding the convert to timestamps idea. My problem with that is not…

@Falco @david any idea what the performance implications are for casting in ORDER BY? in this thread, the author says that the performance will degrade with the returned items number. I’m not a SQL guru so I don’t know if this is true. ( https://stackoverflow.com/a/491240/4020131 ) The reason for th…

[immagine] Stefan_Brighiu_M: any idea what the performance implications are for casting in ORDER BY? Bad. If you plan to query on this in a hot path, you may as well add a new table with a proper column type and index in a migration on your plugin.

What about the joining of the custom fields table, where i search for only the entries that have topic_id? @Falco doesn’t that affect performance as well?

[immagine] Stefan_Brighiu_M: casting in ORDER BY I was just thinking… the aim here is to get things in chronological order, right? Looking at the YYYY-MM-DDTHH:MM:SS format of ISO8601 dates, I think you can actually get away with sorting them ‘alphabetically’, and it will still be chronologic…

Come recuperare argomenti ordinati per valore del campo personalizzato iso8601?

Sviluppatore

fzngagan (Faizaan Gagan) 11 Ottobre 2020, 4:06pm 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.

Argomento		Risposte	Visualizzazioni
Improve performance of Topic load - ORDER BY RANDOM Bug slow-sql	22	4628	Marzo 1, 2015
Retrieve Topics based on custom field? Development	12	2165	Marzo 17, 2022
API topic post creation ordered by updated_at or created_at? Support	7	1342	Aprile 12, 2018
How can I sort the topic replies by date Migration rest-api	7	1264	Settembre 8, 2022
Request time - vote_count Feature topic-voting	13	2043	Agosto 1, 2017

Come recuperare argomenti ordinati per valore del campo personalizzato iso8601?

Argomenti correlati