Cómo obtener temas ordenados por el valor de campo personalizado iso8601?

Desarrollador
1

Hola,

Me he topado con una consulta extraña sobre la cual no sé muy bien cómo plantearla usando solo una consulta SQL/Active Record.

Tengo temas con campos personalizados y necesito ordenar los datos utilizando el valor del campo personalizado ‘importedSortDate’.

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

¿Cómo puedo modificar o añadir a esta consulta para cargar en memoria solo esos 10 temas y no más?

Dado que la plantilla de la tabla de campos personalizados es un poco extraña, en el sentido de que tienes una columna ‘name’ y una columna ‘value’, estoy un poco atascado sobre cómo lograr hacer todo esto usando una consulta a la base de datos.

Mi problema al cargar más datos en memoria es el hecho de que por cada tema que cargo en memoria, también se realizan búsquedas para la categoría, el usuario y la publicación.

Estoy trabajando con 1000-2000 temas por categoría y tengo 4 categorías de las cuales obtener datos.
La búsqueda la realizan todos los usuarios, no es algo que se haga una sola vez.

Tal como lo veo, tengo dos formas de hacerlo hasta ahora (la primera no parece lo suficientemente rápida para mí):

  1. Obtener solo los IDs de todos los temas que me interesan en una sola búsqueda y luego usar esa información de alguna manera para unirla con el valor de ‘importedSortDate’, hacer un ordenamiento en memoria y luego realizar nuevamente una búsqueda para los IDs en el mismo orden en que los tengo (de nuevo, no sé exactamente cómo funcionaría eso).
  2. De alguna manera crear una consulta que se una a la tabla de campos personalizados (he visto algunos ejemplos, pero eran para valores fijos en las columnas ‘name’ y ‘value’) y ordenar todo esto basándose en las columnas donde ‘name’ === ‘importedSortDate’ y la columna ‘value’ (cadena iso8601).

¿Cómo puedo lograr esto?

PD: Algunos temas pueden no tener un valor en el campo personalizado ‘importedSortDate’, por lo que podría simplemente obtenerlos y colocarlos donde quiera, pero es un caso límite con el que puedo lidiar. Si no tienen un valor, deberían ser las primeras entradas.

PPD: Quiero usar esta consulta posteriormente como mi consulta de paginación; la lógica ya está implementada en ese lado, solo necesito cambiar la consulta principal para obtener los temas.

2

El limit(10) ya debería hacer eso :slight_smile:

Esto es lo que debería funcionar (aunque no lo he probado):

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)

El problema es que pedirle a PostgreSQL que convierta la cadena de fecha en una marca de tiempo será muy ineficiente. Tendrá que realizar la conversión para cada tema individualmente antes de seleccionar los primeros 10.

Se me ocurren algunas opciones: podrías almacenar las fechas como “segundos desde la época” en lugar de en formato ISO8601. Eso hará que sea mucho más fácil para PostgreSQL ordenarlas. O también podrías utilizar una migración de base de datos para añadir un índice a la tabla topic_custom_fields para TIMESTAMP value.

3

Oh…

Hmm, vale, así que esto significa que, en lugar de usar iso8601, necesito guardar los segundos desde la época, para que la comparación de cadenas sea correcta y el ordenamiento funcione adecuadamente.

Estaba tan preocupado por no poder obtener el valor de import_tcf que se me olvidó por completo usar SQL puro…

4

Bueno… llegué aquí:

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

Con la salida:

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

Probablemente necesito ejecutar un script para convertir todo a marcas de tiempo

6

Querrás cambiar esto por:

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

para mejorar la legibilidad.

Ordenas por sortDate y, cuando este no está disponible, usas createdAt. Todo en orden descendente. La consulta devuelve los datos exactamente como se solicitaron, ya que 2020 es “mayor” que el segundo lugar de 2011.

Si deseas que los valores nulos aparezcan al final, necesitas:

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

Lo siento @Falco, me di cuenta de que el código era correcto y lo edité lo más rápido que pude.

Edición:
He simplificado mi código a:
.order(“coalesce(cast(custom_fields.value as timestamp), topics.created_at) desc”)

Solo por seguridad..

@david En cuanto a la idea de convertir a marcas de tiempo. Mi problema con eso es que no todas las categorías tienen el mismo modelo de campos personalizados para temas. Esto significaría que tendría que crear un sort_date para cada tema, o dejarlo como está y simplemente ordenar por sort_date || t.created_at.

Con las marcas de tiempo tiene que ser todo o nada. :frowning:

9

@Falco @david ¿tienen alguna idea sobre las implicaciones de rendimiento al realizar un casting en ORDER BY?

En este hilo, el autor afirma que el rendimiento se degradará a medida que aumente el número de elementos devueltos. No soy un experto en SQL, así que no sé si esto es cierto. (https://stackoverflow.com/a/491240/4020131)

La razón de esto es que, con un tipo de dato char, estás ordenando las filas como si fueran cadenas de texto.

La idea de usar ORDER BY CAST() es correcta, sin embargo, el rendimiento de esta operación disminuirá a medida que aumente el número de resultados devueltos.

Si en esta columna solo hay datos numéricos, la mejor práctica sería encontrar un tipo de dato numérico adecuado y cambiarlo.

Si realmente no puedes cambiar la columna y te encuentras con problemas de rendimiento, te sugiero crear una columna de orden de clasificación que contenga el valor convertido a entero (con los valores nulos convertidos a un valor apropiado).

Indexa la columna de orden de clasificación e, idealmente, agrega un disparador (trigger) a la columna CHAR para que las inserciones o actualizaciones del valor CHAR provoquen una actualización del valor entero.

De lo que dice, entiendo que si solo quiero 10-20 temas, el rendimiento se mantendrá igual, sin importar cuántos temas tenga en la base de datos.

Para mí esto es contraintuitivo, ya que ¿cómo sabrá ordenar todos los temas y devolverme 10-20 si no lo convierte de antemano?

También encontré este hilo hilo de MSDN, pero no entiendo exactamente cómo se aplica a mi caso: CAST en ORDER BY.

10

Mal. Si planeas consultar esto en un camino crítico, lo mejor sería agregar una nueva tabla con un tipo de columna adecuado y un índice en una migración de tu plugin.

11

¿Qué hay de la unión de la tabla de campos personalizados, donde busco solo las entradas que tienen topic_id? @Falco, ¿eso no afecta también el rendimiento?

12

Solo estaba pensando… ¿el objetivo aquí es obtener los elementos en orden cronológico, verdad? Al observar el formato YYYY-MM-DDTHH:MM:SS de las fechas ISO8601, creo que realmente puedes contentarte con ordenarlas “alfabéticamente” y seguirán estando en orden cronológico.

Sin la conversión, creo que Postgres debería poder utilizar el índice que tenemos en (name, value), y debería ser mucho más eficiente.

13

Pero, ¿qué pasa con la parte de create_at? Me gustaría que usara sortDate cuando la tenga, y created_at cuando no.

14

Espera, ¿no compara ISO8601 correctamente las cadenas de todos modos? ¿No es esa la mitad del propósito?

Sí.

15

El problema es que quiero realizar un ordenamiento cruzado usando sortDate (donde ya lo tengo) y created_at (donde no lo tengo), sin perder rendimiento.

Mi problema es que el valor del campo personalizado es una CADENA y created_at es de tipo FECHA.

16

Probablemente puedes probar esto.
https://www.blendo.co/documents/queries-casting-postgresql/#:~:text=The%20TO_DATE%20function%20in%20PostgreSQL,to_timestamp(text%2C%20text).

17

Encontré una forma de hacerlo, pero implica realizar conversiones de tipo, ya sea de string a fecha (sortDate es el valor de un campo personalizado en formato string) o convertir created_at de fecha a string.

Buscaba una solución menos exigente que no requiriera trabajo adicional como scripts de migración de base de datos.

No estoy seguro de cómo Discourse maneja las tablas personalizadas en lo que respecta a la reorganización y actualización de la base de datos.

18

Revisaré esto en el futuro, muy probablemente completando el sortDate para todos los elementos. Sería una solución más elegante si pudiera configurarlo en el propio Topic, pero no sé cómo afectaría eso a las actualizaciones de Discourse.

¿Alguna ayuda útil o consejos sobre tablas personalizadas y cómo evitar problemas al tenerlas y actualizar Discourse?

19

Las tablas personalizadas son mucho más seguras que agregar o modificar campos en las existentes. Discourse no interferirá con tus tablas, pero es posible que debas realizar algunos ajustes para mantenerte al día con los cambios en el núcleo.

discourse-subscriptions y discourse-calendar son buenos ejemplos de plugins oficiales que cuentan con sus propias tablas.

20

@fzngagan Empecé a usar Discourse hace 3-4 meses, junto con Ruby :slight_smile:. Cuando dices “hacer algo de trabajo”, ¿a qué pasos exactos te refieres? ¿Se trata de algo como escribir un script de migración que cree la tabla si no existe? Si puedes ayudarme con una pequeña lista, me facilitaría mucho el tiempo de documentación :expressionless:

Gracias de antemano.

21

Si ves algo que falla (y tendrás que revisarlo regularmente), tendrás que seguir corrigiéndolo. Además, puedes escribir algunas pruebas unitarias que te ayudarán a localizar los problemas fácilmente.