Search code examples
cassandracqlcql3

CQL SELECT cannot handle "in" for composite columns

I am struggling with cassandra and CQL. I am defining this table schema,

CREATE TABLE useradvisits (
  userid text,
  adid int,
  type int,
  timestamp timestamp,
  PRIMARY KEY (userid, adid, type)
 );

I want to query like this, 

SELECT * from useradvisits where userid = 'user-1' and adId in (100, 1001);

But it says, Bad Request: PRIMARY KEY part adid cannot be restricted by IN relation

I am working with latest datstax enterprise version 3.1.

cqlsh console says,

[cqlsh 3.1.2 | Cassandra 1.2.6.1 | CQL spec 3.0.0 | Thrift protocol 19.36.0]

As I need (adId + type) to be unique, that is (100 : 1), (100 : 2) both be present for a user. Can anyone help me how to workaround?

Solution

  • You created a composite column key from adid and type. You could add either to the row key to create a compositum. Just create a model with PRIMARY KEY ((userid, adid), type) and your query will work. Downside to this approach: You will always have to specify the adid.

    You could also do the opposite and add type to the row key instead. If you knew the range of "type" in advance, this could work even better.