db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jakob Braeuchi <jbraeu...@gmx.ch>
Subject Re: Building complex negated queries with Criteria
Date Fri, 13 Feb 2004 17:03:02 GMT
hi brendan,

i commited the fix.

hth
jakob

Jakob Braeuchi wrote:

> hi brendan,
> 
> i can confirm that this is a problem.
> 
> after changing Criteria#addAndCriteria to:
> 
>     public void addAndCriteria(Criteria pc)
>     {
>         if (!m_criteria.isEmpty())
>         {
>             this.setEmbraced(true);
>             pc.setEmbraced(true);
>             pc.setType(AND);
>             addCriteria(pc);
>         }
>         else
>         {
>             setEmbraced(false);
>                     pc.setType(AND);
>                     addCriteria(pc);
>         }
>        }
> 
> and SqlQueryStatement#asSQLStatement to:
> 
> ....
>                     case (Criteria.OR):
>                         {
>                             if (statement.length() > 0)
>                             {
>                                 statement.append(" OR ");
>                             }
>                             statement.append(addAtStart);
>                             statement.append(asSQLStatement(pc));
>                             statement.append(addAtEnd);
>                             break;
>                         }
>                     case (Criteria.AND):
>                         {
>                             if (statement.length() > 0)
>                             {
>                                 statement.insert(0, "( ");
>                                 statement.append(") AND ");
>                             }
>                             statement.append(addAtStart);
>                             statement.append(asSQLStatement(pc));
>                             statement.append(addAtEnd);
>                             break;
>                         }
> 
> ....
> 
> the problem seems solved. at least with my testcase.
> 
>         Criteria crit1 = new Criteria();
>         Criteria crit2 = new Criteria();
>         Criteria crit3 = new Criteria();
> 
>         crit2.addEqualTo("lastname","tom");
>         crit2.setNegative(true);
> 
>         crit3.addEqualTo("firstname","tom");
> 
>         crit1.addAndCriteria(crit2);
>         crit1.addAndCriteria(crit3);
> 
>         Query q = QueryFactory.newQuery(Person.class, crit1);
>         Collection results = broker.getCollectionByQuery(q);
> 
> 
> 
> jakob
> 
> 
> Brendan Richards wrote:
> 
>> Hi,
>> I posted this issue to the users list a couple of weeks ago and noone
>> had any ideas so I'm posting here to see if the developers have any
>> suggestions....
>>
>> Here's my original post:
>>
>> I've discovered some odd behavior exhibited by the broker criteria API
>> and I was wondering if anyone had come across this.
>> My system is dynamically building complex queries by building criteria
>> objects one at a time and then pulling them together with addAndCriteria
>> and addOr Criteria.
>> So my code looks something like this pseudocode:
>>
>> Criteria criteria = new Criteria(); // create criteria object
>>
>> while (myQuery.hasMoreElements()) {
>>
>>     Criteria c1 = new Criteria(); // new criteria
>>     C1.addEqualTo("myProperty", myValue);
>>     if (myNegation) {
>>         c1.setNegative(true);
>>     }
>>     criteria.addAndCriteria(c1);
>> }  
>> So this code loops through some objects that I've created and adds a
>> number of criteria to a central criteria object. Each criteria may be
>> negated.
>> My problem is that on the first call to addAndCriteria, a Criteria
>> object is not added to my main criteria, only a ValueCriteria.
>> So say I want to do: WHERE  (NOT forename=brendan) AND (NOT surname IS 
>> NULL)
>> My code would loop twice setting values and setNegative(true) for each
>> sub-criteria. 
>> Looking at my final criteria object via getElements and printing the
>> types Using this code (with Logger being a simple class that writes 
>> output to
>> a file):
>>
>> Enumeration critElements = criteria.getElements();
>>     while (critElements.hasMoreElements()) {
>>         Object testCrit = critElements.nextElement();
>>         Logger.debugLog("type "+testCrit.getClass().getName());
>>         if (testCrit instanceof Criteria) {
>>             Criteria tc = (Criteria) testCrit;
>>             if (tc.isNegative()) {             
>> Logger.debugLog("Criteria is negative");
>>         }
>>         }
>>     }
>> I get the following result in my logfile:
>> type org.apache.ojb.broker.query.ValueCriteria
>> type org.apache.ojb.broker.query.Criteria
>> Criteria is negative
>>
>>
>> Using criteria.toString() I get:
>>
>> [forename = brendan, [surname IS NULL ]]
>>
>>
>>
>>
>> What this means is that I have completely LOST the isNegative value for
>> the first criteria I add. The containing criteria object is not negative
>> (I don't want it to be either I want to control the negativity of each
>> criteria I add).
>>
>> Why does criteria.addAndCriteria convert a Criteria to ValueCriteria
>> when adding the first criteria losing the negative switch?
>>
>> What I'd expect to see from criteria.toString() after the above code is:
>> [[forername = brendan], [surname IS NULL ]] - with both elements being
>> instances of Criteria.
>>
>> Subsequent calls to addAndCriteria() after the first add Criteria
>> objects as you would expect. [ forname=brendan, [surname IS NULL],
>> [email IS NULL] ]
>>
>> There is a simple dodgy workaround I could do where the first criteria I
>> add always evaluates to TRUE but this is far from ideal.
>>
>>
>> </end original post>
>>
>>
>>
>> I've since had a look at the Criteria code and can see that the
>> addAndCriteria and addOrCriteria functions do indeed act very
>> differently for the first criteria you add.
>> public void addAndCriteria(Criteria pc)
>>     {
>>         // by combining a second criteria by 'AND' the existing
>> criteria needs to be enclosed
>>         // in parenthesis
>>         if (!m_criteria.isEmpty())
>>         {
>>             this.setEmbraced(true);
>>             pc.setEmbraced(true);
>>             pc.setType(AND);
>>             addCriteria(pc);
>>         }
>>         else
>>         {
>>                                     setEmbraced(false);
>>             setType(NONE); // root object
>>             addCriteria(pc.getCriteria());
>>                         //addCriteria(pc);
>>             orderby.addAll(pc._getOrderby());
>>             groupby.addAll(pc._getGroupby());
>>         }
>>     }
>>
>> For the first criteria you add to another containing criteria, the
>> negation is lost and also the ordery and groupby settings are passed up
>> to the containing criteria. I'm not sure this is the behaviour I'd want
>> as I'll potentially want to build very complex criteria from a number of
>> sub-criteria to a number of levels and order-by only really applies to
>> the top level.
>>
>> I've tried playing with this code changing the block for an empty
>> criteria to add a full embraced criteria object but this always fails
>> with an SQL error. I suspect that where a criteria resolves to SQL it
>> needs to have a simple criteria object (such as a ValueCriteria) as the
>> first element is this correct.
>> I've come up with a quick-and-dirty solution to my problem by adding an
>> alternative addAndCriteria variant that inserts a sql criteria into the
>> first element:
>>
>> /**
>>      * ANDs two sets of criteria together but with first remaining
>> embraced.
>>          * Alternative version fixes embraced loss of first critera add
>> by adding dummy true clause as first element.
>>          * This doesn't pass up orderby settings for first entry.
>>          *      *
>>      * @param  pc criteria
>>      */
>>     public void addAndCriteriaEmbraced(Criteria pc)
>>     {
>>         // by combining a second criteria by 'AND' the existing
>> criteria needs to be enclosed
>>         // in parenthesis
>>         if (!m_criteria.isEmpty())
>>         {
>>             this.setEmbraced(true);
>>             pc.setEmbraced(true);
>>             pc.setType(AND);
>>             addCriteria(pc);
>>         }
>>         else
>>         {
>>                         this.addSql("1>0");
>>             this.setEmbraced(true);
>>             pc.setEmbraced(true);
>>             pc.setType(AND);
>>             addCriteria(pc);
>>         }
>>     }
>>
>>
>> I'm not too sure about the elegance of this solution and what I may
>> potentially be breaking...
>> Also I haven't ruled out the case that I may be conceptually looking at
>> this criteria api from the wrong angle.
>> If anyone has any ideas I'd be grateful for some suggestions.
>> Thanks,
>>
>>
>> -----------------------------------------
>> Brendan Richards
>> Design UK
>> -----------------------------------------
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
>> For additional commands, e-mail: ojb-dev-help@db.apache.org
>>
>>
> 
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-dev-help@db.apache.org
> 
> 

---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message