Is possible to add Parentheses on the Wheres?

[Josejulio Martínez]

Following on my other post (about joins) i would like to know how i can form the following query...

SELECT * FROM invoices 

WHERE mydate between 12345 and 1234 and (uuid like '%ASDF%' OR invoice_id IN ( SELECT invoice_id FROM invoice_client WHERE name = 'somethign' OR rfc = 'else'))

The tricky part is that i don't know how to add those extra ( after the and and close them after the inner query...

[jc]

Gray had posted a good example for grouping conditions here:
http://stackoverflow.com/questions/9963015/multiple-combined-or-conditions-in-ormlite

[Josejulio Martínez]

Great, thank you again.

[Gray Watson]

On May 21, 2012, at 12:53 PM, jc wrote:

> Gray had posted a good example for grouping conditions here:
> http://stackoverflow.com/questions/9963015/multiple-combined-or-conditions-in-ormlite

Thanks JC. That post is dealing with an uncommon pattern however. The best information on how to build complex queries is in the docs:

http://ormlite.com/docs/building-queries

In your case you would do something like:

qb = invoiceDao.queryBuilder();
where = qb.where();
where.and(where.between(Invoice.MYDATE_FIELD, 1234, 12345),
where.or(where.like(Invoice.UUID_FIELD, "%ASDF%"),
where.in(Invoice.ID_FIELD, selectInvoiceIdQuery)
)
);

Notice that the between goes from low to high. Not sure if it works to do it the other way around. Also, where.in() can take a bunch of objects _or_ it can take a sub-query like your example here.

gray

p.s. I was very proud of that StackOverflow post in that I beat Jon Skreet for the check-mark after the fact. Then again, he didn't write ORMLite so maybe I have an advantage? :-)

[jc]

> p.s. I was very proud of that StackOverflow post in that I beat Jon Skreet for the check-mark after the fact.  Then again, he didn't write ORMLite so maybe I have an advantage?  :-)

You should be proud of that post!! I don't think you that syntax in
your docs and it would be helpful since that syntax is a bit tricky
without an example.

On a side note, I had issues trying to get the syntax from the
documents (similar to your example here) to get the QueryBuilder to do
a "where field1=? and (field2=? or field3=? or field4=?)". The
where.or(left, right, other...) syntax always threw errors with more
than 2 or() arguments. I should go back try it again.