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

开发
1

你好，

我遇到了一个奇怪的查询问题，不太清楚如何仅通过 SQL 或 Active Record 查询来实现。

我的主题包含自定义字段，我需要根据名为 ‘importedSortDate’ 的自定义字段的值对数据进行排序。

初始代码：
Topic.where(‘category_id in (?)’, [cat_id])
.where(‘closed = ?’, false)
.order(‘created_at desc’)
.limit(10)

我该如何修改或添加此查询，以便仅将 10 个主题加载到内存中，而不多加载？

由于自定义字段表的结构有点特殊（包含 name 列和 value 列），我在如何仅通过数据库查询完成此操作方面有些困惑。

我担心在内存中加载过多数据的原因是：每加载一个主题，还会额外执行一次类别、用户和帖子的获取操作。

每个类别下有 1000-2000 个主题，我需要从 4 个类别中获取数据。
此操作由所有用户执行，并非一次性任务。

目前我认为有两种实现方式（第一种对我来说似乎不够快）：

  1. 一次性获取所有我感兴趣的主题 ID，然后利用这些信息与 ‘importedSortDate’ 的值进行某种连接，在内存中排序，再按照排序后的顺序再次获取这些 ID（但我也不太清楚具体如何实现）。
  2. 构建一个查询，将其与自定义字段表进行连接（我见过一些示例，但那些是针对 name 和 value 列固定值的），然后根据 name 列等于 ‘importedSortDate’ 且按 value 列（ISO8601 字符串）进行排序。

我该如何实现这一点？

附注：某些主题可能在 ‘importedSortDate’ 自定义字段中没有值，我可以单独获取这些主题并按需放置，但这属于我可以处理的边缘情况。如果没有值，它们应排在最前面。

再附注：我之后希望将此查询用作分页查询。分页逻辑已实现，我只需要更改获取主题的主查询即可。

2

limit(10) 应该已经可以实现这一点 :slight_smile:

类似这样的代码应该可以（但未经测试）：

Topic.where("category_id in (?)", [cat_id])
         .where("closed = ?", false)
         .joins("LEFT JOIN topic_custom_fields import_tcf ON import_tcf.topic_id = topic.id AND topic_custom_fields.name = 'importedSortDate'")
         .order("TIMESTAMP import_tcf.value")
         .order("created_at desc")
         .limit(10)

问题在于，要求 PostgreSQL 将日期字符串转换为时间戳会非常低效。它必须对每个主题先进行转换，然后再选取前 10 个。

我想到有几个解决方案：你可以将日期存储为“自纪元以来的秒数”，而不是 ISO8601 格式。这将使 PostgreSQL 对其排序容易得多。或者，你也可以使用数据库迁移为 topic_custom_fields 表的 TIMESTAMP value 添加索引。

3

哦……

嗯，好的，所以这基本上意味着我不再使用 ISO8601 格式，而是需要保存自纪元以来的秒数，这样字符串比较才能正确，排序也能正常。

我太担心无法获取 import_tcf 的值，以至于完全忘了直接使用原生 SQL。

4

好吧……我得到了以下结果：

 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_fields.value as timestamp), topics.created_at) desc") # 感谢 @falco
  # .limit(10)

  array = topics.to_a.map do |t|
    next { id: t.id, createdAt: t.created_at, sortDate: t.custom_fields[Constants::TOPIC_SORT_DATE] }
  end
  puts array

输出结果如下：

{:id=>25, :createdAt=>Thu, 14 May 2020 09:26:47 UTC +00:00, :sortDate=>nil}
{:id=>7017, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7016, :createdAt=>Tue, 06 Oct 2020 07:49:10 UTC +00:00, :sortDate=>"2011-01-02T22:00:00.000Z"}
{:id=>7058, :createdAt=>Tue, 06 Oct 2020 15:39:49 UTC +00:00, :sortDate=>"2010-02-02T00:00:00Z"}
{:id=>7008, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7010, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7011, :createdAt=>Tue, 06 Oct 2020 07:49:06 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7012, :createdAt=>Tue, 06 Oct 2020 07:49:07 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7013, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7014, :createdAt=>Tue, 06 Oct 2020 07:49:08 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7015, :createdAt=>Tue, 06 Oct 2020 07:49:09 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7003, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7004, :createdAt=>Tue, 06 Oct 2020 07:49:02 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7005, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7006, :createdAt=>Tue, 06 Oct 2020 07:49:03 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7007, :createdAt=>Tue, 06 Oct 2020 07:49:04 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7009, :createdAt=>Tue, 06 Oct 2020 07:49:05 UTC +00:00, :sortDate=>"2010-01-02T22:00:00.000Z"}
{:id=>7059, :createdAt=>Tue, 06 Oct 2020 15:49:16 UTC +00:00, :sortDate=>"2009-02-02T00:00:00Z"}
{:id=>7002, :createdAt=>Tue, 06 Oct 2020 07:49:01 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7001, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6999, :createdAt=>Tue, 06 Oct 2020 07:48:59 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>6998, :createdAt=>Tue, 06 Oct 2020 07:48:58 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
{:id=>7000, :createdAt=>Tue, 06 Oct 2020 07:49:00 UTC +00:00, :sortDate=>"2009-01-02T22:00:00.000Z"}
5

我可能需要运行一个脚本来将所有内容转换为时间戳

6

您应该将其改为：

.order("COALESCE(CAST(custom_fields.value AS timestamp), custom_fields.value) DESC")

以提高可读性。

您按 sortDate 排序，如果该字段缺失，则使用 createdAt。所有排序均为降序。查询返回的数据完全符合您的要求，因为 2020 年“大于”第二名的 2011 年。

如果您希望空值排在最后，需要：

.order("CAST(custom_fields.value AS timestamp) DESC NULLS LAST")
8

抱歉，@Falco，我已经确认代码是正确的，并尽快进行了编辑。

编辑：
我将代码简化为：
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

为了保险起见……

@david 关于转换为时间戳的想法。我的问题在于并非所有类别都拥有相同的主题自定义字段模型。这意味着我不得不为每个主题创建一个 sort_date，或者保持现状，仅按 sort_date || t.created_at 进行排序。

如果使用时间戳，则必须全部统一或完全不使用。:frowning:

9

@Falco @david 有人知道在 ORDER BY 中进行类型转换会有什么性能影响吗？

在这个帖子中，作者表示性能会随着返回结果数量的增加而下降。我不是 SQL 专家，所以不确定这是否属实。（https://stackoverflow.com/a/491240/4020131）

原因是，对于 char 数据类型，排序是按字符串进行的。

使用 ORDER BY CAST() 的思路是正确的，但随着返回结果数量的增加，其性能会下降。

如果该列中仅包含数值数据，最佳做法是找到合适的数值数据类型并更改它。

如果你确实无法更改该列，并且遇到了性能问题，我建议添加一个排序顺序列，其中包含转换为整数的值（空值转换为适当的值）。

为该排序顺序列建立索引，理想情况下，为 CHAR 列添加触发器，以便对 CHAR 值进行插入或更新时触发对整数值的更新。

根据他的说法，我理解为：如果我只需要 10-20 个主题，那么无论数据库中有多少主题，性能都会保持不变。

对我来说这有些反直觉，因为如果它不事先进行类型转换，它怎么知道要对所有主题进行排序并返回 10-20 个呢？

我还找到了这个帖子 MSDN 帖子，但我不太清楚它如何具体适用于我的情况——在 ORDER BY 中使用 CAST。

10

很差。如果你打算在关键路径上对此进行查询，不如在插件的迁移中添加一个新表，使用正确的列类型并建立索引。

11

那关于自定义字段表的连接呢？我在那里只搜索包含 topic_id 的条目。@Falco，这不会影响性能吗？

12

我刚才在想……这里的目标是按时间顺序排列，对吧？查看 ISO8601 日期格式 YYYY-MM-DDTHH:MM:SS，我认为实际上可以按“字母顺序”对它们进行排序，结果依然是按时间顺序排列的。

如果不进行类型转换，我认为 PostgreSQL 应该能够利用我们在 (name, value) 上建立的索引，这样效率会高得多。

13

那 create_at 部分呢？我希望在有 sortDate 时使用它，否则使用 created_at。

14

等等，ISO8601 不是本来就能正确进行字符串比较吗？这难道不是它的核心优势之一吗？

没错。

15

问题在于，我想使用 sortDate（我已有该字段）和 created_at（我没有该字段）进行交叉排序，同时不损失性能。

我的问题是，自定义字段的值是字符串类型，而 created_at 是日期类型。

16

您可以尝试这个。
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=The%20TO_DATE%20function%20in%20PostgreSQL,to_timestamp(text%2C%20text).

17

我找到了一种实现方法，但这涉及类型转换：要么将字符串转换为日期（因为 sortDate 是自定义字段值的字符串），要么将 created_at 从日期转换为字符串。

我一直在寻找一种更轻量级的解决方案，不需要额外的工作，比如数据库迁移脚本。

我不太确定 Discourse 在处理自定义表时，如何进行数据库的重基（rebase）和升级。

18

我将来很可能会通过为所有项目填充 sortDate 来重新处理这个问题。如果能在主题本身设置它，那将是一个更优的解决方案，但我再次不确定这会如何影响 Discourse 的升级。

关于自定义表格，以及在使用自定义表格时更新 Discourse 如何避免问题，有什么有用的链接或建议吗？

19

自定义表比修改现有表的字段要安全得多。Discourse 不会干扰你的表，但你可能需要做一些工作以跟上核心代码的变更。

discourse-subscriptions 和 discourse-calendar 就是官方插件拥有自己表的很好例子。

20

@fzngagan 我大约 3-4 个月前开始使用 Discourse，同时也开始接触 Ruby :slight_smile:。当你提到“做一些工作”时，具体指的是哪些步骤？是不是像编写一个迁移脚本，如果表不存在就创建它？如果你能提供一个简短的清单，那将大大减轻我编写文档的负担 :expressionless:。

提前感谢。

21

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