Hypatia House Style for SQL in Reports
The simple answer is that there is no mandatory style for queries or reports, however recurring patterns can be useful to prevent common mistakes. This page is intended to list some of these.
Generic PostgreSQL Style
Joins
Joins can be represented in a number of ways but it quickly becomes difficult to untangle reports using a variety of styles. For this reason it is helpful to stick to [[http://www.postgresql.org/docs/9.1/interactive/queries-table-expressions.html#QUERIES-JOIN][ANSI 92"]-style joins]]:
SELECT [...]
FROM basetable a
LEFT JOIN another b ON (a.key = b.key)
LEFT JOIN third c USING (keycol)
[...]
the use of
USING
is reasonable where it improves clarity, but
NATURAL
is to be discouraged as it risks breaking invisibly on schema changes.
For reports joining more than two tables where there is no direct connection between the base table and another, it is acceptable to represent this as a sub-join:
FROM basetable a
INNER JOIN (
another b
LEFT JOIN inner c ON (b.bckey = c.bckey)
) ON (a.key = b.key)
Schema-specific Style
Calculating Student End Dates
COALESCE(occupy_3g.ends, occupy_3g._exits, student_3g._end)
not sure about this one -- please check!
Full Names
COALESCE(_in_inf_firstname,_in_firstname)||' '||_in_lastname
[...] FROM person_3g
'hidden' and other booleans
Most of our boolean fields allow null values and in most cases they are expected; reports should establish the expected default and enforce it with something like
WHERE [...] NOT COALESCE(hidden,'f')
Alternatively and preferably use the following:
WHERE [...] hidden IS DISTINCT FROM TRUE
Both the above conditions evaluate to TRUE when
hidden
is FALSE or NULL, which is normally the behaviour you want.
Deleted rows in Sync Tables
Sync tables (whose content is controlled by the TheonCoupler) can usually be identified by specialised columns used by the sync process. In many circumstances, deleted rows are retained but marked by the presence of a value in the
_th_deletedat
column. Unless it is clear that testing a derived column will exclude deleted rows, it is suggested that reports use some variation of the
WHERE
clause
WHERE [...] _th_deletedat IS NULL
Selecting Student Cohorts
Below are the '''only''' approved ways. Embed suitable variant of the query fragments below (which work in their own right) into your query as necessary. Note that this query expands all '''valid''' values for "cohort" and "status" for demonstration purposes.
SELECT
c.tag, s.status, COUNT(*)
FROM student_3g s
INNER JOIN ( programme_3g p
INNER JOIN cohort_3g c ON (
p.cohort = c.cohort
AND c.tag IN ( 'UG', 'PGT', 'PGR', 'VUG' )
)
) ON (
s.programme = p.programme
)
WHERE
s.status IN ( 'Applying', 'Upcoming', 'Existing', 'Previous' )
AND s._in_old IS NULL
GROUP BY 1, 2
ORDER BY 1, 2;
Note that in the above apart from '''Existing''' and '''Upcoming''' the counts reflect counts of programme registrations not students, hence will be greater (i.e. a student can have any number of programme registrations that are being applied for, upcoming or previous, but can only be on one current programme). However, the counts are still not necessarily correct for Upcoming or Existing as technically a student does not have to make a final programme choice until some weeks into the start of semester (this is very rare though, we have had two instances in total so far in Hypatia).
Alternatively (and if you need per-session data) use a session relative query based against the register.
SELECT
sr.status, COUNT(*)
FROM session_register_3g sr
WHERE
sr.session = er3g_current_session()
AND sr.status ~ 'Existing/PGR'
GROUP BY
sr.status
ORDER BY
sr.status;
The counts above reflect students per-session, so unlike in the earlier query a count across a session reflects distinct students (but the same student may be counted in more than one session). This is because the status value aggregates all possible cohort/status combinations the student has within a session. You cannot collate "previous" students via the session register. The example above shows only one cohort (current PGR students), refer to the status values themselves for matching different combinations.
Need more here ... for example, approved way to factor out "external" students (not taking a programme hosted by our school).
--
TimColles - 27 Nov 2018