Custom Queries
How to use a Custom Query.
See also “running a custom query from the admin menu“.
You can use a custom query as a routine reporting tool, when the standard data downloads are not sufficient for your needs. We write a custom query for you to run. A custom query is a single SQL query that can run once over a start/end date range or can be run once for each day in the range.
Each custom query has a name (the filename of the query) which becomes part of the URL (browser link) that you use to access the data. The name of the query is also the Role that provides secure access to the query. Access the custom query using an API key that has this role. We will create the API key for you when we create the query.
An example of a URL to run a custom query: https://{your domain}/api/index.php?r=information/uniquecsv&queryName={filename}&apikey={apikey}&lodge={lodge}&start=2020-03-01&end=2020-12-31&daily=1 The parts in {braces} are parameters you replace with correct values – do not include the braces in the URL.
API keys allow access without a login, so they are suitable for automated connections. You can also access a custom query while logged in as an administrator without using an API key if your admin login has the required Role. You should generate a new API key periodically to keep it secure. Be careful about using shared computers where the browser may store a history of URLs.
Parameters:
- queryName – the filename created for you by CBDWeb (required parameter)
- apikey (required if not logged in with required Role)
- lodge – optional, if used must match lodgecode in the Lodge database table. If provided, {lodge} is replaced within the query with the database name of that lodge. This parameter is only needed by multi-lodge clubs.
- start,end – date inYYYY-MM-DD format. If absent, start is set to today, if end is absent, duration is 7 days.
- daily – If set to 1 the query will be run once for each day in the date range. Otherwise the query runs once and includes the entire date range.