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 Thu, 12 Feb 2004 19:18:46 GMT
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


Mime
View raw message