<?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;