Hi all, I have a table that represents a train timetable and looks a bit like this:
CREATE TABLE routes ( start text, end text, validFrom timestamp, validTo timestamp, PRIMARY KEY (start, end, validFrom, validTo) ); In this case validFrom is the date that the route becomes valid and validTo is the date that the route that stops becoming valid. If this was SQL I could write a query to find all valid routes between New York and Washington from Jan 1st 2016 to Jan 31st 2016 using something like: SELECT * from routes where start = 'New York' and end = 'Washington' and validFrom <= 2016-01-31 and validTo >= 2016-01-01. As far as I can tell such a query is impossible with CQL and my current table structure. I'm considering running a query like: SELECT * from routes where start = 'New York' and end = 'Washington' and validFrom <= 2016-01-31 And then filtering the rest of the data app side. This doesn't seem ideal though as I'm going to end up fetching much more data (probably around an order of magnitude more) from Cassandra than I really want. Is there a better way to model the data? thanks, Chris
