Skip to main content

Lookups

More powerful than maps, a lookup enables you to combine tables, performing a left join to return another value.

Basic Example​

$run = ExtractAndTransform::transform('Lookup Customer')
    ->from('orders')
    ->select([
        'ref' => 'order_ref',
        'customer_name' => Expr::lookup('customers', 'customer_id', 'id', 'name'),
    ])
    ->toTable('lookup_result_1')
    ->run();

This example will return a table with two columns, ref and customer_name. The customer name will be joined from the customers table. orders.customer_id is joined to customers.id and customers.name is returned.

Joining Multiple Tables​

There is no problem with joining multiple tables, or having multiple columns from one table. The below code is perfectly valid. The system in this case will not make two joins to customers - it will identify the existing join and just add both columns to it.

$run = ExtractAndTransform::transform('Multi Lookup')
    ->from('orders')
    ->select([
        'ref' => 'order_ref',
        'cust_email' => Expr::lookup('customers', 'customer_id', 'id', 'email'),
        'cust_name' => Expr::lookup('customers', 'customer_id', 'id', 'name'),
        'stat_cat' => Expr::lookup('statuses', 'status_code', 'code', 'category'),
    ])
    ->toTable('lookup_result_3')
    ->run();

Operations on Joined Tables​

Operations can be chained on Joined tables, like concatenation. For example, the following creates a column city_and_country with entries like:

  • London, UK
  • Paris, France
  • New York, USA
$run = ExtractAndTransform::transform('Multi Lookup')
    ->from('orders')
    ->select([
        'ref' => 'order_ref',
        'city_and_country' => Expr::concat(
            Expr::lookup('cities', 'city_id', 'id', 'name'),
            ', ',
            Expr::lookup('countries', 'country_id' 'id', 'name')
        ),
    ])
    ->toTable('lookup_result_3')
    ->run();