CQL SELECT cannot handle “in” for composite columns

3
votes

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?

2
cassandracqlcql3

2 Answers

7
votes

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.

0
votes

I have change the table this way, using composite partition key.

 CREATE TABLE useradevents ( 
    userid varchar,        
    type int, 
    adId int,     
    timestamp timestamp,            
    PRIMARY KEY((userid, type), adId));

Now it query works like a charm. One think it that you need to restrict type value, which in my case takes only 3 different values.

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

Thanks @omnibear !