<?php
require_once 'vendor/autoload.php';
require_once 'output-results.php';
use Zend\Db\Sql\Predicate\Expression;
use Zend\Db\Sql\Predicate\Predicate;
use Zend\Db\Sql\Sql;
$adapter = (require_once 'adapter.php');
$queryValues = [':invoiceMin' => 1, ':invoiceMax' => 20, ':countryOne' => 'USA', ':countryTwo' => 'Germany'];
/** @var \Zend\Db\Adapter\Driver\StatementInterface $statement */
$sql = new Sql($adapter['sqlite']);
$countryPredicate = new Predicate();
$countryPredicate->equalTo('c.Country', 'USA')->or->equalTo('c.Country', 'Germany');
$invoicePredicate = new Predicate();
$invoicePredicate->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax']);
$predicate = new Predicate();
//$predicate->addPredicate($invoicePredicate)->addPredicate($countryPredicate);
$predicate->addPredicates([$invoicePredicate]);
$predicate->andPredicate($countryPredicate);
$sqliteFullNameExpression = new Expression('c.FirstName || " " || c.LastName');
$whereLastNameExpression = new Expression('LastName IN (?)', [['Stevens', 'Brooks', 'Harris']]);
/** @var Zend\Db\Sql\Select $select */
$select = $sql->select();
$select->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', "Full Name" => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total'])->where($predicate)->where($whereLastNameExpression)->order(['c.Country DESC', 'i.Total DESC', 'c.LastName'])->limit(30)->offset(1);
/** @var \Zend\Db\Adapter\Driver\Pdo\Result $results */
$statement = $sql->prepareStatementForSqlObject($select);
$results = $statement->execute();
renderResults($results, $statement);
public function testBetweenCreatesBetweenPredicate()
{
$predicate = new Predicate();
$predicate->between('foo.bar', 1, 10);
$parts = $predicate->getExpressionData();
$this->assertEquals(1, count($parts));
$this->assertContains('%1$s BETWEEN %2$s AND %3$s', $parts[0]);
$this->assertContains(array('foo.bar', 1, 10), $parts[0]);
}
FROM Customer c
INNER JOIN Invoice i ON (i.CustomerId = c.CustomerId)
WHERE (i.Total BETWEEN :invoiceMin and :invoiceMax)
AND c.Country = :customerCountry
LIMIT 20", $queryValues
);*/
$sqliteFullNameExpression = new Expression('c.FirstName || " " || c.LastName');
$whereLastNameExpression = new Expression('lower(LastName) IN (?)', ['stevens', 'brooks', 'harris']);
$sql = new Sql($adapter['sqlite']);
/** @var Zend\Db\Sql\Select $select */
$select = $sql->select();
$select->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', 'Customer Full Name' => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total']);
$predicate = new Predicate();
$predicate->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax'])->equalTo('c.Country', $queryValues[':customerCountry']);
$predicate2 = new Predicate();
$predicate2->between('i.Total', $queryValues[':invoiceMin'], $queryValues[':invoiceMax'])->equalTo('c.Country', 'Chile');
$select->where($predicate);
$select2 = $sql->select();
$select2 = $select2->from(['c' => 'Customer'])->columns(['FirstName', 'LastName', 'Customer Full Name' => $sqliteFullNameExpression, 'Email', 'Country'])->join(['i' => 'Invoice'], 'i.CustomerId = c.CustomerId', ['InvoiceId', 'InvoiceDate', 'Total']);
$select2->where($predicate2);
$select->limit(20)->order(["Total DESC", "LastName ASC"]);
$select2->combine($select);
$statement = $sql->prepareStatementForSqlObject($select2);
echo SqlFormatter::format($statement->getSql());
exit;
/** @var \Zend\Db\Adapter\Driver\Pdo\Result $results */
$results = $statement->execute();
if ($results->count()) {
foreach ($results as $result) {
$headers = array_keys($result);
$outputData[] = $result;
