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

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

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

[图片] 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…

[图片] 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?

[图片] 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. W…

如何按自定义字段 iso8601 值获取主题？

开发

fzngagan (Faizaan Gagan) 2020 年10 月 11 日 16:06 21

如果你发现某些内容出错了（你需要定期检查），你就必须持续修复它。此外，你可以编写一些单元测试，这将帮助你轻松定位问题。

话题		回复	浏览量
Improve performance of Topic load - ORDER BY RANDOM Bug slow-sql	22	4627	2015 年3 月 1 日
Retrieve Topics based on custom field? Development	12	2165	2022 年3 月 17 日
API topic post creation ordered by updated_at or created_at? Support	7	1342	2018 年4 月 12 日
How can I sort the topic replies by date Migration rest-api	7	1264	2022 年9 月 8 日
Request time - vote_count Feature topic-voting	13	2042	2017 年8 月 1 日

如何按自定义字段 iso8601 值获取主题？

相关话题