Как получить темы, отсортированные по значению пользовательского поля iso8601?

Разработка
1

Привет,

Я столкнулся с необычным запросом, и не совсем понимаю, как реализовать его, используя только SQL или ActiveRecord.

У меня есть темы с пользовательскими полями, и мне нужно сортировать данные по значению пользовательского поля ‘importedSortDate’.

Исходный код:
Topic.where(‘category_id in (?)’, [cat_id])
.where(‘closed = ?’, false)
.order(‘created_at desc’)
.limit(10)

Как я могу изменить или дополнить этот запрос, чтобы загрузить в память только эти 10 тем, а не больше?

Поскольку шаблон таблицы пользовательских полей немного странный — там есть колонка с именем и колонка со значением — я немного застрял, пытаясь понять, как именно реализовать это всё с помощью запроса к базе данных.

Моя проблема с загрузкой большего количества данных в память заключается в том, что для каждой загружаемой темы также выполняются запросы для получения категории, пользователя и поста.

Я работаю с 1000–2000 тем в каждой категории, и мне нужно получить данные из 4 категорий.
Этот запрос выполняется для всех пользователей, это не разовая операция.

На данный момент я вижу два способа решения (первый, похоже, недостаточно быстрый для меня):

  1. Получить только ID всех интересующих меня тем одним запросом, затем каким-то образом соединить эту информацию со значением ‘importedSortDate’, отсортировать данные в памяти, а затем снова выполнить запрос для получения тем в том же порядке (опять же, не совсем понимаю, как именно это реализовать).
  2. Как-то сформировать запрос с соединением (JOIN) с таблицей пользовательских полей (я видел примеры, но они были для фиксированных значений в колонках name и value) и отсортировать всё это на основе условий: колонка ‘name’ === ‘importedSortDate’ и колонка ‘value’ (строка в формате ISO8601).

Как мне этого добиться?

P.S. У некоторых тем может не быть значения в пользовательском поле 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)

Проблема в том, что заставлять 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 году.

Если вы хотите, чтобы 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, чтобы вставки или обновления значений в нём автоматически обновляли соответствующее целочисленное значение.

Из его слов я понял, что если мне нужно получить только 10–20 тем, то производительность останется неизменной, независимо от того, сколько тем всего в базе данных.

Для меня это контринтуитивно: как система может отсортировать все темы и вернуть только 10–20, если она не выполнит приведение типов заранее?

Я также нашёл эту ветку на MSDN, но не совсем понимаю, как именно она применима к моему случаю — приведение типов (CAST) в ORDER BY.

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 для всех элементов. Было бы лучше, если бы я мог установить это непосредственно на Topic, но я снова не знаю, как это повлияет на обновления Discourse.

Есть ли полезные ссылки или советы по кастомным таблицам и о том, как избежать проблем при их использовании и обновлении Discourse?

19

Пользовательские таблицы гораздо безопаснее, чем добавление или изменение полей в существующих. Discourse не будет вмешиваться в ваши таблицы, но вам, возможно, придется приложить некоторые усилия, чтобы успевать за изменениями в ядре.

discourse-subscriptions и discourse-calendar — хорошие примеры официальных плагинов, имеющих свои собственные таблицы.

20

@fzngagan Я начал использовать Discourse 3–4 месяца назад, а также Ruby :slight_smile:. Когда вы говорите «сделать некоторую работу», какие именно шаги вы имеете в виду? Имеется ли в виду, например, написание скрипта миграции, который создаёт таблицу, если её нет? Если вы сможете помочь небольшим списком, это значительно упростит мне работу по документации :expressionless:

Заранее спасибо

21

Если вы заметите, что что-то ломается (и вам нужно будет регулярно это проверять), вам придётся постоянно исправлять это. Кроме того, вы можете написать некоторые модульные тесты, которые помогут вам легко находить проблемы.