PHP code example of soyuka / esql

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

soyuka / esql example snippets

use App\Entity\Car;
use App\Entity\Model;
use Soyuka\ESQL\Bridge\Doctrine\ESQL;
use Soyuka\ESQL\Bridge\Automapper\ESQLMapper;

$connection = $managerRegistry->getConnection();
$mapper = new ESQLMapper($autoMapper, $managerRegistry);
$esql = new ESQL($managerRegistry, $mapper);
$car = $esql(Car::class);
$model = $car(Model::class);

$query = <<<SQL
SELECT {$car->columns()}, {$model->columns()} FROM {$car->table()}
INNER JOIN {$model->table()} ON {$car->join(Model::class)}
WHERE {$car->identifier()}
SQL;

$stmt = $connection->prepare($query);
$stmt->execute(['id' => 1]);

var_dump($esql->map($stmt->fetch()));

use ApiPlatform\Metadata\ApiResource;
use Soyuka\ESQL\Bridge\ApiPlatform\State\Provider;
use Soyuka\ESQL\Bridge\ApiPlatform\State\Processor;

/**
 * #[ApiResource(provider: Provider::class, processor: Processor::class)]
 */
 class Car {}

$data = new Car();
$car = $esql($data);
$binding = $this->automapper->map($data, 'array'); // map your object to an array somehow

$query = <<<SQL
UPDATE {$car->table()} SET {$car->predicates()}
WHERE {$car->identifier()}
SQL;

$connection->beginTransaction();
$stmt = $connection->prepare($query);
$stmt->execute($binding);
$connection->commit();

$data = new Car();
$binding = $this->automapper->map($data, 'array'); // map your object to an array somehow
$car = $esql($data)
$query = <<<SQL
INSERT INTO {$car->table()} ({$car->columns()}) VALUES ({$car->parameters($binding)});
SQL;

$connection->beginTransaction();
$stmt = $connection->prepare($query);
$stmt->execute($binding);
$connection->commit();

use Soyuka\ESQL\Bridge\Doctrine\ESQL;
use App\Entity\Car;

// Doctrine's ManagerRegistry and an ESQLMapperInterface (see below)
$esql = new ESQL($managerRegistry, $mapper);
$car = $esql(Car::class);

// the Table name
// outputs "car"
echo $car->table();

// the sql alias
// outputs "car"
echo $car->alias();

// Get columns: columns(?array $fields = null, string $output = $car::AS_STRING): string
// columns() outputs "car.id, car.name, car.model_id"
// output can also take: $car::AS_ARRAY | $car::WITHOUT_ALIASES | $car::WITHOUT_JOIN_COLUMNS | $car::IDENTIFIERS
echo $car->columns();

// Get a single column: column(string $fieldName): string
// column('id') outputs "car.id"
echo $car->column('id');

// Get an identifier predicate: identifier(): string
// identifier() outputs "car.id = :id"
echo $car->identifier();

// Get a join predicate: join(string $relationClass): string
// join(Model::class) outputs "car.model_id = model.id"
echo $car->join(Model::class);

// All kinds of predicates: predicates(?array $fields = null, string $glue = ', '): string
// predicates() outputs "car.id = :id, car.name = :name"
echo $car->predicates();

// Get a normalized value for SQL, sometimes booleans are in fact integer: toSQLValue(string $fieldName, $value)
// toSQLValue('sold', true) output "1" on sqlite but "true" on postgresql
$car->toSQLValue('sold', true);

// Given an array of bindings, will output keys prefixed by `:`: parameters(array $bindings): string
// parameters(['id' => 1, 'color' => 'blue']) will output ":id, :color"
$car->parameters();

$car = $esql(Car::class);
$car->alias(); // car
$model = $car(Model::class);
$model->alias(); // car_model

// Let's compute statistics and map car properties to the Aggregate class
// The entity used is Car, mapped to Aggregate and using an SQL alias "car"
$car = $esql(Car::class, Aggregate::class, 'car');
// The model relation doesn't exist, let's just use the model property
$model = $car(Model::class, 'model');

// AutoMapper is an instance of JanePHP's automapper (https://github.com/janephp/automapper)
$mapper = new ESQLMapper($autoMapper, $managerRegistry);
$model = new Model();
$model->id = 1;
$model->name = 'Volkswagen';

$car = new Car();
$car->id = 1;
$car->name = 'Caddy';
$car->model = $model;

$car2 = new Car();
$car2->id = 2;
$car2->name = 'Passat';
$car2->model = $model;

// Aliases should be generated by ESQL to map properties and relation properly
$this->assertEquals([$car, $car2], $mapper->map([
    ['car_id' => '1', 'car_name' => 'Caddy', 'model_id' => '1', 'model_name' => 'Volkswagen'],
    ['car_id' => '2', 'car_name' => 'Passat', 'model_id' => '1', 'model_name' => 'Volkswagen'],
], Car::class));

$esql = $this->esql->__invoke(Car::class);
$parameters = [];

$query = <<<SQL
SELECT {$esql->columns()} FROM {$esql->table()}
SQL;

if ($paginator = $this->dataPaginator->getPaginator($resourceClass, $operationName)) {
    return $paginator($esql, $query, $parameters, $context);
}

$resourceClass = Car::class;
$operationName = 'get';
$esql = $this->esql->__invoke($resourceClass);
['itemsPerPage' => $itemsPerPage, 'firstResult' => $firstResult, 'nextResult' => $nextResult, 'page' => $page, 'partial' => $isPartialEnabled] = $this->dataPaginator->getPaginationOptions($resourceClass, $operationName);

$query = <<<SQL
SELECT {$esql->columns()} FROM {$esql->table()}
LIMIT $itemsPerPage OFFSET $firstResult
SQL;

// fetch data somehow and map
$data = $esql->map($data);

$countQuery = <<< SQL
SELECT COUNT(1) as count FROM {$esql->table()}
SQL;

// get count results somehow
$count = $countResult['count'];

return $isPartialEnabled ? new PartialPaginator($data, $page, $itemsPerPage) : new Paginator($data, $page, $itemsPerPage, $count);