15.7. Joins

15.7.1. Shortcuts
15.7.2. Self Joins
15.7.3. Target Fields

One of the mainstays of a relational database is the ability for one record to refer to another. This allows relational databases to join related records together to retrieve large chunks of data efficiently. A record A typically references a record B by setting some of its columns to the same values as B's primary key columns. The columns in A that mirror primary key columns in B are called foreign key columns. Most relational databases can enforce several special behaviors for foreign keys, which we explore in Section 15.12, “Foreign Keys”. For now, though, it is sufficient to remember that foreign keys are sets of columns that act as a logical reference to another record by storing that record's primary key values.

[Note]Note

In Kodo, logical foreign key columns do not have to link to primary key columns. You can link any columns. You can also express joins in which some columns must have specific constant values. See the Reference Guide's Section 7.6, “Non-Standard Joins” for details on Kodo's support for non-standard joins.

Consider the two tables above. Table T1 has a primary key column PK, two foreign key columns FK1 and FK2, and a standard data column DATA. Table T2 has two primary key columns PK1 and PK2, foreign key column FK, and a data column. The dotted arrows indicate that T1's foreign key columns link to T2's primary key columns, and T2's foreign key column links to T1's primary key column. A foreign key's table is called the source table, and the table it links to is called the target table.

By matching the data in the foreign key and primary key columns, you can see that row R1 in T1 references row R2 in T2 and vice versa. Conversely, row R2 in T1 references row R1 in T2 and vice versa. The solid arrows depict these cross-referencing relationships.

In JDOR, you specify a foreign key column's target primary key column with the column element's target attribute. So, assuming we're mapping something in a class using the T1 table, the link from T1 to T2 becomes:

<class name="..." table="T1">
    <...>
        <column name="FK1" target="PK1"/>
        <column name="FK2" target="PK2"/>
    </...>
</class>

Note that we did not qualify the column names above with the containing table name. The foreign key column names are not qualified because the foreign key columns are in the parent class' table. The target primary key column names are not qualified because JDOR defaults the target table based on the context of the mapping. For example, when you map a relation field, JDOR assumes the target table is the table of the related class.

A standard forward foreign key like the one above maps the target table records referenced by the current source table record. You can, however, also map an inverse foreign key. An inverse foreign key maps all the source table records referencing the current target table record. Inverse foreign keys only apply to mappings from the target table - in this case, T2. Thus, you must qualify the foreign key column names with their source table name:

<class name="..." table="T2">
    <...>
        <column name="T1.FK1" target="PK1"/>
        <column name="T1.FK2" target="PK2"/>
    <.../>
</class>

15.7.1. Shortcuts

Now let's shift our focus to the link from T2 to T1 through the T2.FK foreign key column. This is easy enough to represent in mapping metadata:

<class name="..." table="T2">
    <...>
        <column name="FK" target="PK"/>
    <.../>
</class>

We can, however, simplify this even further. When the target table's primary key consists of a single column, you can omit the target attribute. JDOR assumes that the target is the lone primary key column of the target table (which is itself defaulted based on the mapping context):

<class name="..." table="T2">
    <...>
        <column name="FK"/>
    <.../>
</class>

Recall from the previous section that when you only need to specify a column name, you can use the enclosing element's column attribute in place of a nested column element. This allows us to be even more concise:

<class name="..." table="T2">
    <... column="FK"/>
</class>

JDOR provides one other significant shortcut not seen here. When the foreign key column name is the same as the target primary key column name, you do not have to set the target attribute.

15.7.2. Self Joins

Foreign key columns do not have to reference records in other tables. Often, a table will use foreign keys to itself.

As you can see, R1 links to R2 through the FK foreign key column. The mapping is straightforward:

<class name="..." table="T1">
    <...>
        <column name="FK" target="PK"/>
    <.../>
</class>

Or, after applying available shortcuts:

<class name="..." table="T1">
    <... column="FK"/>
</class>

You may be wondering how to represent an inverse self-join. Because the source and target tables are the same, the naïve approach results in a mapping that looks identical to a forward join, which is obviously incorrect:

<class name="..." table="T1">
    <...>
        <column name="FK" target="PK"/>
    <.../>
</class>

The answer is simple and surprisingly consistent. When we created an inverse foreign key mapping between T1 and T2 above, we had to fully qualify the foreign key column names. To represent an inverse self join, just use the same trick:

<class name="..." table="T1">
    <...>
        <column name="T1.FK" target="PK"/>
    <.../>
</class>

Though the foreign key column is in the parent class' table and wouldn't normally require qualification, using the qualified name serves as a hint to the JDOR implementation that this is an inverse join.

15.7.3. Target Fields

JDOR allows you to specify a target primary key field name in place of a target column with the target-field attribute. This is simply an object-oriented shortcut for targetting the named field's column. For example:

<class name="A" table="T1">
    <field name="relationToB">
        <column name="FK1" target-field="id1"/>
        <column name="FK2" target-field="id2"/>
    </field
</class>
<class name="B" table="T2">
    <field name="id1" column="PK1"/>
    <field name="id2" column="PK2"/>
</class>

Is equivalent to:

<class name="A" table="T1">
    <field name="relationToB">
        <column name="FK1" target="PK1"/>
        <column name="FK2" target="PK2"/>
    </field
</class>
<class name="B" table="T2">
    <field name="id1" column="PK1"/>
    <field name="id2" column="PK2"/>
</class>

You can fully-qualify a target field name with its class, just as you can fully-qualify a target column name with its table.

[Note]Note

Target fields are useful when you rely on Kodo's mapping tools to create the schema, and you want to supply mapping hints without having to name the target columns. The Reference Guide Details Kodo's mapping tools in Section 7.1, “Forward Mapping”.

Also, Kodo does not require that target fields be primary key fields. See Section 7.6, “Non-Standard Joins” in the Reference Guide.

 

Skip navigation bar   Back to Top