How to model cassandra columnfamily

Question

Is the below select queries are possible for the columnfamily I have defined, because I am getting a bad request error. How should I Model my columnfamily to get the correct results.

CREATE TABLE recordhistory ( userid bigint, objectid bigint, operation text, record_link_id bigint, time timestamp, username text, value map<bigint, text>, PRIMARY KEY ((userid, objectid), operation, record_link_id, time) ) WITH CLUSTERING ORDER BY (operation ASC, record_link_id ASC, time DESC)

Select Query:

SELECT * FROM recordhistory WHERE userid=439035 AND objectid=20011009 AND operation='update' AND time>=1389205800000 AND time<=1402338600000 ALLOW FILTERING;

Bad Request: PRIMARY KEY column "time" cannot be restricted (preceding column "record_link_id" is either not restricted or by a non-EQ relation)

SELECT * FROM recordhistory WHERE userid=439035 AND objectid=20011009 AND record_link_id=20011063 ALLOW FILTERING;

Bad Request: PRIMARY KEY column "record_link_id" cannot be restricted (preceding column "operation" is either not restricted or by a non-EQ relation)

possible duplicate of "Bad Request: PRIMARY KEY part to_id cannot be restricted" when trying to select using where condition — fedorqui 'SO stop harming'
Actually here I am having partition key in query and trying to use ALLOW FILTERING by skipping some of clustering columns. — Aftab

catpaws catpaws · Accepted Answer · 2014-09-30T05:33:48

create table recordhistory (
userid bigint,
objectid bigint,
operation text,
record_link_id bigint,
time timestamp,
username text,
value map<bigint, text>,
PRIMARY KEY ((userid, objectid), time, operation, record_link_id)) WITH CLUSTERING ORDER BY (time DESC, operation ASC, record_link_id ASC);

select * from recordhistory where userid=12346 AND objectid=45646 and time >=1389205800000 and time <1402338700000 ALLOW FILTERING;

 userid | objectid | time                     | operation | record_link_id | username | value
--------+----------+--------------------------+-----------+----------------+----------+-------
  12346 |    45646 | 2014-06-09 11:30:00-0700 |     myop4 |          78946 |    name3 |  null
  12346 |    45646 | 2014-01-08 10:30:00-0800 | myop99999 |         999999 |    name3 |  null

How to model cassandra columnfamily

1 Answers