PHP code example of sad_spirit / pg_builder

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

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

    

sad_spirit / pg_builder example snippets

use sad_spirit\pg_builder\{
    Select,
    StatementFactory,
    converters\BuilderSupportDecorator
};
use sad_spirit\pg_wrapper\{
    Connection,
    converters\DefaultTypeConverterFactory
};

$wantPDO = false;

if ($wantPDO) {
    $pdo       = new \PDO('pgsql:host=localhost;user=username;dbname=cms');
    // Uses DB connection properties to set up parsing and building of SQL 
    $factory   = StatementFactory::forPDO($pdo);
    // NB: This still ator(
        $connection->getTypeConverterFactory(),
        $factory->getParser()
    ));
}

// latest 5 news
/** @var Select $query */
$query      = $factory->createFromString(
    'select n.* from news as n order by news_added desc limit 5'
);

// we also need pictures for these...
$query->list[] = 'p.*';
$query->from[0]->leftJoin('pictures as p')->on = 'n.picture_id = p.picture_id';

// ...and need to limit them to only specific rubrics
$query->from[] = 'objects_rubrics as ro';
$query->where->and('ro.rubric_id = any(:rubric::integer[]) and ro.obj_id = n.news_id');

// ...and keep 'em fresh
$query->where->and('age(news_added) < :age::interval');

// $generated contains a query, mapping from named parameters to positional ones, types info
// it can be easily cached to prevent parsing/building SQL on each request
$generated = $factory->createFromAST($query);

// Note that we don't have to specify parameter types, these are extracted from query
if ($wantPDO) {
    $result = $pdo->prepare($generated->getSql());
    $result->execute($converter->convertParameters(
        $generated,
        [
            'rubric' => [19, 20, 21],
            'age'    => 30 * 24 * 3600        
        ]       
    ));
} else {
    $result = $generated->executeParams(
        $connection, 
        [
            'rubric' => [19, 20, 21],
            'age'    => 30 * 24 * 3600
        ]
    );
}


foreach ($result as $row) {
    print_r($row);
}

echo $generated->getSql();