Cannot sort partitions in DjangoCassandraModel, "ORDER BY is only supported when the partition-CodePudding

from cassandra.cqlengine import columns
from django_cassandra_engine.models import DjangoCassandraModel
from django.db import connections

    def get_tag(car_name, car_type):
        class Tag(DjangoCassandraModel):
            __table_name__ = car_name   "_"   car_type
    
            time = columns.Integer(primary_key=True, required=True)  # unix time in seconds  # noqa
            value = columns.Float(required=True)
    
            with connections['cassandra'].cursor() as cursor:
                cursor.execute("CREATE TABLE IF NOT EXISTS mydatabase."   __table_name__   " (time INT PRIMARY KEY, value FLOAT);")  # noqa
    
            def last():
                with connections['cassandra'].cursor() as cursor:
                    elem = cursor.execute("SELECT * FROM "   Tag.__table_name__   " ORDER BY time DESC LIMIT 1;")  # noqa
                return elem
    
            def __str__(self) -> str:
                return str(self.time)   ","   str(self.value)
    
        return Tag

Usage in code:

tag = get_tag(car_name, car_type)
last_elem = tag.last()

Produced error when calling tag.last():

cassandra.InvalidRequest: Error from server: code=2200 [Invalid query] message="ORDER BY is only supported when the partition key is restricted by an EQ or an IN."

Hello. I have a dynamic Django model creation in Cassandra. (time, value) measurements. How to get the last element in the table based on time (primary key)? My implementation has an error.

CodePudding user response：

As the error message states, the ORDER BY clause only works when you are sorting the rows within a single partition, specifically when "the partition key is restricted".

In your case, you are doing a full table scan (no WHERE clause) since the partition key is not restricted to a single partition:

SELECT * FROM ... ORDER BY time DESC LIMIT 1

You cannot sort based on the partition key time since that means Cassandra has to retrieve all partitions from all nodes in order to sort them in descending order.

You can only sort on a clustering column. For example, if you had a table of phone numbers:

CREATE TABLE phone_numbers_by_username (
  username text,
  phone_type text,
  phone_number text,
  PRIMARY KEY (username, phone_type)
)

Then you can sort by phone type (home, mobile, work) if you restrict the query to a single partition (username):

SELECT * FROM phone_numbers_by_username
  WHERE username = ?
  ORDER BY phone_type DESC

However, this isn't possible in your case since each partition in your table only has a single row (no clustering columns in the PRIMARY KEY). Cheers!