Michal
Michal

Reputation:

How can I build a complex where clause in Subsonic 2.0

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

Answers (6)

Johan Kronberg
Johan Kronberg

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

Michal
Michal

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

Adam Cooper
Adam Cooper

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

Trotts
Trotts

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

JimG
JimG

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

sona
sona

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

Related Questions