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]…

[imagem] 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: [imagem] Stefan_Brighiu_M: need to sort data using the value of ‘importedSortDate’ custom field This kind of t…

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

[imagem] 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 readability. …

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…

[imagem] 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?

[imagem] 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 chronological…

Como buscar tópicos ordenados pelo valor do campo personalizado iso8601?

Dev

fzngagan (Faizaan Gagan) Outubro 11, 2020, 4:06pm 21

Se você perceber algo quebrando (e precisará verificar regularmente), terá que continuar corrigindo. Além disso, você pode escrever alguns testes unitários que ajudarão a localizar os problemas facilmente.

Tópico		Respostas	Visualizações
Improve performance of Topic load - ORDER BY RANDOM Bug slow-sql	22	4628	1 de Março de 2015
Retrieve Topics based on custom field? Development	12	2165	17 de Março de 2022
API topic post creation ordered by updated_at or created_at? Support	7	1342	12 de Abril de 2018
How can I sort the topic replies by date Migration rest-api	7	1264	8 de Setembro de 2022
Request time - vote_count Feature topic-voting	13	2043	1 de Agosto de 2017

Como buscar tópicos ordenados pelo valor do campo personalizado iso8601?

Tópicos relacionados