Documentation Index
Fetch the complete documentation index at: https://docs.fiquela.io/llms.txt
Use this file to discover all available pages before exploring further.
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
]
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
// 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.