CQL pagination through User Defined Type

Question

Let say i have this table

CREATE TABLE mykeyspace.post (
  id uuid PRIMARY KEY,
  title text,
  photos set<frozen <photoIds>>
);

and UDT :

CREATE TYPE mykeyspace.photoIds (
  photoId uuid,
  details text
);

How can I paginated through photos, means 10 photos at a time for given post id ?

What do you mean by 'paginated through photos' ? Do you mean you want Cassandra to return 10 photos set at a time ? Or do you mean that for a given id and title you want to paginate through the set of photos ? — Adrien Piquerez, Oct 21 '15 at 07:23
Let say a post X can have 100 photos. So `photos` will have some thing like `[id1, id2,..id100]` So i want to get 1..10, 11..20, 21..29 image id for the given post id x — Manish Kumar, Oct 21 '15 at 07:29

score 3 · Answer 1 · answered Oct 21 '15 at 07:47

3

Paging through collections is not supported.

See reference manual:

Keep collections small to prevent delays during querying because Cassandra reads a collection in its entirety. The collection is not paged internally.

As discussed earlier, collections are designed to store only a small amount of data.

answered Oct 21 '15 at 07:47

Stefan Podkowinski

5,206
1
20
25

but `photos` is UDT not collection – Manish Kumar Oct 21 '15 at 07:55
If I understand you correctly, you're trying to iterate/page through the _set_ of photos, i.e. the actual collection of UDTs. – Stefan Podkowinski Oct 21 '15 at 08:20
so u are saying UDT is collection internally ? – Manish Kumar Oct 21 '15 at 08:24
@Manish `photos set>` In your CQL you defined photoIds as a `set`. So yes, that makes it a collection (set of UDT). – Aaron Oct 21 '15 at 13:25

Adrien Piquerez · Answer 2 · 2015-10-21T08:40:12.507

1

May I propose another schema for your table post :

CREATE TABLE mykeyspace.post (
  id uuid,
  title text static,
  photo photo,
  PRIMARY KEY (id, photo)
);

CREATE TYPE mykeyspace.photo (
  id uuid,
  details text
);

This schema means :

There is one partition by id => a partition is equivalent to a post
There is one title by partition/post
There is multiple photo ids by partition/post

This schema should serve your goal very well until you reach about 100.000 photos by partition/post. If you have never used static columns before, you can refer to Datastax documentation

The driver can do the paging for you. See Paging Feature in Datastax Java driver documentation.

The Cql query looks like this :

select photo.id, photo.details from post where id=*your_post_id*;

PS : I think you should not use uppercase in your schema.

edited Oct 21 '15 at 08:40

answered Oct 21 '15 at 08:06

Adrien Piquerez

1,009
10
11

I cant paginate through UTD type ? – Manish Kumar Oct 21 '15 at 08:23
here u are getting all photos at once not 10 at one time – Manish Kumar Oct 21 '15 at 08:34
1

The paging is done by the driver. You can find how to [there](https://datastax.github.io/java-driver/2.0.10/features/paging/) – Adrien Piquerez Oct 21 '15 at 08:37

CQL pagination through User Defined Type

2 Answers2