cassandra-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Matope Ono <matope....@gmail.com>
Subject Re: Query first 1 columns for each partitioning keys in CQL?
Date Sat, 17 May 2014 12:32:37 GMT
Hmm. Something like a user-managed-index looks the only way to do what I
want to do.
Thank you, I'll try that.


2014-05-17 18:07 GMT+09:00 DuyHai Doan <doanduyhai@gmail.com>:

> 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.ono@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 <jlacefield@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.ono@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?
>>>
>>
>>
>>
>

Mime
View raw message