Assuming that the User - Section is many-to-many with a join table
and User - Absence is one-to-many, you need SQL like that:
select distinct t0.*
from user t0, user_section t1, absence t2, section t3
/* joins */
where t0.id = t1.user_id
and t1.section_id = t3.id
and t0.id = t2.user_id
/* condition for a specific section */
and t3.id = ?
/* condition for time overlap */
and (t3.start_time between t2.start_time and t2.end_time
or t2.start_time between t3.start_time and t3.end_time)
As you see, there is only inner joins and simple conditions involved,
so you should be able to write a Cayenne Expression that resolves to
this SQL:
sections = ?
and (absences.startTime between sections.startTime and
sections.endTime
or sections.startTime between absences.startTime and
absences.endTime)
(I haven't tested it, so there is a chance I got everything wrong :-))
Andrus
On Oct 21, 2005, at 10:19 PM, Todd O'Bryan wrote:
> I have a need to run a query that is more complicated than anything
> I've tried in SQL, and I have no idea how to do it in Cayenne, so I
> was hoping someone could help me out.
>
> We're trying to display a list of students who are absent from (a
> portion of) a teacher's class (which is called Section to avoid
> confusion with java.lang.Class).
>
> User is the class for students, it is linked to some Sections and
> Absences
> Section is the class for a teacher's class, it has a start and end
> time and a getStudents() method
> Absence is the class for an absence, it has a start and end time
> (say if a student leaves for a doctor's appt and comes back to
> school) and a User who's absent.
>
> I need a list of all Users in a given Section who have an
> associated Absence which overlaps with the time of that Section.
>
> If I need to be more specific, let me know. If I need to beg more,
> also let me know. :-)
>
> Thanks,
> Todd
This archive was generated by hypermail 2.0.0 : Sat Oct 22 2005 - 19:36:37 EDT