Table Lookup Convention
To represent relationships between pages, MinistryPlatform uses a table lookup convention. The metadata stored in Pages and Page views is used as a guide to the Platform on how to build the queries it runs in SQL Server.
The easiest way to understand this convention is to add records in related tables to a view in the Advanced Search tool and look in the SQL Layout tab to see how the Advanced Search tool applied the table lookup convention to refer to the table that contains the field being used for display or criteria.
The convention can be summarized as <Foreign_Key>_Table.<Field_Name>.
Up to six levels of foreign key lookups can be chained: <Foreign_Key>_Table_<Foreign_Key>_Table.<Field_Name>.
This is contextual. The first foreign key must be on the table related to your current page for a lookup to function.
Example
A common scenario is selecting the Participant Type for display in a Contacts View. In this example, we will explore how that relationship is expressed in the Advanced Search Tool, the Table Lookup Convention, the database itself and SQL (with no use of the convention).
Views (Advanced Search)
This is what the Advanced Search Tool looks like navigating this relationship to select the Participant Type field for a View in Contacts:
Table Lookup Convention
When the Participant Type field is selected in the Advanced Search Tool, here is the resulting SQL (which uses the Table Lookup Convention):
Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type]
Here is a simplified version. You can see how the "_Table" gets added to represent the relationship:
Participant_Record_Table_Participant_Type_ID_Table.Participant_TypeThe Database Structures
The following diagram shows the three related tables: Contacts, Participants, and Participant Types. The Contact table is related to the Participants table because the Contact field "Participant_Record" stores an ID (a key) from the Participants table. The key icon in the Participants table represents the primary key, which is the ID field for the table.
The lines between the tables represent these relationships. In the SQL example below, they are represented by the JOIN clause.
Standard SQL
The same relationship in SQL (without the use of the convention) would look something like this. Here it is written as a subquery which could be used as a field:
(SELECT PT.Participant_Type FROM Participants P JOIN Participant_Types PT ON P.Participant_Type_ID = PT.Participant_Type_ID WHERE P.Participant_ID = Contacts.Participant_Record)You can see that the Table Lookup Convention greatly simplifies the representation of this table-to-table relationship.
Behind the Scenes
When the Table Lookup Convention is used, the Platform converts these references to standard SQL statements and uses the convention names as table aliases:
SELECT Participant_Record_Table_Participant_Type_ID_Table.Participant_Type
FROM Contacts
LEFT JOIN Participants AS Participant_Record_Table
ON Participant_Record_Table.Participant_ID = Contacts.Participant_Record
LEFT JOIN Participant_Types AS Participant_Record_Table_Participant_Type_ID_Table
ON Participant_Record_Table_Participant_Type_ID_Table.Participant_ID = Participant_Record_Table.Participant_Type_ID