代码之家 › 专栏 › 技术社区 › cfitzarl

Cassandra-按ID分组和按日期排序

cql data-modeling nosql cassandra database

cfitzarl · 技术社区 · 6 年前

我的应用程序的一部分由一个讨论板组成:有线程、帖子和类别。线程按类别分组,帖子按线程分组。我在提出一个模型/查询时遇到了一个问题,该模型/查询将允许按类别选择线程,并按其上一篇文章的降序排列。

类别

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

线

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

邮递

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

我最初考虑将最后一篇文章的“创建时间”作为线程表上的集群键,但这是不可能的,因为它会随着每篇文章的变化而变化。

然后,我考虑创建一个中间表,每次创建帖子时都会写入该表。这就解决了第一种方法的不变性问题,但问题是它将在每个线程中包含多个值,我还无法找出一个分区/集群顺序来支持按线程分组和按日期排序。

例如,以下内容允许我按线程分组,但不允许按日期排序:

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;

以下内容允许我按日期订购,但不能按线程分组:

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;

我不能做一个 distinct 在…上 (category_id, thread_id) 或者是因为我对执行此查询时的线程ID一无所知。

有人知道我如何才能最好地代表这一订单吗?

1 回复 | 直到 6 年前

Community Egal 4 年前

首先,我建议您使用数据类型 datetime 而不是 timestamp ,因为这样可以方便您修改或设置默认值。这只是一个建议。

建议的解决方案:

添加属性 last_post 到桌子上 threads 以节省每个线程中最后添加帖子的时间。
首次创建线程时 最后一篇文章 值应该等于非常旧的日期(因为该线程中还没有帖子)。

在tha之后,创建一个触发器,以便在插入post时 posts ,触发器更新 最后一篇文章 对应线程的值。可以这样添加触发器:

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

最后一步是直接查询,按类别选择线程,按排序 最后一篇文章 ,就像这样:

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

注: 如果你愿意 created_at 要在编辑帖子时更新,您需要添加类似的触发器来更新 最后一篇文章 对应线程的属性。