Registration Query

To dynamically query information about registrations, we have to construct a rather involved view on the fly. This is necessary because the information is spread across multiple different tables and we also want to gather secondary information about a participants courses and lodgements.

We construct this view from six main components:

  • The general event.registrations columns. This contains some general information, like how much of the fee was already paid.

  • The core.personas data. Here we get the personal information of the participant, like name, email and birthday.

  • The custom registration datafields. These are individually configured per event and are added to the fixed information above.

  • A series of part tables, containing information about the registrations status regarding a given part. This includes lodgement information for any assigned lodgement in a part.

  • A series of track tables, containing information about the courses a registration is associated with. This includes both the course they are assigned and any course they are isntructing.

  • The timestamps of registration creation and last modification from event.log.

The final view will be constructed as follows.

event.registrations as reg
LEFT OUTER JOIN (
    SELECT
        registration_field_columns, id,
        amount_owed - amount_paid AS remaining_owed
    FROM
        event.registrations
    WHERE
        event_id = X
) AS reg_fields ON reg.id = reg_fields.id
LEFT OUTER JOIN (
    part_tableX
) AS partX ON reg.id = partX.registration_id
LEFT OUTER JOIN (
    lodgement_tableX
) AS lodgementX ON partX.lodgement_id = lodgementX.id
LEFT OUTER JOIN (
    lodgement_group_tableX
) AS lodgement_groupX ON lodgementX.group_id = lodgement_groupX.id
LEFT OUTER JOIN (
    track_tableX
) AS trackX ON reg.id = trackX.registration_id
LEFT OUTER JOIN (
    course_tableX
) AS courseX ON trackX.course_id = courseX.id
LEFT OUTER JOIN (
    course_tableX
) AS course_instructorX ON trackX.course_id = course_instructorX.id

reg_fields_colum will contain a JSON-cast from the fields column for every relevant course field. part_tableX and lodgement_tableX will be present for every part X of the event. track_tableX, courseX, course_instructorX will be present for every part X of the event.

The following fields are avalable in the dynamic tables:

  • reg_fields.xfield_{field_name} For every custom registration datafield.

  • part{part_id}.status

  • part{part_id}.lodgement_id This is magically replaced by “{title}” linking to the lodgement.

  • part{part_id}.is_camping_mat

  • lodgement{part_id}.xfield_{field_name} For every part and every custom lodgement datafield.

  • lodgement{part_id}.title

  • lodgement{part_id}.notes

  • lodgement{part_id}.group_id

  • lodgement_group{part_id}.title

  • track{track_id}.course_id This is magically replaced by “{nr}. {shortname}” linking to the course.

  • track{track_id}.course_instructor This is magically replaced by “{nr}. {shortname}” linking to the course.

  • track{track_id}.is_course_instructor

  • course{track_id}.xfield_{field_name} For every track and every custom course datafield.

  • course{track_id}.nr

  • course{track_id}.title

  • course{track_id}.shortname

  • course{track_id}.notes

  • course{track_id}.instructors

  • course_instructor{track_id}.xfield_{field_name} For every track and every custom course datafield.

  • course_instructor{track_id}.nr

  • course_instructor{track_id}.title

  • course_instructor{track_id}.shortname

  • course_instructor{track_id}.notes

  • course_instructor{track_id}.instructors

  • ctime.creation_time

  • mtime.modification_time

The Part Tables

For every part we have two tables.

The first table contains information from event.registration_parts, including the registration’s status in that part:

SELECT
    registration_id, status, lodgement_id, is_camping_mat
FROM
    event.registration_parts
WHERE
    part_id = X

The second table provides a view of the assigned lodgement, should one exist. All these columns will be NULL if no lodgement is assigned in this part:

SELECT
    lodge_field_columns,
    title, notes, id
FROM
    event.lodgements
WHERE
    event_id = X

These tables are joined ON partX.lodgement_id = lodgementX.id.

The Track Tables

For every track we have three tables.

The first tables contains information from event.registration_tracks, mainly about the assigned and any instructed course:

SELECT
    registration_id, course_id, course_instructor,
    (NOT(course_id IS NULL AND course_instructor IS NOT NULL)
     AND course_id = course_instructor) AS is_course_instructor
FROM
    event.registration_tracks
WHERE
    track_id = X

After that we have two views on the event.courses table for both the assigned and instrcuted course. All columns will be None, if no course is assigned/instructed:

SELECT
    course_field_columns,
    id, nr, title, shortname, notes, instructors
FROM
    event.courses
WHERE
    event_id = X

The Complete View

The final view for regisration queries looks something like this:

event.registrations AS reg
LEFT OUTER JOIN
    core.personas
AS persona ON reg.persona_id = persona.id
LEFT OUTER JOIN (
    SELECT
        (fields->>'brings_balls')::boolean AS "xfield_brings_balls",
        (fields->>'transportation')::varchar AS "xfield_transportation",
        (fields->>'lodge')::varchar AS "xfield_lodge",
        (fields->>'may_reserve')::boolean AS "xfield_may_reserve",
        id
    FROM
        event.registrations
    WHERE
        event_id = 1
) AS reg_fields ON reg.id = reg_fields.id
LEFT OUTER JOIN (
    SELECT
        registration_id, status, lodgement_id, is_camping_mat
    FROM
        event.registration_parts
    WHERE
        part_id = 1
) AS part1 ON reg.id = part1.registration_id
LEFT OUTER JOIN (
    SELECT
        (fields->>'contamination')::varchar AS "xfield_contamination", title, notes, id
    FROM
        event.lodgements
    WHERE
        event_id = 1
) AS lodgement1 ON part1.lodgement_id = lodgement1.id
LEFT OUTER JOIN (
    SELECT
        title, id
    FROM
        event.lodgement_groups
    WHERE
        event_id = 1
) AS lodgement_group1 ON lodgement1.group_id = lodgement_group1.id
LEFT OUTER JOIN (
    SELECT
        registration_id, status, lodgement_id, is_camping_mat
    FROM
        event.registration_parts
    WHERE
        part_id = 2
) AS part2 ON reg.id = part2.registration_id
LEFT OUTER JOIN (
    SELECT
        (fields->>'contamination')::varchar AS "xfield_contamination",
        title, notes, id
    FROM
        event.lodgements
    WHERE
        event_id = 1
) AS lodgement2 ON part2.lodgement_id = lodgement2.id
LEFT OUTER JOIN (
    SELECT
        title, id
    FROM
        event.lodgement_groups
    WHERE
        event_id = 1
) AS lodgement_group2 ON lodgement2.group_id = lodgement_group2.id
LEFT OUTER JOIN (
    SELECT
        registration_id, status, lodgement_id, is_camping_mat
    FROM
        event.registration_parts
    WHERE
        part_id = 3
) AS part3 ON reg.id = part3.registration_id
LEFT OUTER JOIN (
    SELECT
        (fields->>'contamination')::varchar AS "xfield_contamination",
        title, notes, id
    FROM
        event.lodgements
    WHERE
        event_id = 1
) AS lodgement3 ON part3.lodgement_id = lodgement3.id
LEFT OUTER JOIN (
    SELECT
        title, id
    FROM
        event.lodgement_groups
    WHERE
        event_id = 1
) AS lodgement_group3 ON lodgement3.group_id = lodgement_group3.id
LEFT OUTER JOIN (
    SELECT
        registration_id, course_id, course_instructor,
        (NOT(course_id IS NULL AND course_instructor IS NOT NULL)
         AND course_id = course_instructor) AS is_course_instructor
    FROM
        event.registration_tracks
    WHERE
        track_id = 1
) AS track1 ON reg.id = track1.registration_id
LEFT OUTER JOIN (
    SELECT
        (fields->>'room')::varchar AS "xfield_room",
        id, nr, title, shortname, notes, instructors
    FROM
        event.courses
    WHERE
        event_id = 1
) AS course1 ON track1.course_id = course1.id
LEFT OUTER JOIN (
    SELECT
        (fields->>'room')::varchar AS "xfield_room",
        id, nr, title, shortname, notes, instructors
    FROM
        event.courses
    WHERE
        event_id = 1
) AS course_instructor1 ON track1.course_instructor = course_instructor1.id
LEFT OUTER JOIN (
    SELECT
        registration_id, course_id, course_instructor,
        (NOT(course_id IS NULL AND course_instructor IS NOT NULL)
         AND course_id = course_instructor) AS is_course_instructor
    FROM
        event.registration_tracks
    WHERE
        track_id = 2
) AS track2 ON reg.id = track2.registration_id
LEFT OUTER JOIN (
    SELECT
        (fields->>'room')::varchar AS "xfield_room",
        id, nr, title, shortname, notes, instructors
    FROM
        event.courses
    WHERE
        event_id = 1
) AS course2 ON track2.course_id = course2.id
LEFT OUTER JOIN (
    SELECT
        (fields->>'room')::varchar AS "xfield_room",
        id, nr, title, shortname, notes, instructors
    FROM
        event.courses
    WHERE
        event_id = 1
) AS course_instructor2 ON track2.course_instructor = course_instructor2.id
LEFT OUTER JOIN (
    SELECT
        registration_id, course_id, course_instructor,
        (NOT(course_id IS NULL AND course_instructor IS NOT NULL)
         AND course_id = course_instructor) AS is_course_instructor
    FROM
        event.registration_tracks
    WHERE
        track_id = 3
) AS track3 ON reg.id = track3.registration_id
LEFT OUTER JOIN (
    SELECT
        (fields->>'room')::varchar AS "xfield_room",
        id, nr, title, shortname, notes, instructors
    FROM
        event.courses
    WHERE
        event_id = 1
) AS course3 ON track3.course_id = course3.id
LEFT OUTER JOIN (
    SELECT
        (fields->>'room')::varchar AS "xfield_room",
        id, nr, title, shortname, notes, instructors
    FROM
        event.courses
    WHERE
        event_id = 1
) AS course_instructor3 ON track3.course_instructor = course_instructor3.id
LEFT OUTER JOIN (
    SELECT
        persona_id, MAX(ctime) AS creation_time
    FROM
        event.log
    WHERE
        event_id = 1 AND code = 50
    GROUP BY
        persona_id
) AS ctime ON reg.persona_id = ctime.persona_id
LEFT OUTER JOIN (
    SELECT
        persona_id, MAX(ctime) AS modification_time
    FROM
        event.log
    WHERE
        event_id = 1 AND code = 51
    GROUP BY
        persona_id
) AS mtime ON reg.persona_id = mtime.persona_id