Como buscar tópicos ordenados pelo valor do campo personalizado iso8601?

Dev
1

Oi,

Encontrei uma consulta estranha com a qual não sei muito bem como lidar usando apenas uma consulta SQL/Active Record.

Tenho tópicos com campos personalizados e preciso ordenar os dados usando o valor do campo personalizado ‘importedSortDate’.

Código inicial:
Topic.where(‘category_id in (?)’, [cat_id])
.where(‘closed = ?’, false)
.order(‘created_at desc’)
.limit(10)

Como posso modificar ou adicionar algo a essa consulta para carregar na memória apenas esses 10 tópicos, e não mais?

Como o modelo da tabela de Campos Personalizados é um pouco estranho, no sentido de que você tem uma coluna ‘name’ e uma coluna ‘value’, estou um pouco travado sobre como fazer tudo isso usando apenas a consulta ao banco de dados.

Meu problema ao carregar mais dados na memória é que, para cada tópico carregado, também são realizadas buscas por categoria, usuário e post.

Estou trabalhando com 1000 a 2000 tópicos por categoria e tenho 4 categorias para buscar.
A busca é feita por todos os usuários, não é algo único.

Conforme vejo, tenho até agora 2 formas de fazer isso (a primeira não parece rápida o suficiente para mim):

  1. Obter apenas os IDs de todos os tópicos que me interessam em uma única busca e, em seguida, usar essa informação de alguma forma para fazer um join com o valor de ‘importedSortDate’, fazer uma ordenação na memória e depois buscar novamente os IDs na mesma ordem em que os tenho (novamente, não sei exatamente como isso funcionaria).
  2. De alguma forma, criar uma consulta que faça join com a tabela de campos personalizados (já vi alguns exemplos, mas eram para valores fixos nas colunas ‘name’ e ‘value’) e ordenar tudo com base nas colunas onde ‘name’ === ‘importedSortDate’ e na coluna ‘value’ (string ISO8601).

Como posso conseguir isso?

PS: Alguns tópicos podem não ter um valor no campo personalizado ‘importedSortDate’, então eu poderia apenas buscá-los e colocá-los onde quiser, mas é um caso extremo com o qual consigo lidar. Se não tiverem valor, eles devem ser as primeiras entradas.

PPS: Quero usar essa consulta posteriormente como minha consulta de paginação. A lógica já está implementada desse lado; só preciso alterar a consulta principal para buscar os tópicos.

2

O limit(10) já deve fazer isso :slight_smile:

Algo assim deve funcionar: (mas não foi testado)

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)

O problema é que pedir ao PostgreSQL para converter a string de data em um timestamp será muito ineficiente. Ele terá que fazer a conversão para cada tópico individualmente antes de selecionar os primeiros 10.

Existem algumas opções que consigo pensar: você pode armazenar as datas como “segundos desde a época”, em vez de ISO8601. Isso tornará a ordenação pelo PostgreSQL muito mais fácil. Ou talvez seja possível usar uma migração de banco de dados para adicionar um índice à tabela topic_custom_fields para TIMESTAMP value.

3

Oh…

Hmm, ok, então isso basicamente significa que, em vez do iso8601, preciso salvar os segundos desde a época, para que a comparação de strings esteja correta e a ordenação funcione adequadamente.

Eu estava tão preocupado em não conseguir obter o valor de import_tcf que esqueci completamente de usar SQL puro…

4

Bem… eu cheguei aqui:

 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") # Obrigado @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

Com a saída:

{: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

Provavelmente preciso executar um script para converter tudo em carimbos de data/hora

6

Você vai querer mudar isso para:

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

para melhorar a legibilidade.

Você está ordenando por sortDate e, quando esse campo está ausente, usa createdAt. Tudo em ordem decrescente. A consulta está retornando os dados exatamente como solicitado, pois 2020 é “maior” que o segundo colocado de 2011.

Se você quiser que os valores nulos apareçam por último, precisa usar:

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

Desculpe, @Falco. Percebi que o código estava correto e editei o mais rápido que pude.

Edição:
Simplifiquei meu código para:
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

Apenas para garantir..

@david, sobre a ideia de converter para timestamps. Meu problema com isso é que nem todas as categorias têm o mesmo modelo de campos personalizados de tópicos. Isso significaria que eu teria que criar um sort_date para cada tópico ou manter como está e apenas ordenar por sort_date || t.created_at.

Com timestamps, precisa ser tudo ou nada. :frowning:

9

@Falco @david, vocês têm alguma ideia sobre as implicações de desempenho ao usar CAST no ORDER BY?

Neste tópico, o autor afirma que o desempenho se degrada conforme aumenta o número de itens retornados. Como não sou um guru de SQL, não sei se isso é verdade. (https://stackoverflow.com/a/491240/4020131)

A razão para isso é que, com um tipo de dado char, você está ordenando as linhas como strings.

A ideia de usar ORDER BY com CAST() está correta; no entanto, o desempenho disso diminuirá à medida que o número de resultados retornados aumentar.

Se houver apenas dados numéricos nessa coluna, a melhor prática seria encontrar um tipo de dado numérico adequado e alterá-lo.

Se você realmente não puder alterar a coluna e estiver enfrentando problemas de desempenho, sugiro criar uma coluna de ordem de classificação que contenha o valor convertido para inteiro (com nulos convertidos para um valor apropriado).

Crie um índice na coluna de ordem de classificação e, idealmente, adicione um gatilho à coluna CHAR para que inserções ou atualizações no valor char disparem uma atualização no valor inteiro.

Pelo que ele diz, entendi que, se eu quiser apenas 10 a 20 tópicos, o desempenho permanecerá o mesmo, independentemente de quantos tópicos eu tenha no banco de dados.

Para mim, isso é contra-intuitivo, pois como o sistema saberá ordenar todos os tópicos e me entregar 10 a 20 se não fizer o CAST antes?

Também encontrei este tópico tópico da MSDN, mas não entendo exatamente como ele se aplica ao meu caso — CAST no ORDER BY.

10

Ruim. Se você planeja consultar isso em um caminho crítico, pode ser melhor adicionar uma nova tabela com um tipo de coluna adequado e um índice em uma migração do seu plugin.

11

E quanto à junção da tabela de campos personalizados, onde eu busco apenas as entradas que têm topic_id? @Falco, isso não afeta o desempenho também?

12

Estava apenas pensando… o objetivo aqui é obter as coisas em ordem cronológica, certo? Olhando para o formato YYYY-MM-DDTHH:MM:SS das datas ISO8601, acho que você pode realmente se dar ao luxo de ordená-las “alfabeticamente”, e ainda assim manterá a ordem cronológica.

Sem o cast, acho que o postgres conseguirá usar o índice que temos em (name, value), e isso deve ser muito mais eficiente.

13

Mas e quanto à parte create_at? Gostaria que usasse sortDate quando ele estiver disponível e created_at quando não estiver.

14

Espere, o ISO8601 já não compara strings corretamente? Isso não é metade do ponto?

Sim.

15

O problema é que quero fazer uma ordenação cruzada usando sortDate (onde já tenho) e created_at (onde não tenho), sem perda de desempenho.

Meu problema é que o valor de um campo personalizado é uma STRING e created_at é do tipo DATE.

16

Você pode tentar isso provavelmente.
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=The%20TO_DATE%20function%20in%20PostgreSQL,to_timestamp(text%2C%20text).

17

Encontrei uma maneira de fazer isso, mas envolve conversão de tipo, seja de string para data (já que sortDate é um valor de campo personalizado do tipo string) ou de data para string para created_at.

Eu estava procurando uma solução menos exigente e que não exigisse trabalho extra, como scripts de migração de banco de dados.

Não tenho certeza de como o Discourse lida com tabelas personalizadas quando se trata de rebasing e atualização do banco de dados.

18

Vou revisar isso no futuro, provavelmente preenchendo o sortDate para todos os itens. Seria uma solução mais elegante se eu pudesse configurá-lo diretamente no tópico, mas não sei novamente como isso afetaria as atualizações do Discourse.

Alguma dica ou link útil sobre tabelas personalizadas e como evitar problemas ao usá-las e atualizar o Discourse?

19

Tabelas personalizadas são muito mais seguras do que adicionar ou modificar campos nas tabelas existentes. O Discourse não interferirá em suas tabelas, mas você pode precisar fazer algum trabalho para se manter atualizado com as mudanças no núcleo.

discourse-subscriptions e discourse-calendar são bons exemplos de plugins oficiais que possuem suas próprias tabelas.

20

@fzngagan Comecei a usar o Discourse há 3 ou 4 meses, junto com o Ruby :slight_smile:. Quando você diz “fazer algum trabalho”, quais são os passos exatos aos quais você se refere? É algo como escrever um script de migração que cria a tabela caso ela não exista? Se você puder me ajudar com uma pequena lista, isso realmente facilitaria minha documentação :expressionless:

Obrigado desde já

21

Se você perceber algo quebrando (e precisará verificar regularmente), terá que continuar corrigindo. Além disso, você pode escrever alguns testes unitários que ajudarão a localizar os problemas facilmente.