Fusio

Recipes / Sql / Select build nested result

Routes
resources/routes.yaml
"/test":
  version: 1
  methods:
    GET:
      public: true
      action: "${dir.src}/sql-select-complex.php"
Connection
resources/connections.yaml
Database-Connection:
  class: Fusio\Adapter\Sql\Connection\Sql
  config:
    type: "pdo_mysql"
    host: "127.0.0.1"
    username: "app"
    password: "secret"
    database: "app"
Action
resources/sql-select-complex.php
<?php
/**
 * @var \Fusio\Engine\ConnectorInterface $connector
 * @var \Fusio\Engine\ContextInterface $context
 * @var \Fusio\Engine\RequestInterface $request
 * @var \Fusio\Engine\Response\FactoryInterface $response
 * @var \Fusio\Engine\ProcessorInterface $processor
 * @var \Psr\Log\LoggerInterface $logger
 * @var \Psr\SimpleCache\CacheInterface $cache
 */

/** @var \Doctrine\DBAL\Connection $connection */
$connection = $connector->getConnection('Database-Connection');
$builder    = new \PSX\Sql\Builder($connection);

$sql = 'SELECT article.id,
               article.status,
               article.articleNumber,
               article.articleCount,
               article.description,
               article.serialNumber,
               article.comment,
               article.insertDate,
               location.id AS locationId,
               location.name AS locationName,
               agroup.id AS groupId,
               agroup.name AS groupName,
               supplier.id AS supplierId,
               supplier.name AS supplierName
          FROM app_article article 
    INNER JOIN app_location location
            ON article.locationId = location.id
    INNER JOIN app_group agroup
            ON article.groupId = agroup.id
    INNER JOIN app_supplier supplier
            ON article.supplierId = supplier.id
         WHERE article.status = 1 
      ORDER BY article.insertDate DESC 
         LIMIT 0, 32';

$definition = [
    'totalResults' => $builder->doValue('SELECT COUNT(*) AS cnt FROM app_article WHERE status = 1', [], $builder->fieldInteger('cnt')),
    'entries' => $builder->doCollection($sql, [], [
        'id' => $builder->fieldInteger('id'),
        'location' => [
            'id' => $builder->fieldInteger('locationId'),
            'name' => 'locationName',
        ],
        'group' => [
            'id' => $builder->fieldInteger('groupId'),
            'name' => 'groupName',
        ],
        'supplier' => [
            'id' => $builder->fieldInteger('supplierId'),
            'name' => 'supplierName',
        ],
        'articleNumber' => 'articleNumber',
        'articleCount' => $builder->fieldInteger('articleCount'),
        'description' => 'description',
        'serialNumber' => 'serialNumber',
        'comment' => 'comment',
        'insertDate' => $builder->fieldDateTime('insertDate'),
        'links' => [
            'self' => $builder->fieldReplace('/article/{id}'),
        ]
    ])
];

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