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:
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