Clearly with your current data model, having X latest post for each author
is not possible.
However, what's about this ?
CREATE TABLE latest_posts_per_user (
author ascii
latest_post map<uuid,text>,
PRIMARY KEY (author)
)
The latest_post will keep a collection of X latest posts for each user.
Now the challenge is to "update" this latest_post map every time an user
create a new post. This can be done in a single CQL3 statement: UPDATE
latest_posts_per_user SET latest_post = latest_post + {new_uuid: 'new
entry', oldest_uuid: null} WHERE author = xxx;
You'll need to know the uuid of the oldest post to remove it from the map
On Sat, May 17, 2014 at 8:53 AM, 後藤 泰陽 <matope....@gmail.com> wrote:
> Hello,
>
> Thank you for your addressing.
>
> But I consider LIMIT to be a keyword to limits result numbers from WHOLE
> results retrieved by the SELECT statement.
> 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
>
>
>
>
> 2014/05/16 23:54、Jonathan Lacefield <jlacefi...@datastax.com> のメール：
>
> Hello,
>
> Have you looked at using the CLUSTERING ORDER BY and LIMIT features of
> CQL3?
>
> These may help you achieve your goals.
>
>
> http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/refClstrOrdr.html
>
> http://www.datastax.com/documentation/cql/3.1/cql/cql_reference/select_r.html
>
> Jonathan Lacefield
> Solutions Architect, DataStax
> (404) 822 3487
> <http://www.linkedin.com/in/jlacefield>
>
> <http://www.datastax.com/cassandrasummit14>
>
>
>
> On Fri, May 16, 2014 at 12:23 AM, Matope Ono <matope....@gmail.com> wrote:
>
>> Hi, I'm modeling some queries in CQL3.
>>
>> I'd like to query first 1 columns for each partitioning keys in CQL3.
>>
>> For example:
>>
>> create table posts(
>>> author ascii,
>>> created_at timeuuid,
>>> entry text,
>>> primary key(author,created_at)
>>> );
>>> insert into posts(author,created_at,entry) values
>>> ('john',minTimeuuid('2013-02-02 10:00+0000'),'This is an old entry by
>>> john');
>>> insert into posts(author,created_at,entry) values
>>> ('john',minTimeuuid('2013-03-03 10:00+0000'),'This is a new entry by john');
>>> insert into posts(author,created_at,entry) values
>>> ('mike',minTimeuuid('2013-02-02 10:00+0000'),'This is an old entry by
>>> mike');
>>> insert into posts(author,created_at,entry) values
>>> ('mike',minTimeuuid('2013-03-03 10:00+0000'),'This is a new entry by mike');
>>
>>
>> And I want results like below.
>>
>> mike,1c4d9000-83e9-11e2-8080-808080808080,This is a new entry by mike
>>> john,1c4d9000-83e9-11e2-8080-808080808080,This is a new entry by john
>>
>>
>> I think that this is what "SELECT FIRST " statements did in CQL2.
>>
>> The only way I came across in CQL3 is "retrieve whole records and drop
>> manually",
>> but it's obviously not efficient.
>>
>> Could you please tell me more straightforward way in CQL3?
>>
>
>
>
