Course Query
============

.. highlight:: sql

To dynamically query information about courses, we have to construct a rather involved view on the fly. This is necessary because the information is spread across multiple different tables and there are dynamically many course tracks in which a course may be involved depending on the event.

We construct this view from three main components:

* The general ``event.courses`` table. Here lies general course information like title, description and orga notes.
* The custom course related datafields. These are extracted from the JSON-column ``fields`` which is part of the ``event.courses`` table, ut we have to do on the fly to determine the appropriate fields for that event.
* A series of track tables, containing information for the course in relation to all course tracks of the event. This component will be discussed in further detail below.

The final view will be constructed as follows. ::

  event.courses as course
  LEFT OUTER JOIN (
      SELECT
          course_field_columns, id
      FROM
          event.courses
      WHERE
          event_id = X
  ) AS course_fields ON courses.id = course_fields.id
  LEFT OUTER JOIN (
      track_tableX
  ) AS trackX ON courses.id = trackX.base_id

``course_field_columns`` will contain a JSON-cast from the ``fields`` column for every relevant course field. ``track_tableX`` is an example for one of the dynamic tables that are all joined as described.

The following columns will be available in this view:

* ``course.id``
* ``course.course_id`` *This is magically replaced by "{nr}. {shortname}" linking to the course.*
* ``course.event_id``
* ``course.nr``
* ``course.title``
* ``course.description``
* ``course.shortname``
* ``course.instructors``
* ``course.min_size``
* ``course.max_size``
* ``course.notes``
* ``course_fields.xfield_{field_name}`` *This is available for every custom data field with course association.*
* ``track{track_id}.is_offered``
* ``track{track_id}.takes_place``
* ``track{track_id}.attendees``
* ``track{track_id}.intructors``
* ``track{track_id}.num_choices{rank}`` *This is available for every available rank in that track.*

*Note that some additional columns are present but omitted here, since they are not really useful like ``track{track_id}.base_id``.*


The Track Tables
----------------

For every course track we gather three kinds of information:

* Course segment data. This comes from the ``event.course_segments`` table and contains information whether the course is offered in the specific track and whether it actually takes place.
* Attendee/Instructor counts. This comes from the ``event.registration_tracks`` table and contains information about the number of attendees and instructors of a course in the specific track.
* Course choices data. This comes from the ``event.course_choices`` table and contains a count of how many (active) registrations have chosen a course in the specific track and at a specific track.

The track table starts out with a base table created by simply selecting all the appropriate course ids, that is all ids of courses belonging to the event. The course id is selected as ``base_id`` so we can later use it to join the track tables to the other components. This is necessary because there will be multiple columns called ``id`` in a single track table and POSTGRES wouldn't know which to use in the ``JOIN`` otherwise.

All the components of a track table start out with the same base table. ::

  (SELECT id FROM event.courses WHERE event_id = X) AS c

This ensures that we get some kind of data in that component for all courses even if a course would not be present in that component otherwise. For example if a course is not present in the course choices part, we want the count of choices be 0 instead of NULL.


The Course Segment Table
^^^^^^^^^^^^^^^^^^^^^^^^

This part conatins two layers. In the inner layer we start with the usual base table and join that with the ``event.course_segments`` table, selecting the ``is_active`` column from there.

Afterwards we select the actual information we need from that joined table, while coalescing the ``is_active`` column so we get a bool instead of ``NULL`` ::

  SELECT
      c.id, COALESCE(is_active, False) AS takes_place,
      is_active IS NOT NULL AS is_offered
  FROM
      (SELECT id FROM event.courses WHERE event_id = X) AS c
      LEFT OUTER JOIN (
           SELECT
               is_active, course_id
           FROM
                event.course_segments
           WHERE track_id = X
      ) AS segment ON c.id = segment.course_id

The Attendees Table
^^^^^^^^^^^^^^^^^^^

This part contains two layers. In the inner layer, we start with the usual base table and join that with the ``event.registration_tracks`` table by joining on `c.id = rt.course_id`.

In the outer layer we count the registration ids while grouping by course id. Doing it this way results in a count of ``0`` instead of ``NULL`` for courses without attendees. ::

  SELECT
      c.id, COUNT(registration_id) AS attendees
  FROM
      (SELECT id FROM event.courses WHERE event_id = X) AS c
      LEFT OUTER JOIN (
          SELECT
              registration_id, course_id
          FROM
              event.registration_tracks
          WHERE track_id = X
      ) AS rt ON c.id = rt.course_id
  GROUP BY
      c.id


The Instructors Table
^^^^^^^^^^^^^^^^^^^^^

This works just like the ``attendees`` part of the track table, but we join on `c.id = rt.course_instructor` instead. ::

  SELECT
      c.id, COUNT(registration_id) AS instructors
  FROM
      (SELECT id FROM event.courses WHERE event_id = X) AS c
      LEFT OUTER JOIN (
          SELECT
              registration_id, course_instructor
          FROM
              event.registration_tracks
          WHERE track_id = X
      ) AS rt ON c.id = rt.course_instructor
  GROUP BY
      c.id

The Course Choices Table
^^^^^^^^^^^^^^^^^^^^^^^^

We have one of these tables for every possible rank in the specific track. So if a track allows up to 5 choices we have 5 of these tables.

This table contains three layers.

In the innermost layer we join ``event.course_choices`` filtered by track and rank with ``event.registration_parts`` (filtered by the part id corresponding with the specific track) via the registration id, so that we can get the registration status corresponsing to a course choice.

The middle layer starts with the usual base table, which we join with the innermost layer filtered by active registration stati.

In the outer layer we then count the registration ids while grouping by course id. See ``attendees`` table for more information why we do that in this way. ::

  SELECT
      c.id, COUNT(status.registration_id) AS num_choicesX
  FROM
      (SELECT id FROM event.courses WHERE event_id = X) AS c
      LEFT OUTER JOIN (
          SELECT
              choices.registration_id, choices.course_id
          FROM
              (
                  SELECT registration_id, course_id
                  FROM event.course_choices
                  WHERE rank = X AND track_id = X
              ) AS choices
              LEFT OUTER JOIN (
                  SELECT
                      registration_id AS reg_id, status
                  FROM
                      event.registration_parts
                  WHERE
                      part_id = X
              ) AS reg_part
              ON choices.registration_id = reg_part.reg_id
          WHERE
              status = ANY(X)
      ) AS status ON c.id = status.course_id
  GROUP BY
      c.id


The Complete View
-----------------

The final view for course queries looks something like this::

    event.courses AS course
    LEFT OUTER JOIN (
        SELECT
            (fields->>'room')::varchar AS "xfield_room", id
        FROM
            event.courses
        WHERE
            event_id = 1
    ) AS course_fields ON course.id = course_fields.id
    LEFT OUTER JOIN (
        (
            SELECT id AS base_id
            FROM event.courses
            WHERE event_id = 1
        ) AS base
        LEFT OUTER JOIN (
            SELECT
                c.id, COALESCE(is_active, False) AS is_active
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        is_active, course_id
                    FROM
                        event.course_segments
                    WHERE track_id = 1
                ) AS segment ON c.id = segment.course_id
        ) AS segment1 ON base_id = segment1.id
        LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(registration_id) AS attendees
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        registration_id, course_id
                    FROM
                        event.registration_tracks
                    WHERE track_id = 1
                ) AS rt ON c.id = rt.course_id
            GROUP BY
                c.id
        ) AS attendees1 ON base_id = attendees1.id
        LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices0
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 0 AND track_id = 1
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 2
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices1_0 ON base_id = choices1_0.id LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices1
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 1 AND track_id = 1
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 2
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices1_1 ON base_id = choices1_1.id LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices2
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 2 AND track_id = 1
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 2
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices1_2 ON base_id = choices1_2.id LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices3
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 3 AND track_id = 1
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 2
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices1_3 ON base_id = choices1_3.id
    ) AS track1 ON course.id = track1.base_id
    LEFT OUTER JOIN (
        (
            SELECT id AS base_id
            FROM event.courses
            WHERE event_id = 1
        ) AS base
        LEFT OUTER JOIN (
            SELECT
                c.id, COALESCE(is_active, False) AS is_active
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        is_active, course_id
                    FROM
                        event.course_segments
                    WHERE track_id = 2
                ) AS segment ON c.id = segment.course_id
        ) AS segment2 ON base_id = segment2.id
        LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(registration_id) AS attendees
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        registration_id, course_id
                    FROM
                        event.registration_tracks
                    WHERE track_id = 2
                ) AS rt ON c.id = rt.course_id
            GROUP BY
                c.id
        ) AS attendees2 ON base_id = attendees2.id
        LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices0
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 0 AND track_id = 2
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 2
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices2_0 ON base_id = choices2_0.id
    ) AS track2 ON course.id = track2.base_id
    LEFT OUTER JOIN (
        (
            SELECT id AS base_id
            FROM event.courses
            WHERE event_id = 1
        ) AS base
        LEFT OUTER JOIN (
            SELECT
                c.id, COALESCE(is_active, False) AS is_active
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        is_active, course_id
                    FROM
                        event.course_segments
                    WHERE track_id = 3
                ) AS segment ON c.id = segment.course_id
        ) AS segment3 ON base_id = segment3.id
        LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(registration_id) AS attendees
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        registration_id, course_id
                    FROM
                        event.registration_tracks
                    WHERE track_id = 3
                ) AS rt ON c.id = rt.course_id
            GROUP BY
                c.id
        ) AS attendees3 ON base_id = attendees3.id
        LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices0
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 0 AND track_id = 3
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 3
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices3_0 ON base_id = choices3_0.id LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices1
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 1 AND track_id = 3
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 3
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices3_1 ON base_id = choices3_1.id LEFT OUTER JOIN (
            SELECT
                c.id, COUNT(status.registration_id) AS num_choices2
            FROM
                (SELECT id FROM event.courses WHERE event_id = 1) AS c
                LEFT OUTER JOIN (
                    SELECT
                        choices.registration_id, choices.course_id
                    FROM
                        (
                            SELECT registration_id, course_id
                            FROM event.course_choices
                            WHERE rank = 2 AND track_id = 3
                        ) AS choices
                        LEFT OUTER JOIN (
                            SELECT
                                registration_id AS reg_id, status
                            FROM
                                event.registration_parts
                            WHERE
                                part_id = 3
                        ) AS reg_part
                        ON choices.registration_id = reg_part.reg_id
                    WHERE
                        status = ANY(ARRAY[1,2,3,4])
                ) AS status ON c.id = status.course_id
            GROUP BY
                c.id
        ) AS choices3_2 ON base_id = choices3_2.id
    ) AS track3 ON course.id = track3.base_id