I have an Java application with Cassandra db. I'm using Cassandra Pelops. I have a columnFamily cf1, and a lot of columns there. For some of them I created secondary index, so I can use them for search. For search purpose, I created a list of IndexExpression (expressions), for example:
final IndexExpression propertyIdExpression = new IndexExpression(
ByteBuffer.wrap(Bytes.fromUTF8(CassandraIntegrationDAOHelper.COL_PROPERTY_ID).toByteArray()),
IndexOperator.EQ,
ByteBuffer.wrap(Bytes.fromInt(entity.getProperty().getId()).toByteArray())
);
expressions.add(propertyIdExpression);
Now I need to include some additional check. I have columns dateFrom and dateTo. Requests is to return all rows where this two dates are in some interval. They don't have to be completely in the interval, important is to start or end in this interval. So, I need to somehow implement something like this:
if( (dateTo is greaterThan intervalStart) or (dateFrom is lowerThan intervalEnd) )
by using IndexExpression. Any suggestions? I hope I was clear! Thanks in advance!
Cassandra does not support disjunction ("or") in secondary index queries at this time, only conjunction ("and"). In most cases, it's sufficient to make two separate queries with each half of the disjunction and combine the results.
However, if you're dealing with time ranges, that's something you should be using column names for, not secondary indexes. I suggest checking out some of the documentation on time series data and thinking about how to restructure your data to support efficient queries based on time ranges.