what I mean is, I really just want the last modified date instead of series
of timestamp and still able to sort or order by it.
(maybe I should rephrase my question as how to sort or order by last
modified column in a row)
CREATE TABLE user_file (
user_id uuid,
modified_date timestamp,
file_id timeuuid,
PRIMARY KEY(user_id, modified_date)
);
e.g user1 update file A 3 times in a row, and update file B, then update
file A again.
insert into user_file values(user1_uuid, date1, file_a_uuid);
insert into user_file values(user1_uuid, date2, file_a_uuid);
insert into user_file values(user1_uuid, date3, file_a_uuid);
insert into user_file values(user1_uuid, date4, file_b_uuid);
insert into user_file values(user1_uuid, date5, file_a_uuid);
#trying to get top 3 most recent changed files
select * from user_file where user_id=user1_uuid limit 3
using CQL, I will get 3 rows back(all file a)
(user1_uuid, date1, file_a_uuid);
(user1_uuid, date2, file_a_uuid);
(user1_uuid, date3, file_a_uuid);
what I want is (file a AND file b)
user1_uuid, date1, file_a_uuid
user1_uuid, date4, file_b_uuid
So how do I order by/sort by last modified column in a row?
thanks
On Thu, Jul 11, 2013 at 12:00 AM, aaron morton <aa...@thelastpickle.com>wrote:
> What you described this sounds like the most appropriate:
>
> CREATE TABLE user_file (
> user_id uuid,
> modified_date timestamp,
> file_id timeuuid,
> PRIMARY KEY(user_id, modified_date)
> );
>
> If you normally need more information about the file then either store
> that as additional fields or pack the data using something like JSON or
> Protobuf.
>
> > my return list may still not accurate because a single directory could
> have lot of modification changes. I basically end up pulling out series of
> modification timestamp for the same directory.
> Not sure I understand the problem.
>
> Cheers
>
>
> -----------------
> Aaron Morton
> Freelance Cassandra Consultant
> New Zealand
>
> @aaronmorton
> http://www.thelastpickle.com
>
> On 10/07/2013, at 6:51 PM, Jimmy Lin <y2klyf+w...@gmail.com> wrote:
>
> > I have an application that need to find out the n most recent modified
> files for a given user id. I started out few tables but still couldn't get
> what i want, I hope someone get point to some right direction...
> >
> > See my tables below.
> >
> > #1 won't work, because file_id's timeuuid contains creation time, not
> the modification time.
> >
> > #2 won't work, because i can't order by a non primary key
> column(modified_date)
> > #3,#4 although i can now get a time series of modification time of each
> file belongs to a user, my return list may still not accurate because a
> single directory could have lot of modification changes. I basically end up
> pulling out series of modification timestamp for the same directory.
> >
> > Any suggestion?
> >
> > Thanks
> >
> >
> >
> > #1
> >
> > CREATE TABLE user_file (
> >
> > user_id uuid,
> >
> > file_id timeuuid,
> >
> > PRIMARY KEY(user_id, file_id)
> >
> > );
> >
> >
> >
> > #2
> >
> > CREATE TABLE user_file (
> >
> > user_id uuid,
> >
> > file_id timeuuid,
> >
> > modified_date timestamp,
> >
> > PRIMARY KEY(user_id, file_id)
> >
> > );
> >
> >
> >
> > #3
> >
> > CREATE TABLE user_file (
> >
> > user_id uuid,
> >
> > file_id timeuuid,
> >
> > modified_date timestamp,
> >
> > PRIMARY KEY(user_id, file_id, modified_date)
> >
> > );
> >
> >
> >
> > #4
> >
> > CREATE TABLE user_file (
> >
> > user_id uuid,
> >
> > modified_date timestamp,
> >
> > file_id timeuuid,
> >
> > PRIMARY KEY(user_id, modified_date, file_id)
> >
> > );
> >
> >
> >
> >
> >
>
>
