how can I search NOT CONTAINS in a set ? Say I have the following model:
case class ClassRoom(id:String, age:Int, name:String , kids: Set[String])
abstract class PersonModel extends CassandraTable[PersonModel, Person] {
override def tableName = "ClassRooms"
object id extends StringColumn(this) with PartitionKey[String]
object age extends DoubleColumn(this) with PrimaryKey[Double]
object kids extends SetColumn[String](this)
I want to do the following query
def findMissing(minAge:Double, kid:String) = select
.where(_.age > age)
.and (_.kids not contain kid)
.fetch()
Sadly because in Cassandra the engine is based on "map style" indexing, the NOT operator is not an inherently suitable concept. Arguably it could be exposed through things like the key cache for partition key hits, but for things like secondary indexes they may not be a technical reality.
First of all, you will need object kids extends SetColumn[String](this) with Index[Set[String]] for the normal contains to work, hence my mention of a secondary index.
To achieve what you want, based on cardinality you have two ways, you either use secondary indexes and a normal CONTAIN or you de-normalise and store in a separate table, which may improve performance but it will still require "diffing".