2 Replies Latest reply: Jul 30, 2013 5:29 AM by alexey.ragozin RSS

    Coherence Filtering?

    WhatsGoingOnHere

      I have a simple Cache with 5 fields.  One field being a TimeStamp field.  In my Java App, how would I query this cache for the following SQL?

       

      Select * from cache where TimeStampField = ( select distinct(max("TimeStampField)) from Cache)

       

      I can't seem to find anything on how to filter like this.

       

      Any help appreciated.

       

      Thanks

        • 1. Re: Coherence Filtering?
          Jonathan.Knight

          Hi,

           

          There is not a way to do that in a single "select" statement. I assume you are doing this in code and not using CohQL. You need to do it in two parts, first get the maximum date, then do the query. There are two ways to do this, depending on how efficient you want to be.

           

          First the slightly less efficient way using reflection...

           

          ValueExtractor extractor = new ReflectionExtractor("getTimestampField");

          Timestamp maxTimestamp = (Timestamp) cache.aggregate(AlwaysFilter.INSTANCE, new ComparableMax(extractor));

          Filter filter = new EqualsFilter(extractor, maxTimestamp);

          Set<Map.Entry> entries = cache.entrySet(filter);

           

          You could improve the efficiency a bit by adding an index before the above code...

           

          ValueExtractor extractor = new ReflectionExtractor("getTimestampField");

          cache.addIndex(extractor, true, null);

           

          If you are using POF serialization then you would be more efficient using POF extractors, for example if the POF ID of the timestamp field was 5 then...

           

          ValueExtractor extractor = new PofExtractor(null, 5);

          Timestamp maxTimestamp = (Timestamp) cache.aggregate(AlwaysFilter.INSTANCE, new ComparableMax(extractor));

          Filter filter = new EqualsFilter(extractor, maxTimestamp);

          Set<Map.Entry> entries = cache.entrySet(filter);

           

          Again, you could improve this further by adding an index first...

           

          ValueExtractor extractor = new PofExtractor(null, 5);

          cache.addIndex(extractor, true, null);

           

          Hope that helps,

          JK

          • 2. Re: Coherence Filtering?
            alexey.ragozin

            Some time ago I have written special index exactly for that kind of task.

            It is on GitHub  now, https://github.com/gridkit/coherence-search-timeseries

            See also usage example https://code.google.com/p/gridkit/wiki/TimeSeriesIndex and presentation from SIG Alexey Ragozin: Coherence SIG: Advanced usage of indexes (at the end of slide deck).