Search code examples
cassandramaterialized-views

How do I retrieve the ranking of a user from a materialized view?

I am using Cassandra for storing contest data.

Currently I have a contest table like this (table contest_score):

enter image description here

And I created a materialized views for ranking users in a contest (table contest_ranking):

enter image description here

For get top 10 users of a contest I can simple query select top 10 from contest_ranking;

But how can I get ranking of specific user. For example: user_id = 4 will have rank 2.

Solution

  • The principal philosophy of data modelling in Cassandra is that you need to design a CQL table for each application query. It is a one-to-one mapping between app queries and CQL tables.

    Since you have a completely different application query, you need to create a separate table for it. Here's an example schema:

    CREATE TABLE rank_by_userid (
    user_id int,
    rank int,
    PRIMARY KEY(user_id)
)

    You can then get the rank of a user with this query:

    SELECT rank FROM rank_by_userid WHERE user_id = ?

    You have to manually create and maintain this new table because you won't be able to populate it with materialized views. Cheers!