db-torque-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Bruce Altner <balt...@hq.nasa.gov>
Subject Re: Criterion query gives bizarre results
Date Mon, 16 Feb 2004 23:11:37 GMT
Dale:

Thanks for the reply.

I am using the version of Torque that was bundled with Turbine 2.1. I know 
that we're way behind the times but the pace of new development has not 
allowed me the "luxury" of upgrading, especially since Torque is decouple 
from Turbine in v2.2 and later.

You got what I was trying to achieve so maybe it is a version thing, though.

As for the LESS_EQUAL/GREATER_EQUAL question, I'm pretty sure that my usage 
is correct, given what I want the query to do.

By the way, I finally gave up on Criteria and wrote the query as straight 
SQL, then used the Peer classes executeQuery(sql) method. You have to cast 
it back into an object using the rows2object method of the Record object 
(Village API) but it works fine.

Thanks,
Bruce


At 01:55 PM 2/11/2004 -0800, you wrote:
>
>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


---------------------------------------------------------------------
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