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

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

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

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

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

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

How to fetch topics ordered by custom field iso8601 value?

Development

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

If you see something breaking (and you’ll need to check regularly) you’ll have to keep fixing it. Also, you can write some unit tests which will help you locate the issues easily.

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

How to fetch topics ordered by custom field iso8601 value?

Related topics