Cassandra Filter Two Columns

Question

Is there a way to filter greater than one column and less than another, or to model appropriately?

create table foo (id int, low int, high int, primary key (id, low, high));

These queries work:

select * from foo;
select * from foo where id = 1 and  low < 0;

But this query does not:

select * from foo where id = 1 and  low < 0 and high >10;

and results in:

InvalidRequest: Error from server: code=2200 [Invalid query] message="Clustering column "high" cannot be restricted (preceding column "low" is restricted by a non-EQ relation)"

I've currently resorted to allowing filtering. Its on one partition, and traces for scenarios that far exceed the anticipated scenarios execute in acceptable ranges, but I wonder if there is a better way to model the table to all getting the information without resulting to filtering?

Here is the explanation why it does not work - Clustering columns. A possible solution would be to use IN for low column if possible — Horia
Thanks. Well, the low value could be any number honestly, so I don't think IN could work. — MORCHARD

AntMor AntMor · Accepted Answer · 2019-02-06T23:55:16

The only thing that in my opinion, you could do is to do filtering in a Materialized View for the high value as follows:

CREATE MATERIALIZED VIEW IF NOT EXISTS foo_high_first 
AS SELECT id, low, high FROM foo
WHERE id IS NOT NULL AND low IS NOT NULL AND high IS NOT NULL
PRIMARY KEY (id, high, low);

Then you can perform the following query:

cqlsh:my_keyspace> select * from foo_high_first where id = 1 and high > 3;

 id | high | low
----+------+-----
  1 |    6 |   1

In the application, you must do the intersection between the low values and the high values.

Also, keep in mind that you can order the clustering columns, thus having the low and high numbers sorted.

Cassandra Filter Two Columns

1 Answers