Zend Framework 2 database SQL examples

zf2-sql-examples.php

<?php

use Zend\Db\Sql\Expression;
use Zend\Db\Sql\Predicate\Operator;
use Zend\Db\Sql\Select;
use Zend\Db\Sql\Where;
use Zend\Db\TableGateway\TableGateway;

$adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
$table = new TableGateway('users', $adapter);

// SELECT `users`.* FROM `users`
$query = $table->select();

// SELECT `users`.`user_id` AS `user_id` FROM `users` WHERE `user_id` = 2
$query = $table->select(function(Select $select) {
    $select->columns(['user_id']);
    $select->where(['user_id' => 2]);
});

// SELECT MAX(`users`.`user_id`) FROM `users`
$query = $table->select(function(Select $select) {
    $select->columns([
        'MAX(?)' => [['users.user_id' => Expression::TYPE_IDENTIFIER]],
    ]);
});

// SELECT MAX(`user_id`) AS `max` FROM `users` WHERE `is_active` = 1
$query = $table->select(function(Select $select) {
    $select->columns([
        'max' => new Expression('MAX(?)', [['user_id' => Expression::TYPE_IDENTIFIER]]),
    ]);
    $select->where(['is_active' => 1]);
});

// SELECT `users`.*, `p`.* FROM `users` 
// INNER JOIN `projects` AS `p` ON `p`.`user_id` = `users`.`user_id`
// WHERE `users`.`is_active` = 1 
$query = $table->select(function(Select $select) {
    $select->join(['p' => 'projects'], 'p.user_id = users.user_id');
    $select->where(['users.is_active' => 1]);
});

// SELECT `users`.* FROM `users` WHERE `is_locked` = 0 AND `is_active` = 1 OR `role_id` = 1
$query = $table->select(function(Select $select) {
    $select->where(['is_locked' => 0, 'is_active' => 1], Where::COMBINED_BY_AND);
    $select->where(['role_id' => 1], Where::OP_OR);
});

// SELECT `users`.* FROM `users` WHERE `is_locked` = 0 AND (`role_id` = 1 OR `role_id` IN NULL)
$query = $table->where(function(Where $where) {
    $where->equalTo('is_locked', 0)
          ->AND
          ->nest()
              ->equalTo('role_id', 1)
              ->OR
              ->isNull('role_id')
          ->unnest();
});

// SELECT `users`.* FROM `users` 
// WHERE NOT EXISTS (SELECT `admins`.* FROM `admins` WHERE `admins`.`id` = `users`.`id`)
// ORDER BY RAND()
$query = $table->select(function(Select $select) {
    $subquery = new Select('admins');
    $subquery->where([
        new Operator(
            'er2.id_entity', 
            Operator::OP_EQ, 
            'er1.id_entity_related', 
            Operator::TYPE_IDENTIFIER,
            Operator::TYPE_IDENTIFIER
        ),
        'admins.id' => 'users.id',
    ]);
    $select->where([
        'NOT EXISTS(?)' => $subquery,
    ]);
    $select->order(new Expression('RAND()'));
});

// SELECT `users`.`id_user` AS `id` FROM `users`
// WHERE (BINARY LOWER(`users`.`login`) = LOWER('test') OR BINARY LOWER(`users`.`email`) = LOWER('test'))
//   AND (`users`.`id_site` IS NULL OR `users`.`id_site` = 1) AND `users`.`status` = 1
// LIMIT 1
$query = $table->select()->where(function(Where $where) {
    $where = $where->NEST;
    foreach (['login', 'email'] as $column) {
        $predicate = new Expression('BINARY LOWER(?) = LOWER(?)', [
            [$column => Expression::TYPE_IDENTIFIER],
            ['test'  => Expression::TYPE_VALUE],
        ]);
        $where->orPredicate($predicate);
    }
    $where = $where->UNNEST;
    $where = $where->AND->NEST->isNull('id_site')->OR->equalTo('id_site', 1)->UNNEST->AND->equalTo('status', 1);
})->limit(1);

echo $query->getSqlString($adapter->getPlatform());