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....@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
