Cassandra - Agrupación por ID y pedido por fecha

Cassandra - Agrupación por ID y pedido por fecha

Una parte de mi aplicación consiste en un foro de discusión: hay hilos, publicaciones y categorías. Los hilos se agrupan por categoría y las publicaciones se agrupan por hilos. Tengo un problema con un modelo/consulta que permitirá la selección de hilos por categoría con un orden descendente de su última publicación.

Categoría

CREATE TABLE keyspace.categories (
    id ascii PRIMARY KEY,
    description text,
    name text,
    ...
);

Hilo

CREATE TABLE keyspace.threads (
    id ascii PRIMARY KEY,
    category_id ascii,
    content text,
    ...
);

Publicar

CREATE TABLE keyspace.posts (
    thread_id ascii,
    created_at timestamp,
    id ascii,
    content text,
    ...
    PRIMARY KEY (thread_id, created_at, id)
);

Inicialmente pensé en poner el tiempo "creado en" de la última publicación como una clave de agrupación en la tabla de hilos, pero eso es imposible ya que cambia con cada publicación.

Entonces pensé en crear una tabla intermedia en la que se escriba cada vez que se crea una publicación. Esto resuelve el problema de inmutabilidad con el primer enfoque, pero el problema es que contendrá múltiples valores por subproceso y no he podido descifrar un orden de partición/agrupación que permita agrupar por subproceso y ordenar por fecha.

Por ejemplo, lo siguiente me permitiría agrupar por tema, pero no ordenar por fecha:

CREATE TABLE last_post_for_category (
    category_id ascii,
    thread_id ascii,
    created_at timestamp,
    PRIMARY KEY ((category_id), thread_id, created_at)
) WITH CLUSTERING ORDER BY (thread_id DESC, created_at DESC);

SELECT thread_id FROM last_post_for_category WHERE category_id = 'category' GROUP BY thread_id, created_at;

Y lo siguiente me permitiría ordenar por fecha, pero no agrupar por hilo:

CREATE TABLE keyspace.last_post_for_category (
    category_id ascii,
    thread_id ascii,
    created_at timestamp,
    PRIMARY KEY ((category_id), created_at, thread_id)
) WITH CLUSTERING ORDER BY (created_at DESC, thread_id DESC);

SELECT thread_id FROM last_post_for_category WHERE category_id = 'category' GROUP BY created_at, thread_id;

Tampoco puedo hacer un distinct en (category_id, thread_id) porque no sé nada sobre los ID de subprocesos en el punto en el que se ejecuta esta consulta.

¿Alguien tiene alguna idea de cómo puedo representar mejor este pedido?

Mostrar la mejor respuesta

En primer lugar, le recomiendo que utilice el tipo de datos datetime en lugar de timestamp, ya que le resultará más fácil modificarlo o establecer un valor predeterminado. Esto es solo una recomendación.

Solución sugerida:

Agregue el atributo last_post a la tabla threads para guardar la hora de la última publicación agregada en cada hilo.
Cuando se crea un hilo por primera vez, el valor de last_post debe ser igual a una fecha muy antigua (porque todavía no hay publicaciones en ese hilo).

Después de eso, cree un disparador para que cada vez que se inserte una publicación en posts, el disparador actualice el valor de last_post del hilo correspondiente. El disparador se puede agregar así:

CREATE TRIGGER triggerName ON posts
FOR INSERT
AS
declare @post_time datetime;
declare @thread_id int;
select @post_time=i.created_at from inserted i;
select @thread_id=i.thread_id from inserted i;

update threads set lastpost = @post_time where id=@thread_id  
GO

El último paso será una consulta directa para seleccionar hilos por categoría ordenados por last_post, así:

select * from threads where category_id = 'theCategoryYouWant' order by lastpost asc /*or desc as you like*/  

Nota: si desea que created_at se actualice cuando se edite la publicación, deberá agregar un disparador similar para actualizar el atributo last_post del hilo correspondiente.

Como no puedo hacer que last_post sea una clave de agrupación, intenté crear un índice y ordenarlo, pero no puedo hacerlo: InvalidRequest: Error from server: code=2200 [Invalid query] message="Order by is currently only supported on the clustered columns of the PRIMARY KEY, got last_post"

¿Por qué no puedes convertirlo en la clave? ¿La consulta deducida al final de mi respuesta no resolvió su problema?

Puede que me esté malinterpretando, pero no puedo convertirlo en una clave ya que Cassandra no te permite mutarlos. Cuando intenté resolver esto por primera vez, terminé teniendo que eliminar y volver a crear filas con la fecha actualizada.

Supongo que hay algo mal. last_post es solo un atributo datetime. Nada tiene de especial. No es necesario que sea una clave primaria ni secundaria. Simplemente actualícelo a través del disparador cada vez que se agregue una nueva publicación. Por favor, dame más información sobre tu problema con eso. Quiero decir, ¿por qué no puedes mutarlo aunque es un atributo regular y no tiene nada de especial? ¿Por qué puedes mutar otros atributos pero no last_post específicamente?

¡Perdón por la confusion! Dije que es inmutable porque hasta ahora no he podido ordenar por un campo que no sea una clave de agrupación, incluso con un índice secundario o sasi.