カスタムフィールド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 カラムがあるため、データベースクエリだけでこれをどう実現すればよいか詰まっています。

メモリに多く読み込むことの問題点は、トピックを 1 つ読み込むたびに、カテゴリ、ユーザー、投稿のフェッチも実行されてしまうことです。

カテゴリあたり 1000〜2000 件のトピックがあり、4 つのカテゴリからフェッチする必要があります。
このフェッチは全ユーザーによって行われるため、一度きりの処理ではありません。

現状、以下の 2 つの方法を考えています（1 つ目は速度が足りないようです）:

  1. 対象となるすべてのトピックの ID を 1 回のフェッチで取得し、その情報を使って ‘importedSortDate’ の値と結合 somehow して、メモリ内でソートし、その後、取得した ID の順序通りに再度フェッチを行う（ただし、これが具体的にどう動くかはよくわからない）。
  2. somehow カスタムフィールドテーブルと結合するクエリを作成する（name と value カラムに固定値を指定する例は見たことがあるが、‘name’ === ‘importedSortDate’ かつ value カラム（ISO8601 形式の文字列）に基づいてソートする方法）。

これをどう実現すればよいでしょうか？

PS: 一部のトピックは ‘importedSortDate’ カスタムフィールドに値を持っていない場合があります。そのような場合はフェッチして任意の位置に配置できますが、これは対処可能なエッジケースです。値がない場合は、先頭に配置されるべきです。

PPS: このクエリを後でページネーションクエリとして使用したいと考えています。ページネーションのロジックは既に実装済みですが、トピックを取得するメインクエリを変更する必要があります。

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)

問題は、Postgres に日付文字列をタイムスタンプにキャストさせる処理が非常に非効率になる点です。すべてのトピックに対して変換を行った上で、最初の10件を選ぶ必要があります。

ここで考えられるいくつかのオプションがあります。日付を ISO8601 形式ではなく「エポックからの秒数」として保存する方法です。これにより、Postgres でのソートがはるかに容易になります。あるいは、データベースマイグレーションを使用して topic_custom_fields テーブルに TIMESTAMP value 用のインデックスを追加することも可能です。

3

ああ………

ふむ、なるほど。つまり iso8601 の代わりに、エポックからの秒数を保存する必要があるんですね。そうすれば文字列比較が正しく機能し、ソートも適切に行われるようになります。

import_tcf の値を取得できないことに気を取られすぎて、raw 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 年の 2 位よりも「大きい」ためです。

NULL を最後にしたい場合は、以下のようにします。

.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() というアイデアは正しいですが、返される結果の数が増えるにつれて、そのパフォーマンスは低下します。

この列に数値データしかない場合は、適切な数値データ型を見つけて変更するのがベストプラクティスです。

もし列を変更できない場合、かつパフォーマンスの問題に直面している場合は、整数に変換した値（NULL は適切な値に変換）を含むソート順序列を作成することをお勧めします。

そのソート順序列にインデックスを貼り、理想的には CHAR 列にトリガーを追加して、char 値の挿入または更新が整数値の更新をトリガーするようにします。

彼の発言から私が理解しているのは、10〜20 のトピックだけを取得したい場合、データベース内のトピック数がどれだけあってもパフォーマンスは変わらないということです。

これは私には直感に反します。事前にキャストを行わなければ、どのようにしてすべてのトピックをソートして 10〜20 の結果を返すことができるのでしょうか？

また、このスレッド MSDN スレッド も見つけましたが、私のケース（ORDER BY での CAST）にどのように適用されるのか、正確には理解できていません。

10

悪いです。このクエリをホットパスで実行する予定があるなら、プラグインのマイグレーションで適切な列型とインデックスを持つ新しいテーブルを追加する方が良いでしょう。

11

カスタムフィールドテーブルの結合については、topic_id を持つエントリのみを検索する場合、@Falco さん、これもパフォーマンスに影響しませんか？

12

ふと気づいたのですが、ここでの目的は時系列順に並べることですよね？ISO8601 日付形式の YYYY-MM-DDTHH:MM:SS を見ると、実際にはアルファベット順にソートしても時系列順になるはずです。

キャストなしであれば、Postgres は (name, value) にあるインデックスを利用でき、はるかに効率的になると思います。

13

しかし、create_at の部分はどのようにすればよいでしょうか？sortDate が存在する場合はそれを使用し、存在しない場合は created_at を使用したいです。

14

待って、ISO8601 はもともと文字列比較が正しく機能するんじゃなかったでしたっけ？それこそが主な利点の一つですよね？

そうですね。

15

問題点は、sortDate（持っている場所）と created_at（持っていない場所）を使って、パフォーマンスを落とさずにクロスソートを行いたいことです。

私の問題は、カスタムフィールドの値が STRING 型であり、created_at が DATE 型であることです。

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 がリベースやデータベースのアップグレードの際にカスタムテーブルをどのように扱うのか、私は確信が持てません。

18

将来、おそらくすべてのアイテムの sortDate を埋めて、これを再検討するつもりです。トピック自体で設定できればより良い解決策になるのですが、それが Discourse のアップグレードにどう影響するかは、再びわかりません。

カスタムテーブルに関する役立つリンクやヒント、それらを使用しながら Discourse を更新する際に問題を防ぐ方法について、何かご教示いただけますか？

19

既存のテーブルにフィールドを追加または変更するよりも、カスタムテーブルを作成する方がはるかに安全です。Discourse はあなたのテーブルをいじりませんが、コアの変更に対応するために何らかの作業が必要になる場合があります。

discourse-subscriptions と discourse-calendar は、独自テーブルを持つ公式プラグインの良い例です。

20

@fzngagan Discourse は 3〜4 ヶ月前に使い始め、Ruby も同様です :slight_smile:。「いくつか作業を行う」とおっしゃる際、具体的にどの手順を指していますか？テーブルが存在しない場合に作成するマイグレーションスクリプトを書くようなことでしょうか？もし小さなリストで教えていただければ、ドキュメント作成の負担が大幅に軽減されます :expressionless:。

あらかじめありがとうございます。

21

問題が発生しているのを見つけたら（定期的に確認する必要があります）、修正し続ける必要があります。また、ユニットテストを作成しておくと、問題箇所を簡単に特定できるようになります。