Where Clause

[Leng]

I can get objects with method
"getCollectionByAttributesWithEqualTo"
but
How Can I Retrieve Objects With Where Clase with OR,AND,BETWEEN,NOT

[Richard Banks]

To retrieve objects using clauses requires the use of a CRetrieveCriteria object.  The getCollectionByAttributes... methods are just wrappers around a CRetrieveCriteria call.

By default each condition in a CRetrieveCriteria's where condition will be joined using AND.  To use an OR do the following:

1. Create a CCriteriaCondition object
eg:
  dim xx as new CCriteriaCondition
  xx.addSelectEqualTo(,)

2. Add it to your main retrieve criteria.
eg:
  Criteria.WhereCondition.addSelectEqualTo(,)
  Criteria.WhereCondition.addOrCriteria(xx)

To use BETWEEN, NOT, IN, etc simply call the appropriate addXXX method from the CCriteriaCondition object (see the reference documentation on the web site for a comlpete list)

[Leng]

Thank you I Try To Use Your Suggestion It Work
But I Have A little bit Confuse About This Where Clause Statement Like

Where (A=1 or A=2) and (B=3 or B=4 )
or
Where (A IN (1,2) and (B IN (3,4) )
I try

dim xx_1 as new CCriteriaCondition
dim xx_2 as new CCriteriaCondition
dim xx_3 as new CCriteriaCondition
dim xx_4 as new CCriteriaCondition
xx_1.addSelectEqualTo("A",1)
xx_2.addSelectEqualTo("A",2)
xx_1.addOrCriteria(xx_2)
xx_1.addSelectEqualTo("B",3)
xx_3.addSelectEqualTo("B",4)
xx_1.addOrCriteria(xx_3)

It's Not Correct as Where Clause above
How Can I do?

Where Can I Find Document or Sample Code For Using All CCriteriaCondition

[Richard Banks]

I've added an extra method called addAndSubCriteria.

You would use it in your example as
xx_1.addSelectEqualTo("A",1)
xx_2.addSelectEqualTo("A",2)
xx_1.addOrCriteria(xx_2)
xx_3.addSelectEqualTo("B",3)
xx_4.addSelectEqualTo("B",4)
xx_3.addOrCriteria(xx_3)
xx_1.addAndSubCriteria(xx_3)

I need to set up a test to check this.  I'll try to do it later tonight when I get back from the office.

- Richard

[Richard Banks]

OK,  Did a quick test and it looks good.

Here is the code I used

    r = New CRetrieveCriteria
    r.ClassMap = emp.getClassMap(emp)
    xx_1.ClassMap = r.ClassMap
    xx_2.ClassMap = r.ClassMap
    xx_3.ClassMap = r.ClassMap
    xx_4.ClassMap = r.ClassMap
    xx_1.addSelectEqualTo("Name", "aa")
    xx_2.addSelectEqualTo("Name", "ab")
    xx_1.addOrCriteria(xx_2)
    xx_3.addSelectEqualTo("Name", "ab")
    xx_4.addSelectEqualTo("Name", "ac")
    xx_3.addOrCriteria(xx_4)
    r.WhereCondition.addOrCriteria(xx_1)
    r.WhereCondition.addAndSubCriteria(xx_3)
    cursor = r.perform(emp)

The generated SQL looks as follows:

SELECT employee.oid, employee.name, employee.parentoid, employee.teamoid
FROM employee
WHERE  ((((employee.name = 'aa' OR (employee.name = 'ab'))) AND ((employee.name = 'ab' OR (employee.name = 'ac')))))

The excessive bracketing is a result of the OR's.  I may have a look at simplifying this, but since it works it will be a low priority :-)

Hope that helps,
- Richard.

[Leng]

Thank you for you reply i will try to do above
but not i have add .addSelectEqualToList for where condition "A IN (1,2)"

i wish you to solve complex "or condition."

[Richard Banks]

Hi Leng,

CVS has been updated.

There is now an AddSelectInList method for the where condition.

Also, all of the addSelectxxx methods have a new parameter to control wether the criteria is added using an AND or and OR clause.  SubCriteria is also a little simpler and this should offer you all you need to do pretty much anything.

The code for Where (A IN (1,2) and (B IN (3,4) ) is as follows:

dim a1 as new ArrayList
dim a2 as new ArrayList
a1.add(1)
a1.add(2)
a2.add(3)
a2.add(4)
r.WhereCondition.addSelectInList("A",a1)
r.WhereCondition.addSelectInList("B",a2,true)
r.perform....

I hope that makes sense.

You will need to get the latest code from CVS to use the new features and assuming there are no problems I'll release a new version some time next week.

[Leng]

Good And Easy
Thank you very much