Joining data sources

FiQueLa can join data from different files — even across different formats — in a single query. Any file that FiQueLa can open can be used as a join source.

Joins load all data from the joined sources into memory. For very large files, this can significantly increase memory usage. Plan accordingly when joining large datasets.

Join types

Join type	Fluent method	FQL keyword	Description
Inner	`innerJoin()` / `join()`	`INNER JOIN` or `JOIN`	Returns rows that have matches in both sources. `join()` is an alias for `innerJoin()`.
Left outer	`leftJoin()`	`LEFT JOIN` or `LEFT OUTER JOIN`	All rows from the left source; matched rows from the right
Right outer	`rightJoin()`	`RIGHT JOIN` or `RIGHT OUTER JOIN`	All rows from the right source; matched rows from the left
Full outer	`fullJoin()`	`FULL JOIN` or `FULL OUTER JOIN`	All rows from both sources; unmatched sides filled with `null`

Fluent API

Pass a query object as the join source along with an alias, then call on() to specify the join condition. You can provide the alias in two ways:

As a second parameter: ->leftJoin($query, 'alias')
Via fluent as(): ->leftJoin($query)->as('alias')

Both approaches are equivalent and backward-compatible.

use FQL\Enum\Operator;
use FQL\Query;

$users = Query\Provider::fromFileQuery('json(./examples/data/users.json).data.users');
$orders = Query\Provider::fromFileQuery('xml(./examples/data/orders.xml).orders.order');

// Alias as parameter
$query = $users
    ->select('id', 'name')
    ->select('o.id')->as('orderId')
    ->select('o.total_price')->as('totalPrice')
    ->leftJoin($orders, 'o')
        ->on('id', Operator::EQUAL, 'user_id')
    ->groupBy('o.id')
    ->orderBy('totalPrice')->desc();

// Alias via as() — equivalent
$query = $users
    ->select('id', 'name')
    ->select('o.id')->as('orderId')
    ->select('o.total_price')->as('totalPrice')
    ->leftJoin($orders)->as('o')
        ->on('id', Operator::EQUAL, 'user_id')
    ->groupBy('o.id')
    ->orderBy('totalPrice')->desc();

Multiple joins

You can chain multiple join calls:

$innerData = Query\Provider::fromFileQuery('xml(file.xml).SHOP.SHOPITEM');
$leftData = Query\Provider::fromFileQuery('json(file.tmp).data.customers');

$query = Query\Provider::fromFile('./path/to/file.csv')
    ->innerJoin($innerData, 'p')
        ->on('rightId', Operator::EQUAL, 'leftId')
    ->leftJoin($leftData, 'c')
        ->on('rightId', Operator::EQUAL, 'leftId');

FQL syntax

FROM file_reference [AS alias]
[
    {[INNER] JOIN | {LEFT|RIGHT|FULL} [OUTER] JOIN}
    {file_reference | (select_statement)}
    AS alias_reference
    ON where_condition
]

The alias is required for joined sources, and fields from joined sources are referenced with alias.field notation.

SELECT
    id,
    name,
    o.id AS orderId,
    o.total_price AS totalPrice
FROM json(./examples/data/users.json).data.users
LEFT JOIN
    xml(./examples/data/orders.xml).orders.order AS o
        ON id = user_id
GROUP BY o.id
ORDER BY totalPrice DESC

Subquery JOINs

You can use a full SELECT statement as a join source by wrapping it in parentheses. The subquery is parsed recursively and must be aliased:

SELECT
    u.id,
    u.name,
    recent.last_order_date
FROM json(users.json).data.users AS u
LEFT JOIN (
    SELECT user_id, MAX(order_date) AS last_order_date
    FROM xml(orders.xml).orders.order
    GROUP BY user_id
) AS recent
    ON u.id = recent.user_id
ORDER BY recent.last_order_date DESC

Subquery JOINs let you pre-filter or aggregate the join source before matching rows. The subquery runs its own full pipeline independently.

Joining across file formats

The join sources can use any format that FiQueLa supports. The left and right sources do not need to use the same format:

// JSON users left-joined with XML orders
$users = Query\Provider::fromFileQuery('json(users.json).data.users');
$orders = Query\Provider::fromFileQuery('xml(orders.xml).orders.order');

$query = $users
    ->select('id', 'name')
    ->select('o.total_price')->as('totalPrice')
    ->leftJoin($orders, 'o')
        ->on('id', Operator::EQUAL, 'user_id');

-- Same query in FQL
SELECT id, name, o.total_price AS totalPrice
FROM json(users.json).data.users
LEFT JOIN xml(orders.xml).orders.order AS o
    ON id = user_id

ON condition

The on() method (fluent) and the ON clause (FQL) accept the same condition syntax as where(). The left-hand field refers to the left source; the right-hand value refers to the joined source. When both sources are aliased, you can use aliased dot-notation paths in ON conditions for clarity:

FROM json(users.json).data.users AS u
LEFT JOIN xml(orders.xml).orders.order AS o
    ON u.id = o.user_id

$query->from('data.users')->as('u')
    ->leftJoin($orders)->as('o')
        ->on('u.id', Operator::EQUAL, 'o.user_id');

Calling on() multiple times overwrites the previous condition. Define all join criteria in a single on() call, or use condition chaining through and()/or() after the on() call.

Get Started

Core Concepts

Querying

Functions Reference

Advanced

Joining data sources

Join types

Fluent API

Multiple joins

FQL syntax

Subquery JOINs

Joining across file formats

ON condition

​Join types

​Fluent API

​Multiple joins

​FQL syntax

​Subquery JOINs

​Joining across file formats

​ON condition

Join types

Fluent API

Multiple joins

FQL syntax

Subquery JOINs

Joining across file formats

ON condition