help with queries

[Dave Paroulek]

Hi all, I'm really excited about this project, thanks so much for all

your work! 

(sorry if I double posted, I thought I submitted this once but I didn't see it show up in the list of topics)

I have a few questions about querying. Here's a sample of an object

stored in the db: 

{

    "@type": "d", "@rid": "#7:0", "@version": 0, "@class": "DbOutboundEmail",

    "emailType": "BULK",

    "priority": "LOW",

    "transactions": [

      {

        "@type": "d", "@version": 0, "@class": "SmtpTransaction",

        "state": "NEW",

        "lastResult": "PENDING",

        "attempts": 1,

        "maxAttempts": 5,

        "nextTry": "2012-04-06 12:11:56:113",

        "dateByAttempt": ["2012-03-15 00:00:00:000"],

        "smtpHostByAttempt": ["host"],

        "smtpCodeByAttempt": [200],

        "smtpResponseByAttempt": ["response"],

        "timeoutAt": null,

        "@fieldTypes": "nextTry=t"

      }],

    "messageId": "1",

    "from": null,

    "to": null,

    "cc": null,

    "bcc": null,

    "subject": null,

    "data": null

  }

I've been using the "studio" web ui to test. Here are some

observations/questions: 

1) Is this the correct syntax (If so I don't see any results for this)? 

select from DbOutboundEmail where transactions.state = "NEW"

If I add an index, it returns results, but is there a way to search

all indexes inside the list of transactions?

select from DbOutboundEmail where transactions[0].state = "NEW"

2) Is there special syntax for querying dates? For example, this

   returns results: 

select from DbOutboundEmail where transactions contains (lastResult = "PENDING" AND state = "NEW")

But, this returns 0 results? I expect it to return the object listed above?

select from DbOutboundEmail where transactions contains (nextTry = "2012-04-06 12:11:56:113")

3) I need to be able to query for all objects where

   transactions.nextTry < then some date. This doesn't return any results?

select from DbOutboundEmail where transactions contains (nextTry.toDateTime < "2012-05-06 12:11:56:113")

If I put parenthesis, it throws exception?

select from DbOutboundEmail where transactions contains (nextTry.toDateTime() < "2012-05-06 12:11:56:113")

Ultimately, I'd like to write a query that returns results that match

where transactions.nextTry is less than some date, transactions.state

equals "NEW" ordered by transactions.nextTry and priority. But I think

I might not understand completely about writing queries. Any guidance

and clarification will help a lot! 

Thanks!

Dave

[Raman Gupta]

Hi Dave, I know you :)  My responses below...

I think this should be something like this (not tested):

select from DbOutboundEmail where transactions CONTAINS (state = "NEW")

See http://code.google.com/p/orient/wiki/SQLWhere.

 

select from DbOutboundEmail where transactions[0].state = "NEW"

2) Is there special syntax for querying dates? For example, this

   returns results: 

select from DbOutboundEmail where transactions contains (lastResult = "PENDING" AND state = "NEW")

But, this returns 0 results? I expect it to return the object listed above?

select from DbOutboundEmail where transactions contains (nextTry = "2012-04-06 12:11:56:113")

Are you passing the date as a String? You should be using a bind parameter with a Date object. 

 

3) I need to be able to query for all objects where

   transactions.nextTry < then some date. This doesn't return any results?

select from DbOutboundEmail where transactions contains (nextTry.toDateTime < "2012-05-06 12:11:56:113")

Same as above, the date should be a Date Object via a bind parameter.

 

If I put parenthesis, it throws exception?

select from DbOutboundEmail where transactions contains (nextTry.toDateTime() < "2012-05-06 12:11:56:113")

Ultimately, I'd like to write a query that returns results that match

where transactions.nextTry is less than some date, transactions.state

equals "NEW" ordered by transactions.nextTry and priority. But I think

I might not understand completely about writing queries. Any guidance

and clarification will help a lot! 

Thanks!

Dave

Regards,

Raman Gupta

Principal

VIVO Systems

[Luca Garulli]

Ops,

this message has been sent twice. My answer is on the other one.

The Raman's suggestions should work too.

Lvc@

[Dave Paroulek]

I think the explanation of why the queries weren't working for me can be mostly explained by the acronym: PEBCAT ;-)

Thanks for your and Raman's suggestions, here's a working version of the query I was after: 

                    return db.command(new OSQLSynchQuery<DbOutboundEmail>("select from DbOutboundEmail where " +

                        "transactions CONTAINS (state = ? AND nextTry < ?) ORDER BY priority ASC, " +

                        "transactions.nextTry ASC LIMIT " + count)

                        .setFetchPlan(FETCH_PLAN_ALL_RECURSIVE))

                        .execute("NEW",

                            new Date());

Also, just in case others might stumble upon this thread in the future, here are some notes that might be helpful. Please feel free to correct me on any of these bullets, but, otherwise, hope they're helpful: 

- I found the syntax page in the wiki to be very helpful: http://code.google.com/p/orient/wiki/SQLSyntax

- dotted syntax such as `transactions.state` works in projections and order by clause, but in where clause use CONTAINS (and/or CONTAINSALL)

- I confirmed this works and here's an example of how to specify dates in studio: 

select from DbOutboundEmail where transactions contains (nextTry > date("2012-01-15 12:00:00:000", "yyyy-MM-dd HH:mm:ss:SSS") )

- When in doubt, add a space! For example, the space surrounding equals sign in where clause is significant. Also the last space between parenthesis is necessary in previous bullet's example.

- Remember to add comma to separate ORDER BY conditions. If the comma in the ORDER BY clause is missing, no exception is thrown, and the second "order by" condition doesn't appear to have any effect. 

- seems that the OCommandRequest.setLimit behaves slightly differently than specifying LIMIT in the sql query. For example, given 3 records with priority HIGH, MED, and LOW then `ORDER BY PRIORITY LIMIT 1` alphabetically sorts and returns the HIGH record (which is expected). But OCommandRequest.setLimit(1) returns the LOW record (unexpectedly). Haven't had time to dig into exact reason why, but seems sql LIMIT works fine. 

Dave

[Luca Garulli]

On 9 April 2012 22:27, Dave Paroulek <dpar...@gmail.com> wrote:

- dotted syntax such as `transactions.state` works in projections and order by clause, but in where clause use CONTAINS (and/or CONTAINSALL)

Hi Dave,

thanks for this extra-wiki suggestions.

dotted syntax should behave like CONTAINS () but if you've multiple conditions, then CONTAINS/CONTAINS ALL is the only way to do it right.

 

- I confirmed this works and here's an example of how to specify dates in studio: 

select from DbOutboundEmail where transactions contains (nextTry > date("2012-01-15 12:00:00:000", "yyyy-MM-dd HH:mm:ss:SSS") )

Good

 

- When in doubt, add a space! For example, the space surrounding equals sign in where clause is significant. Also the last space between parenthesis is necessary in previous bullet's example.

You're right: there are places where a clean text is needed like before/after operators.

 

- Remember to add comma to separate ORDER BY conditions. If the comma in the ORDER BY clause is missing, no exception is thrown, and the second "order by" condition doesn't appear to have any effect. 

Fixed in SVN trunk ;-) 

- seems that the OCommandRequest.setLimit behaves slightly differently than specifying LIMIT in the sql query. For example, given 3 records with priority HIGH, MED, and LOW then `ORDER BY PRIORITY LIMIT 1` alphabetically sorts and returns the HIGH record (which is expected). But OCommandRequest.setLimit(1) returns the LOW record (unexpectedly). Haven't had time to dig into exact reason why, but seems sql LIMIT works fine. 

Yes,

there was an old thread by Luca Molino arguing about it. Use always SQL LIMIT. Probably setLimit() will be deprecated soon.

 

Dave

Lvc@