Ah- that looks interesting! I'm actaully still on cassandra 2.x but I was planning on updgrading anyway. Once I do so I'll check this one out.
Chris On Thu, Mar 24, 2016 at 2:57 AM, Henry M <henrymanm...@gmail.com> wrote: > I haven't tried the new SASI indexer but it may help: > https://github.com/apache/cassandra/blob/trunk/doc/SASI.md > > > On Wed, Mar 23, 2016 at 2:08 PM, Chris Martin <ch...@cmartinit.co.uk> > wrote: > >> 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 >> >> >> >> >
