Pattern #3: Get rid of all NOT operators from sql where clauses.
For example, consider a typical SQL query to find all employees who have NOT completed a mandatory information security training:
select ee1.* from employee ee1
where NOT exists (
select ‘true’ from employee_training et1
where et1.employeeID = ee1.employeeID
and et1.courseName = ‘Information Security’
Cassandra does not support NOT operator. So the way to make this work is to have NOT-condition data and have Cassandra search for it as below:
select * from employee_training
where courseName = ‘No Information Security’
select * from employee
where employeeID = ?
and let the middle-tier object graph builder (I am using Object Persistence API for C*) compose it for you as below:
Collection<Employee> colOfTrainedEmployees = EmployeeTraining.findNotTrainedEmployees()
-> return Employee.findByEmployeeID(e.getEmployeeID())
The split two queries are Cassandra compliant and NOT operator is replaced by additional NOT-condition data in the database.