What about having something like CREATE TABLE user_views ( user_name text, video_id text, position int, last_time timestamp, PRIMARY KEY(user_name, last_time) ) WITH CLUSTERING ORDER BY (last_time DESC);
Where you insert a record everytime a user watches a video and then having a batch task (every night maybe?) that deletes the extra rows that are not needed anymore. The query pattern for this is quite efficient as something like SELECT * FROM user_views WHERE user_name = ? LIMIT 10; Regards Carlos Alonso | Software Engineer | @calonso <https://twitter.com/calonso> On 10 November 2016 at 09:19, Vladimir Yudovin <vla...@winguzone.com> wrote: > >Do you mean the oldest one should be removed when a new play is added? > Sure. As you described the issue "the last ten items may be adequate for > the business" > > Best regards, Vladimir Yudovin, > > *Winguzone <https://winguzone.com?from=list> - Hosted Cloud > CassandraLaunch your cluster in minutes.* > > > ---- On Wed, 09 Nov 2016 20:47:05 -0500*Diamond ben > <diamond....@outlook.com <diamond....@outlook.com>>* wrote ---- > > The solution maybe work. However, the play list will grow over time and > somebody maybe has ten thousands that will slow down the query and sort . > Do you mean the oldest one should be removed when a new play is added? > > BTW, the version is 2.1.16 in our live system. > > > BRs, > > BEN > ------------------------------ > > *发件人:* Vladimir Yudovin <vla...@winguzone.com> > *发送时间:* 2016年11月9日 18:11:26 > *收件人:* user > *主题:* Re: 答复: A difficult data model with C* > > You are welcome! ) > > >recent ten movies watched by the user within 30 days. > In this case you can't use PRIMARY KEY (user_name, video_id), as video_id > is demanded to fetch row, so all this stuff may be > > CREATE TYPE play (video_id text, position int, last_time timestamp); > CREATE TABLE recent (user_name text PRIMARY KEY, play_list > LIST<frozen<play>>); > > You can easily retrieve play list for specific user by his ID. Instead of > LIST you can use MAP, I don't think that for ten entries it matters. > > > Best regards, Vladimir Yudovin, > > *Winguzone <https://winguzone.com?from=list> - Hosted Cloud Cassandra > Launch your cluster in minutes.* > > > ---- On Tue, 08 Nov 2016 22:29:48 -0500*ben ben <diamond....@outlook.com > <diamond....@outlook.com>>* wrote ---- > > Hi Vladimir Yudovin, > > > Thank you very much for your detailed explaining. Maybe I didn't > describe the requirement clearly. The use cases should be: > > 1. a user login our app. > > 2. show the recent ten movies watched by the user within 30 days. > > 3. the user can click any one of the ten movie and continue to watch from > the last position she/he did. BTW, a movie can be watched several times by > a user and the last positon is needed indeed. > > BRs, > > BEN > ------------------------------ > > *发件人:* Vladimir Yudovin <vla...@winguzone.com> > *发送时间:* 2016年11月8日 22:35:48 > *收件人:* user > *主题:* Re: A difficult data model with C* > > Hi Ben, > > if need very limited number of positions (as you said ten) may be you can > store them in LIST of UDT? Or just as JSON string? > So you'll have one row per each pair user-video. > > It can be something like this: > > CREATE TYPE play (position int, last_time timestamp); > CREATE TABLE recent (user_name text, video_id text, review > LIST<frozen<play>>, PRIMARY KEY (user_name, video_id)); > > UPDATE recent set review = review + [(1234,12345)] where user_name='some > user' AND video_id='great video'; > UPDATE recent set review = review + [(1234,123456)] where user_name='some > user' AND video_id='great video'; > UPDATE recent set review = review + [(1234,1234567)] where user_name='some > user' AND video_id='great video'; > > You can delete the oldest entry by index: > DELETE review[0] FROM recent WHERE user_name='some user' AND > video_id='great video'; > > or by value, if you know the oldest entry: > > UPDATE recent SET review = review - [(1234,12345)] WHERE user_name='some > user' AND video_id='great video'; > > Best regards, Vladimir Yudovin, > > *Winguzone <https://winguzone.com?from=list> - Hosted Cloud Cassandra > Launch your cluster in minutes.* > > > ---- On Mon, 07 Nov 2016 21:54:08 -0500*ben ben <diamond....@outlook.com > <diamond....@outlook.com>>* wrote ---- > > > Hi guys, > > We are maintaining a system for an on-line video service. ALL users' > viewing records of every movie are stored in C*. So she/he can continue to > enjoy the movie from the last point next time. The table is designed as > below: > CREATE TABLE recent ( > user_name text, > vedio_id text, > position int, > last_time timestamp, > PRIMARY KEY (user_name, vedio_id) > ) > > It worked well before. However, the records increase every day and the > last ten items may be adequate for the business. The current model use > vedio_id as cluster key to keep a row for a movie, but as you know, the > business prefer to order by the last_time desc. If we use last_time as > cluster key, there will be many records for a singe movie and the recent > one is actually desired. So how to model that? Do you have any suggestions? > Thanks! > > > BRs, > BEN > > > > > >
