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

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

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

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

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

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

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

Entwickler

david (David Taylor) 6. Oktober 2020 um 10:27 2

Das limit(10) sollte das bereits bewirken

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

Thema		Antworten	Aufrufe
Improve performance of Topic load - ORDER BY RANDOM Bug slow-sql	22	4627	1. März 2015
Retrieve Topics based on custom field? Development	12	2165	17. März 2022
API topic post creation ordered by updated_at or created_at? Support	7	1342	12. April 2018
How can I sort the topic replies by date Migration rest-api	7	1264	8. September 2022
Request time - vote_count Feature topic-voting	13	2042	1. August 2017

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

Verwandte Themen