Ordering by username in Cassandra

Question

Let's say I have this table:

CREATE TABLE "users" (
    username text,
    created_at timeuuid,
    email text,
    firstname text,
    groups list<text>,
    is_active boolean,
    lastname text,
    "password" text,
    roles list<text>,
    PRIMARY KEY (username, created_at)
) 

I want to order users by username, which is not possible as ordering is only possible via the clustering column. How can I order my users by username?

I need to query users by username, so that is the reason, why username is the indexing column.

What is the right approach here?

Answer 1

If you absolutely must have the username sorted, and return all usernames in one query then you will need to create another table for this effect:

CREATE TABLE "users" (
field text,
value text,
PRIMARY KEY (field, value)
)

Unfortunately, this will put all the usernames in just one partition, but it's the only way of keeping them sorted. On the other hand, you could expand the table to store different values that you need to retrieve in the same way. So for instance, the partition field="username" would have all the usernames, but you could create another partition field="Surname" to store all the usernames sorted.

Cassandra is NoSQL, so duplication of data can be expected.