Lodgement Query

To dynamically query information about lodgements, 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 event parts in which a lodgement may be involved depending on the event.

We construct this view from the following main components:

• The general event.lodgements table. Here lies general lodgement information like title, capacity and orga notes.

• The general event.lodgement_groups table. Every lodgement may belong to one lodgement group.

• The custom lodgement related datafields. These are extracted from the JSON-column fields which is part of the event.lodgements table, but we have to do this on the fly to determine the appropriate fields for that event.

• A series of part tables, containing information for the lodgement in relation to all event parts of the event. This component will be discussed in further detail below.

The final view will be constructed as follows (slightly simplified).

(
    SELECT
        id, id as lodgement_id, event_id,
        title, regular_capacity, camping_mat_capacity, notes, group_id
    FROM
        event.lodgements
) AS lodgement
LEFT OUTER JOIN (
    SELECT
        id, title, regular_capacity, camping_mat_capacity
    FROM
        event.lodgement_groups
) AS lodgement_group ON lodgement.group_id = lodgement_group.id
LEFT OUTER JOIN (
    SELECT
        *course_field_columns*, id
    FROM
        event.courses
    WHERE
        event_id = X
) AS lodgement_field ON lodgement.id = lodgement_fields.id
LEFT OUTER JOIN (
    part_tableX
) AS partX ON lodgement.id = partX.base_id

lodgement_field_columns will contain a JSON-cast from the fields column for every relevant lodgement field. part_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:

• lodgement.id

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

• lodgement.event_id

• lodgement.title

• lodgement.regular_capacity

• lodgement.notes

• lodgement.group_id

• lodgement.camping_mat_capacity

• lodgement_group.title

• lodgement_group.regular_capacity

• lodgement_group.camping_mat_capacity

• lodgement_fields.xfield_{field_name} This is available for every custom data field with course association.

• part{part_id}.regular_inhabitants

• part{part_id}.camping_mat_inhabitants

• part[part_id}.total_inhabitants

• part{part_id}.group_regular_inhabitants

• part{part_id}.group_camping_mat_inhabitants

• part[part_id}.group_total_inhabitants

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

Implementation Details

In this section we discuss some more involved details of the implementation. These need not be considered when using the query view, rather you need to keep this in mind when chaning how the view is constructed.

Temporarily casting the group_id

A lodgement can either be linked to a lodgement group via the event.lodgements.group_id foreign key to the event.lodgement_groups.id column, but it can also not belong to any lodgement group. For practical reasons we want to group all lodgements that don’t belong to any group together, but POSTGRES won’t allow joining tables using NULL = NULL. Due to this we need to temporarily cast the nullable event.lodgements.group_id column to a non-nullable tmp_id column by replacing NULL-entries with -1, like this:

SELECT
    -- replace NULL ids with temp value so we can join.
    id, COALESCE(group_id, -1) AS tmp_group_id
FROM
    event.lodgements

We also need to create an artificial row for the event.ldogement_groups table to join to, like this:

(
    SELECT
        id AS tmp_id, title
    FROM
        event.lodgement_groups
)
UNION
(
    SELECT
        -1, ''
)

Casting nested sums to bigint

Summing rows of integer datatypes with POSTGRES converts the result to the next bigger numerical datatype. Summing integer results in bigint. Summing bigint however results in numeric, which is also used to store fixed-point numbers and is thus converted to decimal.Decimal by psycopg2. To avoid this we cast the sums for group inhabitants to bigint, since we do not expect to need lodgement groups with more than 10^19 inhabitants.

Note that these sums are also normalized to zero in case a NULL-value occurs, because there are no inhabitants/lodgements for a lodgement (group).

The Part Tables

For every event part we gather the following data points:

• Regular inhabitants

• Reserve inhabitants

• Total inhabitants

We gather these both for each lodgement individually and summed for each lodgement group.

The part table starts out with a base table created by selecting all the appropriate lodgement ids, aswell as the corresponding lodgement group ids. The lodgement id is selected as base_id so we can later use it to join the part tables to the other components. This is necessary because there will be multiple columns called id in a single part table and POSTGRES wouldn’t know which to use in the JOIN otherwise:

(
    SELECT
        id as base_id, COALESCE(group_id, -1) AS tmp_group_id
    FROM
        event.lodgements
    WHERE
        event_id = X
) AS base

The part table consists of two components, both of which use the inhabitants_view. The first is just the inhabitants_view itself:

SELECT
    id, tmp_group_id,
    COALESCE(rp_regular.inhabitants, 0) AS regular_inhabitants,
    COALESCE(rp_camping_mat.inhabitants, 0) AS camping_mat_inhabitants,
    COALESCE(rp_total.inhabitants, 0) AS total_inhabitants
FROM
    (
        SELECT id, COALESCE(group_id, -1) as tmp_group_id
        FROM event.lodgements
        WHERE event_id = X
    ) AS l
    LEFT OUTER JOIN (
        *regular_inhabitants_counter*
    ) AS rp_regular ON l.id = rp_regular.lodgement_id
    LEFT OUTER JOIN (
        *camping_mat_inahbitants_counter*
    ) AS rp_camping_mat ON l.id = rp_camping_mat.lodgement_id
    LEFT OUTER JOIN (
        *total_inhabitants_counter*
    ) AS rp_total ON l.id = rp_total.lodgement_id

The second component is the group_inhabitants_view:

SELECT
    tmp_group_id,
    COALESCE(SUM(regular_inhabitants)::bigint, 0) AS group_regular_inhabitants,
    COALESCE(SUM(camping_mat_inhabitants)::bigint, 0) AS group_camping_mat_inhabitants,
    COALESCE(SUM(total_inhabitants)::bigint, 0) AS group_total_inhabitants
FROM (
    *inhabitants_view*
) AS inhabitants_viewX
GROUP BY
    tmp_group_id

The inhabitants counter

The inhabitants counter is a simple query where all inhabitants (with a specific camping_mat status are counted:

SELECT
    lodgement_id, COUNT(registration_id) AS inhabitants
FROM
    event.registration_parts
WHERE
    part_id = X
    *camping_mat_condition*
GROUP BY
    lodgement_id

Where camping_mat condition is either “is_camping_mat = True”, “is_camping_mat = False” or nothing, for regular, camping_mat, total inhabitants respectively.

The Complete View

(
    SELECT
        id, id as lodgement_id, event_id,
        title, regular_capacity, camping_mat_capacity, notes, group_id
    FROM
        event.lodgements
) AS lodgement
LEFT OUTER JOIN (
    SELECT
        -- replace NULL ids with temp value so we can join.
        id, COALESCE(group_id, -1) AS tmp_group_id
    FROM
        event.lodgements
    WHERE
        event_id = 1
) AS tmp_group ON lodgement.id = tmp_group.id
LEFT OUTER JOIN (
    SELECT
        (fields->>'contamination')::varchar AS "xfield_contamination",
        id
    FROM
        event.lodgements
    WHERE
        event_id = 1
) AS lodgement_fields ON lodgement.id = lodgement_fields.id
LEFT OUTER JOIN (
    SELECT
        tmp_id, title, regular_capacity, camping_mat_capacity
    FROM (
        (
            (
                SELECT
                    id AS tmp_id, title
                FROM
                    event.lodgement_groups
                WHERE
                    event_id = 1
            )
            UNION
            (
                SELECT
                    -1, ''
            )
        ) AS group_base
        LEFT OUTER JOIN (
            SELECT
                COALESCE(group_id, -1) as tmp_group_id,
                SUM(regular_capacity) as regular_capacity,
                SUM(camping_mat_capacity) as camping_mat_capacity
            FROM
                event.lodgements
            WHERE
                event_id = 1
            GROUP BY
                tmp_group_id
        ) AS group_totals ON group_base.tmp_id = group_totals.tmp_group_id
    )
) AS lodgement_group ON tmp_group.tmp_group_id = lodgement_group.tmp_id
LEFT OUTER JOIN (
    (
        SELECT
            id as base_id, COALESCE(group_id, -1) AS tmp_group_id
        FROM
            event.lodgements
        WHERE
            event_id = 1
    ) AS base
    LEFT OUTER JOIN (
        SELECT
            id, tmp_group_id,
            COALESCE(rp_regular.inhabitants, 0) AS regular_inhabitants,
            COALESCE(rp_camping_mat.inhabitants, 0) AS camping_mat_inhabitants,
            COALESCE(rp_total.inhabitants, 0) AS total_inhabitants
        FROM
            (
                SELECT id, COALESCE(group_id, -1) as tmp_group_id
                FROM event.lodgements
                WHERE event_id = 1
            ) AS l
            LEFT OUTER JOIN (
                SELECT
                    lodgement_id, COUNT(registration_id) AS inhabitants
                FROM
                    event.registration_parts
                WHERE
                    part_id = 1
                    AND is_camping_mat = False
                GROUP BY
                    lodgement_id
            ) AS rp_regular ON l.id = rp_regular.lodgement_id
            LEFT OUTER JOIN (
                SELECT
                    lodgement_id, COUNT(registration_id) AS inhabitants
                FROM
                    event.registration_parts
                WHERE
                    part_id = 1
                    AND is_camping_mat = True
                GROUP BY
                    lodgement_id
            ) AS rp_camping_mat ON l.id = rp_camping_mat.lodgement_id
            LEFT OUTER JOIN (
                SELECT
                    lodgement_id, COUNT(registration_id) AS inhabitants
                FROM
                    event.registration_parts
                WHERE
                    part_id = 1
                GROUP BY
                    lodgement_id
            ) AS rp_total ON l.id = rp_total.lodgement_id
    ) AS inhabitants_view1 ON base.base_id = inhabitants_view1.id
    LEFT OUTER JOIN (
        SELECT
            tmp_group_id,
            COALESCE(SUM(regular_inhabitants)::bigint, 0) AS group_regular_inhabitants,
            COALESCE(SUM(camping_mat_inhabitants)::bigint, 0) AS group_camping_mat_inhabitants,
            COALESCE(SUM(total_inhabitants)::bigint, 0) AS group_total_inhabitants
        FROM (
            SELECT
                id, tmp_group_id,
                COALESCE(rp_regular.inhabitants, 0) AS regular_inhabitants,
                COALESCE(rp_camping_mat.inhabitants, 0) AS camping_mat_inhabitants,
                COALESCE(rp_total.inhabitants, 0) AS total_inhabitants
            FROM
                (
                    SELECT id, COALESCE(group_id, -1) as tmp_group_id
                    FROM event.lodgements
                    WHERE event_id = 1
                ) AS l
                LEFT OUTER JOIN (
                    SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 1
                        AND is_camping_mat = False
                    GROUP BY
                        lodgement_id
                    ) AS rp_regular ON l.id = rp_regular.lodgement_id
                        LEFT OUTER JOIN (
                            SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 1
                        AND is_camping_mat = True
                    GROUP BY
                        lodgement_id
                    ) AS rp_camping_mat ON l.id = rp_camping_mat.lodgement_id
                    LEFT OUTER JOIN (
                        SELECT
                            lodgement_id, COUNT(registration_id) AS inhabitants
                        FROM
                            event.registration_parts
                        WHERE
                            part_id = 1

                        GROUP BY
                            lodgement_id
                    ) AS rp_total ON l.id = rp_total.lodgement_id
            ) AS inhabitants_view1
        GROUP BY
            tmp_group_id
    ) AS group_inhabitants_view1 ON base.tmp_group_id = group_inhabitants_view1.tmp_group_id
) AS part1 ON lodgement.id = part1.base_id
LEFT OUTER JOIN (
    (
        SELECT
            id as base_id, COALESCE(group_id, -1) AS tmp_group_id
        FROM
            event.lodgements
        WHERE
            event_id = 1
    ) AS base
    LEFT OUTER JOIN (
        SELECT
            id, tmp_group_id,
            COALESCE(rp_regular.inhabitants, 0) AS regular_inhabitants,
            COALESCE(rp_camping_mat.inhabitants, 0) AS camping_mat_inhabitants,
            COALESCE(rp_total.inhabitants, 0) AS total_inhabitants
        FROM
            (
                SELECT id, COALESCE(group_id, -1) as tmp_group_id
                FROM event.lodgements
                WHERE event_id = 1
            ) AS l
        LEFT OUTER JOIN (
            SELECT
                lodgement_id, COUNT(registration_id) AS inhabitants
            FROM
                event.registration_parts
            WHERE
                part_id = 2
                AND is_camping_mat = False
            GROUP BY
                lodgement_id
            ) AS rp_regular ON l.id = rp_regular.lodgement_id
        LEFT OUTER JOIN (
            SELECT
                lodgement_id, COUNT(registration_id) AS inhabitants
            FROM
                event.registration_parts
            WHERE
                part_id = 2
                AND is_camping_mat = True
            GROUP BY
                lodgement_id
        ) AS rp_camping_mat ON l.id = rp_camping_mat.lodgement_id
        LEFT OUTER JOIN (
            SELECT
                lodgement_id, COUNT(registration_id) AS inhabitants
            FROM
                event.registration_parts
            WHERE
                part_id = 2

            GROUP BY
                lodgement_id
        ) AS rp_total ON l.id = rp_total.lodgement_id
    ) AS inhabitants_view2 ON base.base_id = inhabitants_view2.id
    LEFT OUTER JOIN (
        SELECT
            tmp_group_id,
            COALESCE(SUM(regular_inhabitants)::bigint, 0) AS group_regular_inhabitants,
            COALESCE(SUM(camping_mat_inhabitants)::bigint, 0) AS group_camping_mat_inhabitants,
            COALESCE(SUM(total_inhabitants)::bigint, 0) AS group_total_inhabitants
        FROM (
            SELECT
                id, tmp_group_id,
                COALESCE(rp_regular.inhabitants, 0) AS regular_inhabitants,
                COALESCE(rp_camping_mat.inhabitants, 0) AS camping_mat_inhabitants,
                COALESCE(rp_total.inhabitants, 0) AS total_inhabitants
            FROM
                (
                    SELECT id, COALESCE(group_id, -1) as tmp_group_id
                    FROM event.lodgements
                    WHERE event_id = 1
                ) AS l
                LEFT OUTER JOIN (
                    SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 2
                        AND is_camping_mat = False
                    GROUP BY
                        lodgement_id
                ) AS rp_regular ON l.id = rp_regular.lodgement_id
                LEFT OUTER JOIN (
                    SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 2
                        AND is_camping_mat = True
                    GROUP BY
                        lodgement_id
                ) AS rp_camping_mat ON l.id = rp_camping_mat.lodgement_id
                LEFT OUTER JOIN (
                    SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 2

                    GROUP BY
                        lodgement_id
                ) AS rp_total ON l.id = rp_total.lodgement_id
        ) AS inhabitants_view2
    GROUP BY
        tmp_group_id
    ) AS group_inhabitants_view2 ON base.tmp_group_id = group_inhabitants_view2.tmp_group_id
) AS part2 ON lodgement.id = part2.base_id
LEFT OUTER JOIN (
    (
        SELECT
            id as base_id, COALESCE(group_id, -1) AS tmp_group_id
        FROM
            event.lodgements
        WHERE
            event_id = 1
    ) AS base
    LEFT OUTER JOIN (
        SELECT
            id, tmp_group_id,
            COALESCE(rp_regular.inhabitants, 0) AS regular_inhabitants,
            COALESCE(rp_camping_mat.inhabitants, 0) AS camping_mat_inhabitants,
            COALESCE(rp_total.inhabitants, 0) AS total_inhabitants
        FROM
            (
                SELECT id, COALESCE(group_id, -1) as tmp_group_id
                FROM event.lodgements
                WHERE event_id = 1
            ) AS l
            LEFT OUTER JOIN (
                SELECT
                    lodgement_id, COUNT(registration_id) AS inhabitants
                FROM
                    event.registration_parts
                WHERE
                    part_id = 3
                    AND is_camping_mat = False
                GROUP BY
                    lodgement_id
            ) AS rp_regular ON l.id = rp_regular.lodgement_id
            LEFT OUTER JOIN (
                SELECT
                    lodgement_id, COUNT(registration_id) AS inhabitants
                FROM
                    event.registration_parts
                WHERE
                    part_id = 3
                    AND is_camping_mat = True
                GROUP BY
                    lodgement_id
            ) AS rp_camping_mat ON l.id = rp_camping_mat.lodgement_id
            LEFT OUTER JOIN (
                SELECT
                lodgement_id, COUNT(registration_id) AS inhabitants
            FROM
                event.registration_parts
            WHERE
                part_id = 3

            GROUP BY
                lodgement_id
                ) AS rp_total ON l.id = rp_total.lodgement_id
    ) AS inhabitants_view3 ON base.base_id = inhabitants_view3.id
    LEFT OUTER JOIN (
        SELECT
            tmp_group_id,
            COALESCE(SUM(regular_inhabitants)::bigint, 0) AS group_regular_inhabitants,
            COALESCE(SUM(camping_mat_inhabitants)::bigint, 0) AS group_camping_mat_inhabitants,
            COALESCE(SUM(total_inhabitants)::bigint, 0) AS group_total_inhabitants
        FROM (
            SELECT
                id, tmp_group_id,
                COALESCE(rp_regular.inhabitants, 0) AS regular_inhabitants,
                COALESCE(rp_camping_mat.inhabitants, 0) AS camping_mat_inhabitants,
                COALESCE(rp_total.inhabitants, 0) AS total_inhabitants
            FROM
                (
                    SELECT id, COALESCE(group_id, -1) as tmp_group_id
                    FROM event.lodgements
                    WHERE event_id = 1
                ) AS l
                LEFT OUTER JOIN (
                    SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 3
                        AND is_camping_mat = False
                    GROUP BY
                        lodgement_id
                ) AS rp_regular ON l.id = rp_regular.lodgement_id
                LEFT OUTER JOIN (
                    SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 3
                        AND is_camping_mat = True
                    GROUP BY
                        lodgement_id
                ) AS rp_camping_mat ON l.id = rp_camping_mat.lodgement_id
                LEFT OUTER JOIN (
                    SELECT
                        lodgement_id, COUNT(registration_id) AS inhabitants
                    FROM
                        event.registration_parts
                    WHERE
                        part_id = 3
                    GROUP BY
                        lodgement_id
                ) AS rp_total ON l.id = rp_total.lodgement_id
        ) AS inhabitants_view3
        GROUP BY
            tmp_group_id
    ) AS group_inhabitants_view3 ON base.tmp_group_id = group_inhabitants_view3.tmp_group_id
) AS part3 ON lodgement.id = part3.base_id