CODEWITHSUNDEEP
cassandracqldatastax-java-driver
Manish Kumar
Manish Kumar

Reputation: 10482

CQL pagination through User Defined Type

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 ?

Upvotes: 0

Views: 89

Answers (2)

Adrien Piquerez
Adrien Piquerez

Reputation: 1044

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.

Upvotes: 1

Stefan Podkowinski
Stefan Podkowinski

Reputation: 5249

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.

Upvotes: 3

Related Questions