Query selection on date

[shashi kant]

I have just started using CQEngine and not able to find how to do the queries on specific dates. What I am looking for is something like "select * from XXXX where someField = '12-Jan-2017'" or something like "select * from XXXX where someField < 23-Jan-2013 09:10:15"

Thanks,

Shashi 

[Niall Gallagher]

Hi Shashi, you just need to create a Date object representing the date in your query, and supply that as the argument to lessThan()/greaterThan() etc. Hope that helps

Sent from my Android

--
-- You received this message because you are subscribed to the "cqengine-discuss" group.
http://groups.google.com/group/cqengine-discuss
---
You received this message because you are subscribed to the Google Groups "cqengine-discuss" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cqengine-discu...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Vivian Oliveres]

Hi Niall,

Could you please post a code exemple? 

I have exactly the same problem (with LocalDate) and cannot find a solution.

Thanks,

Vivian

private static final Attribute<TickSummary, ChronoLocalDate> DATE = new MultiValueAttribute<TickSummary, ChronoLocalDate>("date") {
    @Override
    public Iterable<ChronoLocalDate> getValues(TickSummary o, QueryOptions queryOptions) {
        return o.getDetails().stream().map(tick -> tick.getDate()).collect(Collectors.toList());
    }
};

private static void queryByCqnString(IndexedCollection<TickSummary> summaries) {
    System.out.println("******* Query by CQN string *******");

    Map<String, Attribute<TickSummary, ?>> attributes = Maps.newHashMap();
    attributes.put("date", DATE);

    CQNParser<TickSummary> parser = CQNParser.forPojoWithAttributes(TickSummary.class, attributes);
    String queryAsString = "greaterThan(\"date\", \"LocalDate.parse(2015-04-20)\")";
    System.out.println(queryAsString);
    Query<TickSummary> query = parser.query(queryAsString);
    DeduplicationOption deduplication = deduplicate(DeduplicationStrategy.MATERIALIZE);
    ResultSet<TickSummary> resultSet = summaries.retrieve(query, queryOptions(deduplication));
    resultSet.forEach(summary -> System.out.println(summary));
}

Exception in thread "main" com.googlecode.cqengine.query.parser.common.InvalidQueryException: Failed to parse query

at com.googlecode.cqengine.query.parser.cqn.CQNParser.parse(CQNParser.java:76)

at com.googlecode.cqengine.query.parser.common.QueryParser.query(QueryParser.java:161)

at com.fast.tests.cpengine.MainCPEngine.queryByCqnString(MainCPEngine.java:95)

at com.fast.tests.cpengine.MainCPEngine.main(MainCPEngine.java:83)

at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

at java.lang.reflect.Method.invoke(Method.java:497)

at com.intellij.rt.execution.application.AppMain.main(AppMain.java:140)

Caused by: java.lang.IllegalStateException: Failed to parse value using a valueOf() method in class 'java.time.chrono.ChronoLocalDate': LocalDate.parse(2015-04-20)

at com.googlecode.cqengine.query.parser.cqn.support.FallbackValueParser.parse(FallbackValueParser.java:43)

at com.googlecode.cqengine.query.parser.common.QueryParser.parseValue(QueryParser.java:128)

at com.googlecode.cqengine.query.parser.common.QueryParser.parseValue(QueryParser.java:113)

at com.googlecode.cqengine.query.parser.cqn.support.CQNAntlrListener.exitGreaterThanQuery(CQNAntlrListener.java:108)

at com.googlecode.cqengine.query.parser.cqn.grammar.CQNGrammarParser$GreaterThanQueryContext.exitRule(CQNGrammarParser.java:1014)

at org.antlr.v4.runtime.tree.ParseTreeWalker.exitRule(ParseTreeWalker.java:71)

at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:54)

at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)

at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)

at org.antlr.v4.runtime.tree.ParseTreeWalker.walk(ParseTreeWalker.java:52)

at com.googlecode.cqengine.query.parser.cqn.CQNParser.parse(CQNParser.java:69)

... 8 more

Caused by: java.lang.NoSuchMethodException: java.time.chrono.ChronoLocalDate.valueOf(java.lang.String)

at java.lang.Class.getMethod(Class.java:1786)

at com.googlecode.cqengine.query.parser.cqn.support.FallbackValueParser.parse(FallbackValueParser.java:39)

... 18 more

[Zsombor]

Why do you need the query as a string? Use the QueryFactory, it's much more powerful - and simpler to use:

Query<TickSummary> query = QueryFactory.greaterThan(DATE, LocalDate.of(2015,4,20));

Best regards,

 Zsombor

[Vivian Oliveres]

I agree with you. The QueryFactory is great !

But in my case, it is simpler to build a string query because queries come from a GUI (a table) and will be dynamically computed (according to user selection, filters, sort, etc). 

So it is much easier to build a String instead of an object but if there is no other alternative I will do it.

Regards,

Vivian

[Sam Bishop]

It looks like there are two issues.  One, just pass in the date itself.  ("2015-04-20" instead of "LocalDate.parse(2015-04-20)".)  Two, you'll need to register a custom ValueParser for ChronoLocalDate objects.

ValueParser is an abstract class in the com.googlecode.cqengine.query.parser.common package.  Implement the parse() method.  Then pass an instance of that class to the registerValueParser() method on your CQNParser object.

[Zsombor]

Easier to build a string? I don't think so. Creating string queries quickly became very complicated, because you have to properly escape everything, have to remember to put ' and ' in appropriate places, with parenthesis, etc.

And in the end, you get an untestable, string creator mass, which fails miserably in execution when something changes.

But with the type-safe Query API, you can easily create complex queries - even from UI !

Something like this:

Query<A> createQuery(ACriteria criteria) {

 Query<A> statements = new ArrayList<>();

 if (criteria.getStartDate() != null) {
    statements.add(QueryFactory.greaterThan(DATE, criteria.getStartDate());
 }

 if (criteria.getEndDate() != null) {
    statements.add(QueryFactory.lessThan(DATE, criteria.getEndDate());
 }

 if (criteria.getSomeID() != null) {

  statements.add(QueryFactory.equal(SOME_ID, criteria.getSomeID());

}
...

if (statements.isEmpty()) {

   return QueryFactory.all();

 } else if (statements.size() == 1) {

   return statements.get(0);

 } else {

   return QueryFactory.and(statements);

 }

}

This patterns works very well, in my experiences.

Regards,

 Zsombor

[Sam Bishop]

On Thu, Feb 18, 2016 at 9:46 AM, Zsombor <gzso...@gmail.com> wrote:

Easier to build a string? I don't think so. Creating string queries quickly became very complicated, because you have to properly escape everything, have to remember to put ' and ' in appropriate places, with parenthesis, etc.

And in the end, you get an untestable, string creator mass, which fails miserably in execution when something changes.

But with the type-safe Query API, you can easily create complex queries - even from UI !

This is definitely the way to go, if you can.  But it doesn't work if the UI is separate from the service running the queries.  (This would happen if the UI were a web app, for instance.)  In that case you need some sort of textual representation of the query to pass from the UI to the server.

[Vivian Oliveres]

Thanks Sam. It works very well the ValueParser.

Just one little curiosity. With the query "greaterThan("date", "2015-04-20")", the string given to ValueParser is ""2015-04-20"". With the two extras ". If I remove them to the query there is a parsing error.

Here is the ValueParser code:

CQNParser<TickSummary> parser = CQNParser.forPojoWithAttributes(TickSummary.class, attributes);

parser.registerValueParser(ChronoLocalDate.class, new ValueParser<ChronoLocalDate>() {
    @Override
    protected ChronoLocalDate parse(Class<? extends ChronoLocalDate> aClass, String s) {
        return LocalDate.parse(s.replaceAll("\"", ""));
    }
});

Thanks a lot!

[Vivian Oliveres]

Yes in fact it is a webapp. The GUI and the server (which contains the CPEngine) are not on the same desktop.

So I could use a String query (easier to serialize and create) or a pojo wich describes the statements.

Is the Query object fully serializable?

[Sam Bishop]

On Thu, Feb 18, 2016 at 10:18 AM, Vivian Oliveres <v.oli...@gmail.com> wrote:

Just one little curiosity. With the query "greaterThan("date", "2015-04-20")", the string given to ValueParser is ""2015-04-20"". With the two extras ". If I remove them to the query there is a parsing error.

Yes, that is a little strange, but it appears to be necessary given the way the grammar was written.  The StringParser class is basically written the same way, so I don't think there's anything wrong with what you're doing.

[Sam Bishop]

On Thu, Feb 18, 2016 at 10:24 AM, Vivian Oliveres <v.oli...@gmail.com> wrote:

Yes in fact it is a webapp. The GUI and the server (which contains the CPEngine) are not on the same desktop.

So I could use a String query (easier to serialize and create) or a pojo wich describes the statements.

Is the Query object fully serializable?

I'm a little puzzled.  I assume by "web app" you mean something written using HTML and Javascript.  In that case I don't see how POJOs and fully serializable objects would be of any help to you.  But no, it doesn't look like query objects are serializable.

[Vivian Oliveres]

Our webapp is in JavaFX but maybe in some time we will have another client in AngularJS...

From now, we need to send a Query to be executed by the server. As you said, it seems that Query object is not serializable. So we have to translate the user query into a serializable object in order to send it to the server. We could use a string or any pojo (with fields like attributeName, filterType (greaterThan, ...), filterValue(date/double/...), sortType(ascending/descending), etc). 

Do you see what I mean?

[Zsombor]

It's trivial to convert a POJO to a JSON, and vice-verse. For example, in Spring MVC you don't have to do anything, just specify that you expect your criteria objects arrive as a POST call's body.

Sending strings over the wire, and blindly executing as a query, is a very dangerous thing to do. At least, you could expose a lot's of your data, and it's equivalent of 'sql injection' type of bugs.

[Vivian Oliveres]

Ok I see.

Thanks for your advice :)

[Niall]

Hi Vivian,

Just to add, if you have a choice of which string-based query dialect to use in a new project, would it be easier for your users if you go with SQL instead CQN?

The CQN dialect is fully supported etc, but it intentionally looks like Java code and it's actually based on an Antlr grammar for the Java programming language. It's because the idea is that you can copy-paste a CQN query into an IDE and it should compile as Java code using QueryFactory, and vice-versa. So CQN queries are not as easy to write outside of an IDE.

OTOH, you can probably get javascript libraries to do syntax highlighting for SQL queries inside a browser, which could make things easier.

Both dialects are fully supported though.

[Sam Bishop]

On Thu, Feb 18, 2016 at 11:21 AM, Vivian Oliveres <v.oli...@gmail.com> wrote:

From now, we need to send a Query to be executed by the server. As you said, it seems that Query object is not serializable. So we have to translate the user query into a serializable object in order to send it to the server. We could use a string or any pojo (with fields like attributeName, filterType (greaterThan, ...), filterValue(date/double/...), sortType(ascending/descending), etc). 

Do you see what I mean?

I do.  In fact, I am doing exactly what Zsombor suggests (between my server app and an Angular UI): passing the query as JSON.  It's still a work in progress, and I have to wrap CQEngine in a framework that also supports operations like aggregation, but so far it hasn't been bad.  Good luck!