Chaining Lookups

Lookups can be chained to return grandparent, great-grandparent (etc.) relationships.

Basic Usage

Assume that you have a table of orders. Each order has a customer, each customer has a city, and each city a country.

The below code will return two columns, being the ref and country_name columns.

$run = ExtractAndTransform::transform('Orders and Countries')
    ->from('orders')
    ->select([
        'ref' => 'order_ref',
        'country_name' => Expr::lookup('customers', 'customer_id', 'id', 'city_id')
            ->then('cities', 'id', 'country_id')
            ->then('countries', 'id', 'name'),
    ])
    ->toTable('lookup_result_chain')
    ->run();

The interim columns from the customers and cities tables are not returned. If we wanted the customer name and city name as well, then we we need:

$run = ExtractAndTransform::transform('Orders and Countries')
    ->from('orders')
    ->select([
        'ref' => 'order_ref',
        'customer_name' => Expr::lookup('customers', 'customer_id', 'id', 'name'),
        'city_name' => Expr::lookup('customers', 'customer_id', 'id', 'city_id')
            ->then('cities', 'id', 'name')
        'country_name' => Expr::lookup('customers', 'customer_id', 'id', 'city_id')
            ->then('cities', 'id', 'country_id')
            ->then('countries', 'id', 'name'),
    ])
    ->toTable('lookup_result_chain')
    ->run();

This will return a four-column table now including the customer and city names.

Dynamic Chained Lookups

$config = [
    'selects' => [
        'ref' => [
            'type' => 'column',
            'column' => 'order_ref',
        ],
        'country_name' => [
            'type' => 'lookup',
            'steps' => [
                [
                    'table' => 'customers',
                    'local' => 'customer_id',
                    'foreign' => 'id',
                    'target' => 'city_id',
                ],
                [
                    'table' => 'cities',
                    'local' => 'city_id', // Implied from previous target, but stored explicitly in steps
                    'foreign' => 'id',
                    'target' => 'country_id',
                ],
                [
                    'table' => 'countries',
                    'local' => 'country_id',
                    'foreign' => 'id',
                    'target' => 'name',
                ],
            ],
        ],
    ],
    'wheres' => [],
];

$transformation = Transformation::create([
    'name' => 'JSON Chain Transform',
    'source_table' => 'orders',
    'destination_table_pattern' => 'lookup_result_json_chain',
    'configuration' => $config,
]);

$run = $transformation->run();

In order to run chained lookups dynamically, the 'steps' element of the lookup must be specified. The local field must be specified in this JSON even though it is implied from the previous table.