You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-dev@db.apache.org by Brendan Richards <br...@designuk.com> on 2004/02/12 12:10:16 UTC
Building complex negated queries with Criteria
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
Re: Building complex negated queries with Criteria
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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
Re: Building complex negated queries with Criteria
Posted by Jakob Braeuchi <jb...@gmx.ch>.
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