You could store the order as the first part of a composite string say first picture as A and second as B. To insert one between call it AA. If you shuffle alot the strings could get really long.
Might be better to store the order in a separate column. Neither solution mentioned deals with concurrent access well. On Friday, December 21, 2012, Adam Venturella <[email protected]> wrote: > One more link that might be helpful. It's a similar system to photo's but instead of Photos/Albums it's Songs/Playlists: > http://www.datastax.com/dev/blog/cql3-for-cassandra-experts. > > It's not exactly 1:1 but it covers related concepts in making it work. > > > On Fri, Dec 21, 2012 at 8:02 AM, Adam Venturella <[email protected]> wrote: > > Ok.. So here is my latest thinking... Including that index: > CREATE TABLE Users ( > user_name text, > password text, > PRIMARY KEY (user_name) > ); > ^ Same as before > CREATE TABLE Photos( > user_name text, > photo_id uuid, > created_time timestamp, > data text, > PRIMARY KEY (user_name, photo_id, created_time) > ) WITH CLUSTERING ORDER BY (created_time DESC); > ^ Note the addition of a photo id and using that in the PK def with the created_time > Data is a JSON like this: > { > "thumbnail": <url>, > "standard_resolution":<url> > } > > CREATE TABLE PhotosAlbums ( > user_name text, > album_name text, > poster_image_url text, > data text > PRIMARY KEY (user_name, album_name) > ); > ^ Same as before, data represents a JSON array of the photos: > [{"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>}, > {"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>}, > {"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>}, > {"photo_id":..., "thumbnail":<url>, "standard_resolution":<url>}] > > CREATE TABLE PhotosAlbumsIndex ( > user_name text, > photo_id uuid, > album_name text, > created_time timestamp > PRIMARY KEY (user_name, photo_id, album_name) > ); > The create_time column here is because you need to have at least 1 column that is not part of the PK. Or that's what it looks like in my quick test. > ^ Each photo added to an album needs to be added to this index row > > As before, your application will need to keep the order of the array in tact as your users modify the order of things. Now however if they delete a photo you need to fetch the PhotoAlbums the photo existed in and update them accordingly: > SELECT * FROM PhotosAlbumsIndex WHERE user_name='the_user' AND photo_id="<uuid>" > This should return to you all of the albums that the photo was a part of. Now you need to: > SELECT * FROM PhotosAlbums where user_name = "the_user" and album_name IN
