15.7. Joins
Prev | Chapter 15. Mapping Metadata | Next

## 15.7. Joins

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

Prev | Up | Next
15.6. Column | Home | 15.8. Inheritance