Difference between revisions of "Querying your own database"
| Line 6: | Line 6: | ||
This page provides some sample queries to help understand the underlying structure of the database. | This page provides some sample queries to help understand the underlying structure of the database. | ||
| + | |||
'''A list of people''' | '''A list of people''' | ||
| Line 20: | Line 21: | ||
</code> | </code> | ||
| − | Which | + | Which returns 'org_volunteer'. Then use that to select from the organisations table: |
<code> | <code> | ||
| Line 28: | Line 29: | ||
WHERE org_volunteer = 'Y' AND org_active = 'Y' | WHERE org_volunteer = 'Y' AND org_active = 'Y' | ||
| + | </code> | ||
| + | |||
| + | |||
| + | '''Ouststanding Roster Job Cards''' | ||
| + | Note the LEFT JOIN for workers, because it is valid for a client to have a job card which does not have an assigned worker. | ||
| + | <code> | ||
| + | SELECT * | ||
| + | FROM carer_jcards | ||
| + | JOIN carer_time_worked ON cjcard_ctwork_id = ctwork_id | ||
| + | JOIN organisations AS client ON ctwork_client_id = client.org_id | ||
| + | LEFT JOIN organisations AS worker ON cjcard_actual_carer_id = worker.org_id | ||
| + | WHERE cjcard_date_work_due > NOW() | ||
</code> | </code> | ||
Revision as of 09:43, 25 June 2020
Your entire database can be downloaded in a single file. Please see How to backup your data.
If you are familiar with administering databases, you may wish to rebuild the database into a local MySQL or MariaDb server. This may be for:
- Advanced reporting using SQL queries
- As an emergency backup, e.g. during outages at the data centre or network infrastructure.
This page provides some sample queries to help understand the underlying structure of the database.
A list of people
The types of Organisations and People are stored in the organisations_types table. The orgs/people are stored in the organisations table. A list of active volunteers can be found using:
SELECT orgtype_org_field_name
FROM organisations_types
WHERE orgtype_name_plural = 'Volunteers'
Which returns 'org_volunteer'. Then use that to select from the organisations table:
SELECT *
FROM organisations
WHERE org_volunteer = 'Y' AND org_active = 'Y'
Ouststanding Roster Job Cards
Note the LEFT JOIN for workers, because it is valid for a client to have a job card which does not have an assigned worker.
SELECT *
FROM carer_jcards
JOIN carer_time_worked ON cjcard_ctwork_id = ctwork_id
JOIN organisations AS client ON ctwork_client_id = client.org_id
LEFT JOIN organisations AS worker ON cjcard_actual_carer_id = worker.org_id
WHERE cjcard_date_work_due > NOW()
