用IN和ORDER BY创建Cassandra CQL

Question

我需要一个CQL从表中获取所有的行，基于当前用户朋友的集合（我使用IN），并根据创建的日期对它们进行排序。

CREATE TABLE chat.news_feed(
  id_news_feed                           uuid,
  id_user_sent         uuid,
  first_name text,
  last_name text,
  security int,
  news_feed text, 
  image blob,
  image_preview text,
  image_name text,
  image_length int,
  image_resolution text,
  is_image int,
  created_date        timestamp,
  PRIMARY KEY ((id_news_feed, id_user_sent), created_date))
    WITH CLUSTERING ORDER BY (created_date DESC) AND comment = 'List of all news feed by link id';

SELECT JSON id_news_feed,  first_name, last_name, id_user_sent, news_feed, image_name, image_preview, image_length, created_date, is_image, image_resolution FROM chat.news_feed WHERE id_user_sent in (b3306e3f-1f1d-4a87-8a64-e22d46148316,b3306e3f-1f1d-4a87-8a64-e22d46148316) ALLOW FILTERING;

有没有什么方法可以用Order by获得由一组用户创建的所有行（我尝试用不同的方法创建表，但还没有结果）？

谢谢！

Answer 1

与这里的关系数据库不同，您可能需要对表进行非正规化。首先，您无法有效地从单个表中查询所有内容。另外，Cassandra不支持本地联接。我建议把你的桌子分成几张。

让我们从friends开始：当前用户id应该是主键的一部分，而friends应该作为集群列。

CREATE TABLE chat.user_friends (
  user_id uuid,
  friend_id uuid,
  first_name text,
  last_name text,
  security int,
  PRIMARY KEY ((user_id), friend_id));

现在，您可以通过如下查询为每个特定用户查找好友：

SELECT * FROM chat.user_friends WHERE user_id = 'a001-...';

SELECT * FROM chat.user_friends WHERE user_id = 'a001-...' and friend_id in ('a121-...', 'a156-...', 'a344-...');

CREATE TABLE chat.news_feed(      
  id_user_sent uuid,
  first_name text,
  last_name text,
  security int,
  id_news_feed uuid,
  news_feed text, 
  image blob,
  image_preview text,
  image_name text,
  image_length int,
  image_resolution text,
  is_image int,
  created_date        timestamp,
  PRIMARY KEY ((id_user_sent), created_date))
    WITH CLUSTERING ORDER BY (created_date DESC) AND comment = 'List of all news feed by link id';

cqlsh:ks_test> select * from news_feed ;

 id_user_sent                         | created_date                    | first_name | id_news_feed                         | image | image_length | image_name | image_preview | image_resolution | is_image | last_name | news_feed | security
--------------------------------------+---------------------------------+------------+--------------------------------------+-------+--------------+------------+---------------+------------------+----------+-----------+-----------+----------
 01b9b9e8-519c-4578-b747-77c8d9c4636b | 2017-02-23 00:00:00.000000+0000 |       null | fd25699c-78f1-4aee-913a-00263912fe18 |  null |         null |       null |          null |             null |     null |      null |      null |     null
 9bd23d16-3be3-4e27-9a47-075b92203006 | 2017-02-21 00:00:00.000000+0000 |       null | e5d394d3-b67f-4def-8f1e-df781130ea22 |  null |         null |       null |          null |             null |     null |      null |      null |     null
 6e05257d-9278-4353-b580-711e62ade8d4 | 2017-02-25 00:00:00.000000+0000 |       null | ec34c655-7251-4af8-9718-3475cad18b29 |  null |         null |       null |          null |             null |     null |      null |      null |     null
 6e05257d-9278-4353-b580-711e62ade8d4 | 2017-02-22 00:00:00.000000+0000 |       null | 5342bbad-0b55-4f44-a2e9-9f285d16868f |  null |         null |       null |          null |             null |     null |      null |      null |     null
 6e05257d-9278-4353-b580-711e62ade8d4 | 2017-02-20 00:00:00.000000+0000 |       null | beea0c24-f9d6-487c-a968-c9e088180e73 |  null |         null |       null |          null |             null |     null |      null |      null |     null
 63003200-91c0-47ba-9096-6ec1e35dc7a0 | 2017-02-21 00:00:00.000000+0000 |       null | a0fba627-d6a7-463c-a00c-dd0472ad10c5 |  null |         null |       null |          null |             null |     null |      null |      null |     null

和过滤后的：

cqlsh:ks_test> select * from news_feed where id_user_sent in (01b9b9e8-519c-4578-b747-77c8d9c4636b, 6e05257d-9278-4353-b580-711e62ade8d4) and created_date >= '2017-02-22';

 id_user_sent                         | created_date                    | first_name | id_news_feed                         | image | image_length | image_name | image_preview | image_resolution | is_image | last_name | news_feed | security
--------------------------------------+---------------------------------+------------+--------------------------------------+-------+--------------+------------+---------------+------------------+----------+-----------+-----------+----------
 01b9b9e8-519c-4578-b747-77c8d9c4636b | 2017-02-25 00:00:00.000000+0000 |       null | 26dc0952-0636-438f-8a26-6a3fef4fb808 |  null |         null |       null |          null |             null |     null |      null |      null |     null
 01b9b9e8-519c-4578-b747-77c8d9c4636b | 2017-02-23 00:00:00.000000+0000 |       null | fd25699c-78f1-4aee-913a-00263912fe18 |  null |         null |       null |          null |             null |     null |      null |      null |     null
 6e05257d-9278-4353-b580-711e62ade8d4 | 2017-02-25 00:00:00.000000+0000 |       null | ec34c655-7251-4af8-9718-3475cad18b29 |  null |         null |       null |          null |             null |     null |      null |      null |     null
 6e05257d-9278-4353-b580-711e62ade8d4 | 2017-02-22 00:00:00.000000+0000 |       null | 5342bbad-0b55-4f44-a2e9-9f285d16868f |  null |         null |       null |          null |             null |     null |      null |      null |     null

附言。您可能注意到，我们删除了Allow Filtering子句。不要在任何应用程序中使用Allow Filtering，因为它会严重影响性能。这只能用于查找分散在不同分区中的一些小块数据。