ILIOS: get offerings/independent learning sessions for calendar feed query.

fml.sql

SELECT DISTINCT
    session.title as session_title, session.attire_required,
    session.equipment_required, session.supplemental, session.session_id,
    session.published_as_tbd,
    session_description.description,
    session_type.title as session_type, session_type.session_type_id,
    session_type.session_type_css_class,
    offering.room, offering.start_date, offering.end_date, offering.offering_id,
    course.title as course_title, course.course_id, course.year,
    course.course_level, course.published_as_tbd AS course_published_as_tbd
FROM offering
    JOIN session ON offering.session_id = session.session_id
    JOIN session_type ON session.session_type_id = session_type.session_type_id
    LEFT JOIN session_description ON session.session_id = session_description.session_id
    JOIN course ON session.course_id = course.course_id
    LEFT JOIN offering_x_learner
        ON offering_x_learner.offering_id = offering.offering_id AND offering_x_learner.user_id = 4670 
    LEFT JOIN offering_x_group
        ON offering_x_group.offering_id = offering.offering_id AND offering_x_group.group_id IN (
            SELECT group_id from group_x_user WHERE user_id = 4670
	) 
    LEFT JOIN offering_x_instructor
        ON offering_x_instructor.offering_id = offering.offering_id AND offering_x_instructor.user_id = 4670 
    LEFT JOIN offering_x_instructor_group
        ON offering_x_instructor_group.offering_id = offering.offering_id AND offering_x_instructor_group.instructor_group_id IN (
            SELECT instructor_group_id FROM instructor_group_x_user WHERE user_id= 4670
        ) 
    LEFT JOIN course_director ON course_director.course_id = course.course_id AND course_director.user_id = 4670
WHERE
    offering.deleted = 0 
    AND session.deleted = 0 
    AND course.deleted = 0
    AND course.archived = 0
    AND course.publish_event_id IS NOT NULL
    AND session.publish_event_id IS NOT NULL
    AND offering.start_date > FROM_UNIXTIME(1386877039)
    AND offering.end_date < FROM_UNIXTIME(1392665839)
    AND (
	offering_x_learner.offering_id IS NOT NULL 
	OR offering_x_instructor.offering_id IS NOT NULL 
	OR course_director.course_id IS NOT NULL
    )
ORDER BY offering.start_date ASC, offering.offering_id ASC

fml2-fixed.sql

(    
    SELECT
    s.session_id, i.ilm_session_facet_id, i.hours, i.due_date, s.title AS session_title,
    s.session_type_id, c.course_id,
    c.title AS course_title,
    c.year, c.course_level, sd.description AS event_details ,
    s.published_as_tbd, c.published_as_tbd AS course_published_as_tbd
    FROM session s
    JOIN course c ON c.course_id = s.course_id
    JOIN ilm_session_facet i ON i.ilm_session_facet_id = s.ilm_session_facet_id
    JOIN ilm_session_facet_x_learner ON ilm_session_facet_x_learner.ilm_session_facet_id = s.ilm_session_facet_id
    LEFT JOIN session_description sd ON sd.session_id = s.session_id
    WHERE
    s.deleted = 0
    AND c.deleted = 0
    AND c.archived = 0
    AND s.publish_event_id IS NOT NULL
    AND c.publish_event_id IS NOT NULL
    AND i.due_date > FROM_UNIXTIME(1386874663)
    AND i.due_date < FROM_UNIXTIME(1392663463)
    AND ilm_session_facet_x_learner.user_id = 4670
)
UNION DISTINCT 
(
    SELECT
    s.session_id, i.ilm_session_facet_id, i.hours, i.due_date, s.title AS session_title,
    s.session_type_id, c.course_id,
    c.title AS course_title,
    c.year, c.course_level, sd.description AS event_details ,
    s.published_as_tbd, c.published_as_tbd AS course_published_as_tbd
    FROM session s
    JOIN course c ON c.course_id = s.course_id
    JOIN ilm_session_facet i ON i.ilm_session_facet_id = s.ilm_session_facet_id
	 JOIN ilm_session_facet_x_group ON ilm_session_facet_x_group.ilm_session_facet_id = s.ilm_session_facet_id
    JOIN group_x_user ON group_x_user.group_id = ilm_session_facet_x_group.group_id
    LEFT JOIN session_description sd ON sd.session_id = s.session_id
    WHERE
    s.deleted = 0
    AND c.deleted = 0
    AND c.archived = 0
    AND s.publish_event_id IS NOT NULL
    AND c.publish_event_id IS NOT NULL
    AND i.due_date > FROM_UNIXTIME(1386874663)
    AND i.due_date < FROM_UNIXTIME(1392663463)
    AND group_x_user.user_id = 4670
)
UNION DISTINCT
(
    SELECT
    s.session_id, i.ilm_session_facet_id, i.hours, i.due_date, s.title AS session_title,
    s.session_type_id, c.course_id,
    c.title AS course_title,
    c.year, c.course_level, sd.description AS event_details ,
    s.published_as_tbd, c.published_as_tbd AS course_published_as_tbd
    FROM session s
    JOIN course c ON c.course_id = s.course_id
    JOIN ilm_session_facet i ON i.ilm_session_facet_id = s.ilm_session_facet_id
    JOIN ilm_session_facet_x_instructor ON ilm_session_facet_x_instructor.ilm_session_facet_id = s.ilm_session_facet_id 
    LEFT JOIN session_description sd ON sd.session_id = s.session_id
	 WHERE
    s.deleted = 0
    AND c.deleted = 0
    AND c.archived = 0
    AND s.publish_event_id IS NOT NULL
    AND c.publish_event_id IS NOT NULL
    AND i.due_date > FROM_UNIXTIME(1386874663)
    AND i.due_date < FROM_UNIXTIME(1392663463)
    AND ilm_session_facet_x_instructor.user_id = 4670 
)
UNION DISTINCT
(
    SELECT
    s.session_id, i.ilm_session_facet_id, i.hours, i.due_date, s.title AS session_title,
    s.session_type_id, c.course_id,
    c.title AS course_title,
    c.year, c.course_level, sd.description AS event_details ,
    s.published_as_tbd, c.published_as_tbd AS course_published_as_tbd
    FROM session s
    JOIN course c ON c.course_id = s.course_id
    JOIN ilm_session_facet i ON i.ilm_session_facet_id = s.ilm_session_facet_id
    JOIN ilm_session_facet_x_instructor_group ON ilm_session_facet_x_instructor_group.ilm_session_facet_id = s.ilm_session_facet_id 
	 JOIN instructor_group_x_user ON instructor_group_x_user.instructor_group_id = ilm_session_facet_x_instructor_group.instructor_group_id 
    LEFT JOIN session_description sd ON sd.session_id = s.session_id
	 WHERE
    s.deleted = 0
    AND c.deleted = 0
    AND c.archived = 0
    AND s.publish_event_id IS NOT NULL
    AND c.publish_event_id IS NOT NULL
    AND i.due_date > FROM_UNIXTIME(1386874663)
    AND i.due_date < FROM_UNIXTIME(1392663463)
    AND instructor_group_x_user.user_id = 4670
)
UNION DISTINCT
(
    SELECT
    s.session_id, i.ilm_session_facet_id, i.hours, i.due_date, s.title AS session_title,
    s.session_type_id, c.course_id,
    c.title AS course_title,
    c.year, c.course_level, sd.description AS event_details ,
    s.published_as_tbd, c.published_as_tbd AS course_published_as_tbd
    FROM session s
    JOIN course c ON c.course_id = s.course_id
    JOIN ilm_session_facet i ON i.ilm_session_facet_id = s.ilm_session_facet_id
    JOIN course_director ON course_director.course_id = c.course_id
    LEFT JOIN session_description sd ON sd.session_id = s.session_id
    WHERE
    s.deleted = 0
    AND c.deleted = 0
    AND c.archived = 0
    AND s.publish_event_id IS NOT NULL
    AND c.publish_event_id IS NOT NULL
    AND i.due_date > FROM_UNIXTIME(1386874663)
    AND i.due_date < FROM_UNIXTIME(1392663463)
    AND course_director.user_id = 4670
)
ORDER BY due_date ASC, session_id ASC

fml2.sql

SELECT DISTINCT 
s.session_id, i.ilm_session_facet_id, i.hours, i.due_date, s.title AS session_title, 
s.session_type_id, c.course_id, 
c.title AS course_title, 
c.year, c.course_level, sd.description AS event_details , 
s.published_as_tbd, c.published_as_tbd AS course_published_as_tbd 
FROM session s 
JOIN course c 
ON c.course_id = s.course_id 
JOIN ilm_session_facet i ON i.ilm_session_facet_id = s.ilm_session_facet_id 
LEFT JOIN session_description sd ON sd.session_id = s.session_id 
LEFT JOIN ilm_session_facet_x_learner ON ilm_session_facet_x_learner.ilm_session_facet_id = s.ilm_session_facet_id 
LEFT JOIN ilm_session_facet_x_group ON ilm_session_facet_x_group.ilm_session_facet_id = s.ilm_session_facet_id 
LEFT JOIN ilm_session_facet_x_instructor ON ilm_session_facet_x_instructor.ilm_session_facet_id = s.ilm_session_facet_id 
LEFT JOIN ilm_session_facet_x_instructor_group ON ilm_session_facet_x_instructor_group.ilm_session_facet_id = s.ilm_session_facet_id 
LEFT JOIN course_director ON course_director.course_id = c.course_id 
WHERE 
s.deleted = 0 
AND c.deleted = 0 
AND c.archived = 0 
AND s.publish_event_id IS NOT NULL 
AND c.publish_event_id IS NOT NULL
AND i.due_date > FROM_UNIXTIME(1386874663) 
AND i.due_date < FROM_UNIXTIME(1392663463)
AND ( 
    0 
    OR ( 
        ilm_session_facet_x_learner.user_id = 4670 
        OR EXISTS (
            SELECT group_x_user.user_id
            FROM group_x_user WHERE group_x_user.group_id = ilm_session_facet_x_group.group_id 
            AND group_x_user.user_id = 4670
        )
    ) 
    OR ( 
        ilm_session_facet_x_instructor.user_id = 4670 
        OR EXISTS (
            SELECT instructor_group_x_user.user_id 
            FROM instructor_group_x_user 
            WHERE instructor_group_x_user.instructor_group_id = ilm_session_facet_x_instructor_group.instructor_group_id 
            AND instructor_group_x_user.user_id = 4670
        )
    ) 
    OR course_director.user_id = 4670
) 
ORDER BY i.due_date ASC, s.session_id ASC

Ne oder ?