<relationship>

Abstract

Learn to configure the <relationship> element for Pull Reports™ Ad Hoc report and data service software.

Table of Contents

Catalog Configuration Java API reference: JoinColumnsRelationshipConfiguration.Builder, JoinTableRelationshipConfiguration.Builder

The <relationship> element specifies a SQL join relationship between two <table>s. within the Export Report REST API. Nest <relationship> elements beneath the report's base table to any depth to join additional information to the report's relational data tree. <table>s within <relationship>s are referenced within the Export Report REST API's columns, filter, and sort parameters via their table resource path, the unique forward slash (/) separated list of table ids which identifies the table within the join tree.

<relationship>s SQL joins are always directional from the parent <table> to the child <table>. This directionality means that the parent <table> is always included in the Export Report REST API SQL join if the child <table> is included. Additionally, SQL joins from a table to a <relationship> child table are always a left outer or inner join. Right outer SQL joins are not supported.

<relationship>s support three types of SQL joins:

to-many, `<join_column>`

A to-many, <join_column> configuration is when one or more foreign key columns on the child table reference one or more columns on the parent table. This type of join must be cardinality="many".

Configure this type of relationship with one or more <join_column> elements as direct children of the <relationship> element. The <join_column> columnName attribute references the child foreign key column, and the referencedColumnName references the parent key column.

to-one, `<join_column>`

A to-one, <join_column> configuration is when one or more foreign key columns on the parent table reference one or more columns on the child table. This type of join must be cardinality="one".

Configure this type of relationship with one or more <join_column> elements as direct children of the <relationship> element. The <join_column> columnName attribute references the parent foreign key column, and the referencedColumnName references the child key column.

to-many, `<join_table>`

A to-many, <join_table> configuration requires an indirection table between the parent and child tables. The indirection table must have one or more foreign key columns that reference columns on each of the parent and child tables. Configure this type of relationship with a <join_table> element.

Pull Reports™ and the Java Persistence API

Because of the similarity in purpose between <relationship>s and object relational mapping (ORM) technology, the Pull Reports™ Schema and Catalog Configuration Java API relationship vocabulary follows the Java Persistence API (JPA) mapping vocabulary.

Usage

Basic `<relationship>` example

This example uses a real estate data model which contains information about home sales, the owners of homes, and the realtors used by an owner. The following diagram represents the six tables of the data model and the foreign key relationships between them.

The data model relationships may be described like this:

A home may have 0 to many sales but a sale is associated with one home.
A home may have 0 to many owners and an owner may own 0 to many homes.
A home has exactly one address.
An owner may have 0 to one realtors but a realtor may have 0 to many owners.
An owner may have many home_expenses. Each expense is associated with one home.

Use the following join techniques to create a <report> with the the home table as the base table and the address, sale, owner, and owner_realtor tables as nested <relationship>s:

Use a to-many, <join_column> join to left outer join the sale table to home with a <join_column> from sale.home_id to home.id. The <relationship> is one to many from home to sale.
Use a to-many, <join_table> join to left outer join the owner table to home with a <join_table> element on the home_owner indirection table. The <relationship> is zero to many from home to owner.
Use a to-one, <join_column> join to inner join the address table to home with a <join_column> from home.address_id to address.id. The inner join is appropriate if the home.address_id column may not be null. The <relationship> is one to one from home to address.
Use a to-one, <join_column> join to left outer join the owner_realtor table to owner with two <join_column>s from owner.first_name to owner_realtor.first_name and owner.last_name to owner_realtor.last_name respectively. The <relationship> is many to one from owner to owner_realtor.
Use a to-many, <join_column> join to left outer join the home_expense table to owner and home with two <join_column>s from owner_expense.owner_id to owner.id and owner_expense.home_id to home.id. The later join uses the <join_column> referencedTablePath attribute to indicate the referencedColumnName is on the home grandparent table of the relationship and not the parent table.

The following Pull Reports™ configuration represents the data model within the Pull Reports REST API. See the comments within the XML for specific <column> configuration guidance.

Example 1. Via an XML Catalog file

<?xml version="1.0" encoding="UTF-8"?>
<catalog xmlns="http://www.pullreports.com/catalog-1.6.1" id="realestate" name="Real Estate Reports"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.pullreports.com/catalog-1.6.1
    https://www.pullreports.com/docs/xsd/pullreports-catalog-1.6.1.xsd">
    <report id="home" name="Home Report">
        <export_config defaultColumns='id'/>
        <table id="home" name="realestate.home" displayName="Home" primaryKeyColumns="id">
            <column id="id" name="id" paramType="java.lang.Integer"/>
            <!-- A Home has One-to-Many Past Sales

            Example REST API URL: 
                /pullreports/catalog/realestate/report/home/export?columns=/home/sale 

            Resultant SQL from statement: 
                from realestate.home left outer join realestate.sale 
                    on realestate.sale.home_id = realestate.home.id
            -->
            <relationship>
                <join_column columnName="home_id" referencedColumnName="id" />
                <table id="sale" displayName="Sale" name="realestate.sale">
                    <column id="sale_id" name="id" paramType="java.lang.Integer"/>
                    <column id="home_id" name="home_id" paramType="java.lang.Integer"/>
                    <column id="amount" name="amount" paramType="java.lang.Double"/>
                </table>
            </relationship>
            <!-- A Home has One to One Addresses

            Example REST API URL: 
                /pullreports/catalog/realestate/report/home/export?columns=/home/address

            Resultant SQL from statement: 
                from realestate.home inner join realestate.address 
                     on realestate.address.id = realestate.home.address_id 
            -->
            <relationship join="inner" cardinality="one">
                <join_column columnName="address_id" referencedColumnName="id" />
                <table id="address" displayName="Address" name="realestate.address">
                    <column id="aid" name="id" paramType="java.lang.Integer"/>
                    <column id="street" name="street" displayName="Street"/> 
                    <column id="city" name="city" displayName="City" /> 
                    <column id="state" name="state" displayName="State"/> 
                    <column id="zipcode" name="zipcode" displayName="Zip Code"/> 
                </table>
            </relationship>
            <!-- A Home has Many to Many Owners

            Example REST API URL: 
                /pullreports/catalog/realestate/report/home/export?columns=/home/owner

            Resultant SQL from statement: 
                from realestate.home left outer join realestate.home_owner 
                     on realestate.home_owner.home_id = realestate.home.id
                 left outer join realestate.owner 
                     on realestate.home_owner.owner_id = realestate.owner.id
            -->
            <relationship>
                <join_table name="realestate.home_owner">
                    <join_columns>
                        <join_column columnName="home_id" referencedColumnName="id" />
                    </join_columns>
                    <inverse_join_columns>
                        <join_column columnName="owner_id" referencedColumnName="id" />
                   </inverse_join_columns>
                </join_table>
                <table id="owner" displayName="Owner" name="realestate.owner" primaryKeyColumns="oid">
                    <column id="oid" name="id" paramType="java.lang.Integer"/>
                    <column id="first_name" name="first_name"/> 
                    <column id="last_name" name="last_name"/> 
                    <!-- An owner has a many-to-one relationship with a realtor. 
                     This example demonstrates a compound key with two <join_column>s.

                    Example REST API URL: 
                        /pullreports/catalog/realestate/report/home/export?columns=/home/owner/realtor

                    Resultant SQL from statement: 
                        from realestate.home left outer join realestate.home_owner 
                             on realestate.home_owner.home_id = realestate.home.id
                         left outer join realestate.owner 
                             on realestate.home_owner.owner_id = realestate.owner.id
                         left outer join realestate.owner_realtor 
                            on realestate.owner.first_name = realestate.owner_realtor.owner_first_name and
                               realestate.owner.last_name = realestate.owner_realtor.owner_last_name
                    -->
                    <relationship cardinality="one">
                        <join_column columnName="first_name" referencedColumnName="owner_first_name" />
                        <join_column columnName="last_name" referencedColumnName="owner_last_name" />
                        <table id="realtor" displayName="Realtor" name="realestate.owner_realtor">
                            <column id="realtor_name" name="realtor_name"/>
                        </table>
                    </relationship>
                    <!-- An owner has a one-to-many relationship with a home_expense. However,
                     each home_expense is in the context of one home. Use the <join_column>
                     referencedTablePath attribute to indicate the columnName value is on the 
                     home table.

                    Example REST API URL: 
                        /pullreports/catalog/realestate/report/home/export?columns=/home/owner/expense

                    Resultant SQL from statement: 
                        from realestate.home left outer join realestate.home_owner 
                             on realestate.home_owner.home_id = realestate.home.id
                         left outer join realestate.owner 
                             on realestate.home_owner.owner_id = realestate.owner.id
                         left outer join realestate.owner_expense
                            on realestate.owner.id = realestate.owner_expense.owner_id and
                               realestate.home.id = realestate.owner_expense.home_id 
                    -->
                    <relationship>
                        <join_column columnName="owner_id" referencedColumnName="id" />
                        <join_column columnName="home_id" referencedColumnName="id" referencedTablePath="/home"/>
                        <table id="expense" displayName="Owner Expense" name="realestate.owner_expense">
                            <column id="amount" name="amount" paramType="java.lang.Double"/>
                            <column id="reason" name="reason" />
                        </table>
                    </relationship>
                </table>
            </relationship>
        </table>
    </report>
</catalog>

Example 2. Via the Catalog Configuration Java API

The following example is an identical Catalog Configuration Java API configuration.

package com.pullreports.examples.relationship;

import com.pullreports.model.Cardinality;
import com.pullreports.model.CatalogId;
import com.pullreports.model.ColumnId;
import com.pullreports.model.Join;
import com.pullreports.model.JoinColumn;
import com.pullreports.model.JoinTable;
import com.pullreports.model.ParamType;
import com.pullreports.model.ReportId;
import com.pullreports.model.TableId;
import com.pullreports.model.TablePath;
import com.pullreports.model.config.CatalogConfiguration;
import com.pullreports.model.config.CatalogConfigurationFactory;
import com.pullreports.model.config.ColumnConfiguration;
import com.pullreports.model.config.JoinColumnsRelationshipConfiguration;
import com.pullreports.model.config.JoinTableRelationshipConfiguration;
import com.pullreports.model.config.RelationshipConfiguration;
import com.pullreports.model.config.ReportConfiguration;
import com.pullreports.model.config.TableConfiguration;
import com.pullreports.model.exportconfig.ExportConfiguration;

import javax.servlet.ServletContext;
import java.util.Arrays;
import java.util.Collections;
import java.util.HashSet;
import java.util.List;

public class RelationshipCatalogConfigurationFactory implements CatalogConfigurationFactory {
    
    protected final ColumnId idColumn = new ColumnId("id");

    @Override
    public CatalogConfiguration makeCatalog(ServletContext servletContext) {

        ColumnConfiguration idColumnConfiguration = new ColumnConfiguration.Builder(
            idColumn,"id")
            .setParamType(ParamType.INTEGER).build();

        List<ColumnConfiguration> columnConfigurations = Collections.singletonList(idColumnConfiguration);

        List<RelationshipConfiguration> relationshipConfigurations = Arrays.asList(
                makeSaleRelationshipConfiguration()
                ,makeAddressRelationshipConfiguration()
                ,makeOwnerRelationshipConfiguration());

        TableConfiguration homeTableConfiguration = new TableConfiguration.Builder(
            new TableId("home"),"Home",columnConfigurations)
            .setName("realestate.home")
            .setPrimaryKeyColumnIds(Collections.singleton(idColumn))
            .setRelationshipConfigurations(relationshipConfigurations).build();

        ReportConfiguration homeReportConfiguration = new ReportConfiguration.Builder(
            new ReportId("home"),"Home Report",homeTableConfiguration)
            .setExportConfiguration(
                makeExportConfigurationBuilder().build()).build();
        
        List<ReportConfiguration> reportConfigurations = Collections.singletonList(homeReportConfiguration);

        return new CatalogConfiguration(new CatalogId("realestate"),"Real Estate Reports",reportConfigurations);
    }
    
    /**
     * Subclasses may override and add their own ExportConfiguration.Builder
     * configuration.
     * <h5>Example</h5>
     * <code>
     * @Override
     * protected ExportConfiguration.Builder makeExportConfigurationBuilder() {
     *     return super.makeExportConfigurationBuilder().setDefaultSortTerms(...)
     * }
     * </code>
     */
    protected ExportConfiguration.Builder makeExportConfigurationBuilder() {
        return new ExportConfiguration.Builder().setDefaultColumns(
            Collections.singletonList(idColumn));
    }
    
    private RelationshipConfiguration makeSaleRelationshipConfiguration() {

        ColumnConfiguration saleIdColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("sale_id"),"id")
            .setParamType(ParamType.INTEGER).build();

        ColumnConfiguration homeIdColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("home_id"),"home_id")
            .setParamType(ParamType.INTEGER).build();

        ColumnConfiguration amountColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("amount"),"amount")
            .setParamType(ParamType.DOUBLE).build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                saleIdColumnConfiguration
                ,homeIdColumnConfiguration
                ,amountColumnConfiguration);

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("sale"),"Sale",columnConfigurations)
            .setName("realestate.sale").build();

        return new JoinColumnsRelationshipConfiguration.Builder(tableConfiguration
            ,Collections.singletonList(new JoinColumn("home_id","id"))).build();
    }

    private RelationshipConfiguration makeAddressRelationshipConfiguration() {

        ColumnConfiguration addressIdColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("aid"),"id")
            .setParamType(ParamType.INTEGER).build();

        ColumnConfiguration cityColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("city"),"city")
            .setDisplayName("City").build();

        ColumnConfiguration streetColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("street"),"street")
            .setDisplayName("Street").build();

        ColumnConfiguration stateColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("state"),"state")
            .setDisplayName("State").build();

        ColumnConfiguration zipcodeColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("zipcode"),"zipcode")
            .setDisplayName("Zip Code").build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                addressIdColumnConfiguration
                ,cityColumnConfiguration
                ,streetColumnConfiguration
                ,stateColumnConfiguration
                ,zipcodeColumnConfiguration);

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("address"),"Address",columnConfigurations)
            .setName("realestate.address").build();

        return new JoinColumnsRelationshipConfiguration.Builder(tableConfiguration
            ,Collections.singletonList(new JoinColumn("address_id","id")))
            .setCardinality(Cardinality.ONE).setJoin(Join.INNER).build();
    }

    private RelationshipConfiguration makeOwnerRelationshipConfiguration() {

        ColumnConfiguration ownerIdColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("oid"),"id")
            .setParamType(ParamType.INTEGER).build();

        ColumnConfiguration firstNameColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("first_name"),"first_name").build();

        ColumnConfiguration lastNameColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("last_name"),"last_name").build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
                ownerIdColumnConfiguration
                ,firstNameColumnConfiguration
                ,lastNameColumnConfiguration);

        List<RelationshipConfiguration> relationships = Arrays.asList(
            makeOwnerRealtorRelationshipConfiguration()
            ,makeOwnerExpenseRelationshipConfiguration()
        );

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("owner"),"Owner",columnConfigurations)
                .setName("realestate.owner")
                .setPrimaryKeyColumnIds(new HashSet<>(){{add(ownerIdColumnConfiguration.getId());}})
                .setRelationshipConfigurations(relationships).build();

        JoinTable joinTable = new JoinTable("realestate.home_owner"
            ,Collections.singletonList(new JoinColumn("home_id","id"))
            ,Collections.singletonList(new JoinColumn("owner_id","id")));

        return new JoinTableRelationshipConfiguration.Builder(tableConfiguration,joinTable).build();
    }

    private RelationshipConfiguration makeOwnerRealtorRelationshipConfiguration() {

        ColumnConfiguration realtorColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("realtor_name"),"realtor_name").build();

        List<ColumnConfiguration> columnConfigurations = Collections.singletonList(
            realtorColumnConfiguration);

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("realtor"),"Realtor",columnConfigurations)
            .setName("realestate.owner_realtor").build();

        return new JoinColumnsRelationshipConfiguration.Builder(tableConfiguration
            ,Arrays.asList(
                new JoinColumn("first_name","owner_first_name")
                ,new JoinColumn("last_name","owner_last_name")))
            .setCardinality(Cardinality.ONE).build();
    }

    private RelationshipConfiguration makeOwnerExpenseRelationshipConfiguration() {

        ColumnConfiguration amountColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("amount"),"amount").setParamType(ParamType.DOUBLE).build();

        ColumnConfiguration reasonColumnConfiguration = new ColumnConfiguration.Builder(
            new ColumnId("reason"),"reason").build();

        List<ColumnConfiguration> columnConfigurations = Arrays.asList(
            amountColumnConfiguration,reasonColumnConfiguration);

        TableConfiguration tableConfiguration = new TableConfiguration.Builder(
            new TableId("expense"),"Owner Expense",columnConfigurations)
            .setName("realestate.owner_expense").build();

        return new JoinColumnsRelationshipConfiguration.Builder(tableConfiguration
            ,Arrays.asList(new JoinColumn("owner_id","id")
                ,new JoinColumn("home_id","id",new TablePath("/home"))))
            .build();
    }
}

Children

<access_control_voter>?

(<join_column>+ | <join_table>)

(<table> | <table_ref>)

Parents

<table>

Attributes

cardinality (default: many)

Specifies the cardinality of the join between the parent <table> of the <relationship> and the child <table> of the <relationship>. Permitted values are one or many. For to-many and to-one, <join_column> relationships, the cardinality attribute determines the location of the database columns referenced by child <join_column> columnName and referencedColumnName attributes.

The Pull Reports™ Report Creator reflects the cardinality attribute via a "stacked" display within the Columns panel.

join (default: left)

Specifies the join type. Permitted values are left or inner. right outer joins are not supported.

Prev		Next
<pre_filter>	Home	<report>