Document SQL query builder

[chriskapp]

Fusio has a quite powerful SQL builder to produce nested results based on SQL queries. This feature is more hidden since we have no real documentation about this, which is a shame since it is a really great feature.

We should add a chapter with examples in our documentation to explain how it is possible to use it since it is a really common use case. See also #168 (comment)

The following shows an example taken from a project, how to build a nested structure based on multiple SQL queries.

$startIndex = (int) $request->getParameter('startIndex');
$startIndex = $startIndex <= 0 ? 0 : $startIndex;
$condition  = getCondition($request);
$builder    = new \PSX\Sql\Builder($connection);

$sql = 'SELECT supplier.id,
               supplier.name,
               supplier.insertDate
          FROM app_supplier supplier
         WHERE supplier.status = 1
           AND ' . $condition->getExpression($connection->getDatabasePlatform()) . '
      ORDER BY supplier.name ASC
         LIMIT :startIndex, 32';

$parameters = array_merge($condition->getValues(), ['startIndex' => $startIndex]);
$definition = [
    'totalResults' => $builder->doValue('SELECT COUNT(*) AS cnt FROM app_supplier WHERE status = 1', [], $builder->fieldInteger('cnt')),
    'startIndex' => $startIndex,
    'entries' => $builder->doCollection($sql, $parameters, [
        'id' => $builder->fieldInteger('id'),
        'name' => 'name',
        'count' => $builder->doValue('SELECT COUNT(*) AS cnt FROM app_article WHERE supplierId = :supplierId', ['supplierId' => new \PSX\Sql\Reference('id')], $builder->fieldInteger('cnt')),
        'insertDate' => $builder->fieldDateTime('insertDate'),
        'links' => [
            'self' => $builder->fieldReplace('/supplier/{id}'),
        ]
    ])
];

return $response->build(200, [], $builder->build($definition));

[chriskapp]

We have now added a chapter to our documentation:
https://fusio.readthedocs.io/en/latest/development/sql_builder.html