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