db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dale Thoms" <dth...@pobox.com>
Subject Re: Criterion query gives bizarre results
Date Wed, 11 Feb 2004 21:55:28 GMT
 
Bruce,

What version of Torque are you using?

I tried to duplicate your problem and got something like this:
((((a1 AND a2) OR (b1 AND b2)) OR (c1 AND c2)) OR (d1 AND d2))

Also, it looks like you might want to swap the LESS_EQUAL/GREATER_EQUAL
operators
(assuming you want: startdate => a1 and enddate <= a2).


   Dale


On Wed, 11 Feb 2004 04:44:05 -0500, "Bruce Altner" <baltner@hq.nasa.gov>
said:
> Greetings:
> 
> I've been experiencing problems with a fairly simple query lately
> involving 
> a single table but several combinations of "OR" and "AND" so I checked
> the 
> criteria and peers how-to files and found the example at the end of the 
> peers how-to (http://db.apache.org/torque/peers-howto.html). This example 
> is often-cited on this list in answer to questions like mine, but it 
> doesn't work for me, so I'm hoping that someone can spot my error and
> save 
> me my sanity.
> 
> This is for an events calendar in which I want to find all events which 
> "pass through" a date interval defined by date1 and date2. Since the
> advice 
> is that you should use Criterion for "or" instead of Criteria, I prepared
> a 
> bunch of Criterion objects in advance:
> 
>            Criteria crit = new Criteria();
> 
>            Criteria.Criterion a1 = 
> crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date1,Criteria.LESS_EQUAL);
>            Criteria.Criterion a2 = 
> crit.getNewCriterion(VeventsPeer.DTEND,(Object)date1,Criteria.GREATER_EQUAL);
> 
>            Criteria.Criterion b1 = 
> crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date1,Criteria.GREATER_EQUAL);
>            Criteria.Criterion b2 = 
> crit.getNewCriterion(VeventsPeer.DTEND,(Object)date2,Criteria.LESS_EQUAL);
> 
>            Criteria.Criterion c1 = 
> crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date2,Criteria.LESS_EQUAL);
>            Criteria.Criterion c2 = 
> crit.getNewCriterion(VeventsPeer.DTEND,(Object)date2,Criteria.GREATER_EQUAL);
> 
>            Criteria.Criterion d1 = 
> crit.getNewCriterion(VeventsPeer.DTSTART,(Object)date1,Criteria.LESS_EQUAL);
>            Criteria.Criterion d2 = 
> crit.getNewCriterion(VeventsPeer.DTEND,(Object)date2,Criteria.GREATER_EQUAL);
> 
>            crit.add(a1.and(a2)
>              .or(b1.and(b2))
>              .or(c1.and(c2))
>              .or(d1.and(d2)));
> 
> Graphically, what I wanted was the following events from my events table, 
> where a1, b1, c1, and d1 are start dates and a2, b2, c2, d2 are end
> dates:
> 
> 
>        O----------------------------------------------------------------O 
> event
>         d1                                                              
>         d2
> 
>          O---------------O   O----------O      O-------------------O 
>    event(s)
>          a1             a2   b1         b2     c1                 c2
> 
> --------------|---------------------------------------------|-------------- 
> time line
>              date1                                         date2
> 
> 
> I expected the SQL to come out like this:
> 
> WHERE ((a1 AND a2) or (b1 and b2) or (c1 and c2) or (d1 and d2)). Simple, 
> right? But when I got bizarre results I checked the logs and found that 
> what was being passed to the database was this:
> 
> WHERE
> (VEVENTS.DTSTART<='2004-02-01 00:00:00'
>          OR (VEVENTS.DTSTART>='2004-02-01 00:00:00'
>          OR (VEVENTS.DTSTART<='2004-03-01 00:00:00'
>          OR (VEVENTS.DTSTART<='2004-02-01 00:00:00'
>          AND (VEVENTS.DTEND>='2004-03-01 00:00:00'))
>          AND (VEVENTS.DTEND>='2004-03-01 00:00:00'))
>          AND (VEVENTS.DTEND<='2004-03-01 00:00:00'))
>          AND (VEVENTS.DTEND>='2004-02-01 00:00:00'))
> 
> which looks like this, symbolically:
> 
> WHERE (a1 OR (b1 OR (c1 OR (d1 AND d2) AND c2) AND b2) AND a2),
> 
> which is NOT what I wanted! I tried this with formatted String objects 
> instead of Date objects but got the same result. What am I doing wrong?
> 
> Thanks, in advance!
> 
> Bruce Altner
> 
> 


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


Mime
View raw message