Have Geek Brain

How can I construct a complex where object in Subsonic 2.0

I am trying to create a search engine query that looks like this:

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

I though the code below worked:

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 this is not the case as the result:

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

How can I build the query I need?

+1


a source to share


6 answers


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()
+3


a source


I think you should use WhereExpression / AndExpression / OrExpression to express expressions reading the documentation, but I've never used it so I can't say for sure. Try 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);
+2


a source


You can first create two queries that include all ORs after that from the resulting view. You can filter out the AND condition. I am testing it ... but hope it works ...

+1


a source


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

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

You can easily create this substring programmatically.

+1


a source


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

0


a source


I would recommend that you upgrade to SubSonic 2.2 ... The new query features added in 2.1 are much more efficient.

0


a source






More articles:


All Articles