Xataface allows you to define relationships between tables using the relationships.ini file.
Xataface applications without relationships between tables can be quite boring. In our FacultyOfWidgetry application, we have implicitly defined a relationship between the Program table and the Course table by adding a ProgramID field to the Course table. In the previous section we saw how to configure this relationship from the context of a 'Course' by adding a select list to the Course table form to select the Program that the Course belongs to.
From the 'Program' side it is a little bit more complicated. There are no fields in the 'Program' table that can be edited to add a 'Course' to the list of courses in a 'Program', and it would be highly inconvenient to have to edit a 'Course' record in order to add the course to a 'Program'. What we want is a sort of 'Add Course' button to add a course to a 'Program'.
Concepts, Definitions, and Terminology
Before we can delve into examples, it will help to go over some of the concepts and terminology involved in relationships using Xataface. Xataface relationships are always defined in a one-way fashion from the point of view of one table. For example, if you define a one-to-many relationship from the 'Program' table to the 'Course' table, the mirror (many-to-one) relationship from 'Course' to 'Program' is not automatically created. This method of defining relationships allows us to unambiguously refer to the source and destination tables of a relationship.
Definition 1: The source table of a relationship is the table on which a relationship is defined. For example if we define a relationship named 'Courses' on the 'Program' table to associate courses in a given program, then the 'Program' table would be considered the source table of the relationship, and the 'Course' table would be a destination table of the relationship.
Definition 2: The destination table of a relationship a table from which related records are selected.
There may be multiple destination tables in a given relationship but only one source table. If there are multiple destination tables, then one of these tables is designated as the domain table and the remaining destination tables are called join tables.
Definition 3: A domain table is a destination table which stores the object of the relationship. For example if we define a many-to-many relationship between the 'Program' table and the 'Course' table, (i.e., each program can contain multiple courses and each course can be part of multiple programs), then we would need to add a join table to map 'Course' records to 'Program' records. Let's call this table 'ProgramCourses'. Each record of the 'ProgramCourses' table would contain a 2 fields: a 'ProgramID' field (to reference the program) and a 'CourseID' field to reference the course. If we define the relationship from the point of view of a 'Program' then the 'Program' table would be the source table, the 'ProgramCourses' table would be the join table, and the 'Course' table would be the domain table.
Don't worry if these definitions and terms aren't clear at this point. Use this section as a reference for when you run across the terms later in the tutorial.
Defining a relationship
To define a relationship in Xataface, all you need to do is tell Xataface how to select the related records using SQL. Xataface will be able to figure out how to add/remove records to the relationship from this information. This information is defined inside a the 'relationships.ini' file inside the configuration folder for the table.
Example 1: Adding a 'Courses' relationship to the 'Program' table
We want to be able to associate multiple courses with each program. We do this by defining a relationship on the 'Program' table as follows:
Let's check out our changes. Since we have defined the relationship on the 'Program' table, we will click on the 'Program' link in the navigation menu:
Notice that there is now a 'course' tab at the top of the page. Click on this tab to see the courses that are related to this Program (as defined by our 'Courses' relationship). If it says that "No records matched the request" or something to that effect, then you don't have any Course records in the relationship yet. Just click the "Add New Courses Record" button in the upper left to add a course. If there are courses in the relationship, then the Courses tab will look something like:
Currently there is only one course in the program, but we can add more. If you click on the "Add New Courses" button in the upper left, you will be presented with a new course form that will allow you to add a new course in this Program.
Example 2: Making the 'Courses' relationship a Many-to-Many relationship
Example 1 shows how Xataface can handle a one-to-many relationship. Now we will alter the database a little bit and turn this into a many-to-many relationship.
Example 3: Defining Relationships using SQL
The previous examples used a simple INI file syntax to define relationships. However, some people may be more comfortable defining their relationships using SQL. This is also possible. Let's look at the relationships.ini file from example 1:
This also could have been defined as follows:
Note: Make sure you use two underscores on either side of 'sql' in the above example. It should be '__sql__' not '_sql_'.
The two syntaxes are equivalent. In fact, the former will be converted into the later by Xataface behind the scenes.
Now let's look at example 2's relationships.ini file:
This could have been written as:
The two are equivalent. This example, however, shows how defining a relationship using SQL can be beneficial. The above SQL query will work but it can be done better using Joins as follows:
All of these 3 methods will produce the same results, but the last one will probably give a little bit better performance.
Xataface has built-in logic to figure out how to add new and existing records to relationships that you define, as long as your relationships obey a few guidelines.
Download Source Files
(Note: These PHP sources are out of date and include deprecated syntax such as short PHP open tags. You will likely need to change all PHP open tags from "<?" to "<?php" in order for the code to run in most modern PHP environments).