incubator-cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bryan Talbot <bryan.tal...@playnext.com>
Subject Re: Query first 1 columns for each partitioning keys in CQL?
Date Mon, 19 May 2014 18:48:50 GMT
I think there are several issues in your schema and queries.

First, the schema can't efficiently return the single newest post for every
author. It can efficiently return the newest N posts for a particular
author.

On Fri, May 16, 2014 at 11:53 PM, 後藤 泰陽 <matope.ono@gmail.com> wrote:

>
> But I consider LIMIT to be a keyword to limits result numbers from WHOLE
> results retrieved by the SELECT statement.
>


This is happening due to the incorrect use of minTimeuuid() function. All
of your created_at values are equal so you're essentially getting 2 (order
not defined) values that have the lowest created_at value.

The minTimeuuid() function is mean to be used in the WHERE clause of a
SELECT statement often with maxTimeuuid() to do BETWEEN sort of queries on
timeuuid values.




> The result with SELECT.. LIMIT is below. Unfortunately, This is not what I
> wanted.
> I wante latest posts of each authors. (Now I doubt if CQL3 can't represent
> it)
>
> cqlsh:blog_test> create table posts(
>              ... author ascii,
>              ... created_at timeuuid,
>              ... entry text,
>              ... primary key(author,created_at)
>              ... )WITH CLUSTERING ORDER BY (created_at DESC);
> cqlsh:blog_test>
> cqlsh:blog_test> insert into posts(author,created_at,entry) values
> ('john',minTimeuuid('2013-02-02 10:00+0000'),'This is an old entry by
> john');
> cqlsh:blog_test> insert into posts(author,created_at,entry) values
> ('john',minTimeuuid('2013-03-03 10:00+0000'),'This is a new entry by
> john');
> cqlsh:blog_test> insert into posts(author,created_at,entry) values
> ('mike',minTimeuuid('2013-02-02 10:00+0000'),'This is an old entry by
> mike');
> cqlsh:blog_test> insert into posts(author,created_at,entry) values
> ('mike',minTimeuuid('2013-03-03 10:00+0000'),'This is a new entry by
> mike');
> cqlsh:blog_test> select * from posts limit 2;
>
>  author | created_at                           | entry
>
> --------+--------------------------------------+------------------------------
>    mike | 1c4d9000-83e9-11e2-8080-808080808080 |  This is a new entry by
> mike
>    mike | 4e52d000-6d1f-11e2-8080-808080808080 | This is an old entry by
> mike
>
>
>
>


To get most recent posts by a particular author, you'll need statements
more like this:

cqlsh:test> insert into posts(author,created_at,entry) values
('john',now(),'This is an old entry by john'); cqlsh:test> insert into
posts(author,created_at,entry) values ('john',now(),'This is a new entry by
john'); cqlsh:test> insert into posts(author,created_at,entry) values
('mike',now(),'This is an old entry by mike'); cqlsh:test> insert into
posts(author,created_at,entry) values ('mike',now(),'This is a new entry by
mike');

and then you can get posts by 'john' ordered by newest to oldest as:

cqlsh:test> select author, created_at, dateOf(created_at), entry from posts
where author = 'john' limit 2 ;

 author | created_at                           | dateOf(created_at)       |
entry
--------+--------------------------------------+--------------------------+------------------------------
   john | 7cb1ac30-df85-11e3-bb46-4d2d68f17aa6 | 2014-05-19 11:43:36-0700 |
 This is a new entry by john
   john | 74bb6750-df85-11e3-bb46-4d2d68f17aa6 | 2014-05-19 11:43:23-0700 |
This is an old entry by john


-Bryan

Mime
View raw message