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?

score 1 · Accepted Answer · answered Feb 01 '18 at 15:34

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.

score 0 · Answer 2 · answered Jan 31 '18 at 18:16

0

Cassandra stores the partition key data by hashing the value. So when the data is returned, the order is done by the hash values and not order of the data itself. Thus, you can't order on the partition key.

Coming back to your question, I'm not sure about what kind of data it is and what kind of query you would want to run. Assuming multiple users per email I'd create the following 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 (email, username)

)

answered Jan 31 '18 at 18:16

Bigby

321
5
16

I need to get all users for a list view: select * from users; Would i need to create a second table, like the one in your example and use it for the list view? The select by username queries would be issued on the example table from my original question? – Alex Tbk Jan 31 '18 at 19:19
if you want `username` as the unique partition key, then `select * from users` would work from the original table. If in future your query gets complex then create another table with different primary keys. – Bigby Jan 31 '18 at 20:01
Yes, i know that select * from users works with my original table and username as the partition key. The problem ist that the result is not sorted. I need a table, which is sorted by username when i do a select * from users; on it – Alex Tbk Jan 31 '18 at 20:26

Ordering by username in Cassandra

2 Answers2