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" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
