Table Lookup Convention
Learn how MinistryPlatform simplifies SQL queries with a table lookup convention that maps relationships between database tables.
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.
To better understand this convention, add records in related tables to a view in the Advanced Search Tool. Then, 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 used for display or criteria.
The convention can be summarized as <Foreign_Key>_Table.<Field_Name>.
You can chain up to six levels of foreign key lookups: <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
This example examines selecting the Participant Type for display in a Contacts View. 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) looks 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 simplifies the representation of this table-to-table relationship.
Behind the Scenes
When you use the Table Lookup Convention, 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