Relations and joins
From GTwM
Understanding Relations
Every table in GT portalBase has a primary key field as its first field. This is an integer field that automatically increments for each new record, (starting with 1 for the first record). The value of a primary key field uniquely identifies a record within a table. By default, this value is not displayed in Panes 2 or 3 but can be seen to exist by looking at a table’s ‘fields’ tab in Pane 3.
Using primary keys, we can relate the data in one table to that stored in another by means of a ‘Relation’ field. The ‘Relation’ field creates what is known as a ‘foreign key’ by storing the primary key value of the record from the related table.
As an example, employees can be related to their employers by storing the employer primary key value within a Relation field in the employee record. As can be seen in the table below, the employee Sergey Brin is related to the employer Google by storing id 2 in the Employer relation field.
Joins
When you create a report in GT portalBase you immediately have access to all the fields of the parent table. However, when you need to bring together data from various tables or reports, (for example, you may want to view a report that lists details of employers and their employees,) you first need to 'join' to the related tables/reports.
To add a join to a report select the 'Joins' tab of Pane 3 whilst viewing a report.
GT portalBase will automatically sense what joins may be useful and present them as links. For example, if you are editing a report of employees, portalBase may offer to join to the employers table. Clicking a link will immediately create the relevant join, so you don't need to get involved in any complex details. It chooses the right fields and the most relevant type of join. This is a really quick way to build up reports when prototyping an application and is useful even for advanced users.
In some cases, though, administrators with a knowledge of SQL principles may want to customise their joins, for example joining on a calculation or creating a full outer join. If you need this capability, click 'add complex join' and read on...
You can only join from tables/reports already included within the report, (this includes the parent table and any previously joined tables or reports). As can be seen in the image above, the relation field will be listed by table name and display name, (in our example this is 'Organisations: organisation name').
The 'Join Type' column allows you to specify how records should be returned when there is unrelated data in either table. The options are:
#inner: Only returns records where the join fields have matching values in both tables/reports.
#left outer: Returns all records from the left table. Records from the right table will only be returned if they match with left table records on the join fields. Where a record from each table matches on the join fields, they will be returned as one row.
#right outer: Returns all records from the right table. Records from the left table will only be returned if they match with right table records on the join fields. Where a record from each table matches on the join fields, they will be returned as one row. This is obviously the reverse of a left outer join.
#full outer: Returns all records from both tables. Where a record from each table matches on the join fields, they will be returned as one row.
#none: This join type is equivalent to a 'cross outer' or 'cartesian product' in SQL. It is sometimes but rarely useful.
