Reputation:
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?
Upvotes: 1
Views: 1069
Reputation: 1086
I would recommend you to upgrade to SubSonic 2.2... The new query functions added in 2.1 are much more powerful.
Upvotes: 0
Reputation:
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?
Upvotes: 0
Reputation: 8677
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()
Upvotes: 3
Reputation: 625
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);
Upvotes: 2
Reputation: 1782
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.
Upvotes: 1
Reputation: 175
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...
Upvotes: 1