simple query with brackets - how to build?

Question

I'm trying to build a query to use for a search engine, that would look like this one:

SELECT * FROM sometable

WHERE col1 = 1 AND col2 = 2 AND (col3a = 3 OR col3b = 3 OR col3c = 3)

I though the code below would work:

SubSonic.Query query = new SubSonic.Query("sometable");
query = query.WHERE("col1", 1);
query = query.WHERE("col2", 2);
query = query.AND("col3a = " + 3).
  OR("col3b = " + 3).
  OR("col3c = " + 3);

but it doesn't as it results in something like this:

SELECT * FROM sometable
WHERE col1 = 1
AND   col2 = 2
AND   col3a = 3
OR    col3b = 3
OR    col3c = 3

How can I build a query I need?

Answer 1

You can create two one query first that include all OR's after that from the resulting view..you can filter out AND condition..I dint check it out..but hope it will work...

Answer 2

I don't know SubSonic, but would this work?

query = query.AND("col3a = " + 3 + " OR col3b = " + 3 + " OR col3c = " + 3);

You could easily build that substring programmatically.

Answer 3

I think you are meant to use WhereExpression/AndExpression/OrExpression to nest expressions reading the documentation but Ive never used it so cant say for sure. Try the below and see if it works

SubSonic.Query query = new SubSonic.Query("sometable");
query = query.WHEREEXPRESSION("col1", 1);
query = query.AND("col2 = " + 2);
query = query.ANDEXPRESSION("col3a = " + 3).
  OR("col3b = " + 3).
  OR("col3c = " + 3);

Answer 4

The following should be pretty close to what you want, if OpenExpression/CloseExpression is supported in 2.0:

SubSonic.Query query = new SubSonic.Query("sometable");
  .WHERE("col1", 1);
  .AND("col2", 2);
  .AND("col3a = " + 3).
  .OpenExpression()
    .OR("col3b = " + 3).
    .OR("col3c = " + 3);
  .CloseExpression()

Answer 5

I see it's not as simple as I expected, at least not in the version I have so I decided to loop through the DataSet manually to filter out the records from the last check. Unless there is a better way?

Answer 6

I would recommend you to upgrade to SubSonic 2.2... The new query functions added in 2.1 are much more powerful.