Re: Query and Expression help

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Sat Oct 22 2005 - 19:36:36 EDT

  • Next message: Christian Mittendorf: "Re: Searching for Examples of Cayenne + HiveMind"

    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