Zend Framework 2 Advanced database operations ?

Last updated on December 21, 2022

In this post, I would like to list basic to advanced database queries using ZF2. I am gonna use TableGateway methods here.

Simple Select Query

// use Zend\Db\TableGateway\TableGateway;
// use Zend\Db\Sql\Select;
$adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
$projectTable = new TableGateway('users', $adapter);
$rowset = $projectTable->select(); 
print_r($rowset->toArray());
exit;

SQL statement is

SELECT `users`.* FROM `users`

Select Query with Where condition

// use Zend\Db\TableGateway\TableGateway;
// use Zend\Db\Sql\Select;
public function indexAction() {
   $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
   $projectTable = new TableGateway('usersx', $adapter);
   $rowset = $projectTable->select(function(Select $select) {
            $select->columns(array('user_id')); 
   });
    print_r($rowset->toArray());
    exit;
}

SQL statement is

SELECT `usersx`.`user_id` AS `user_id` FROM `usersx`

Select Query with Where condition

// use Zend\Db\TableGateway\TableGateway;
// use Zend\Db\Sql\Select;
public function indexAction() {
    $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
    $projectTable = new TableGateway('usersx', $adapter);
    $rowset = $projectTable->select(function(Select $select) {
            $select->where(array('user_id' => 2)); 
    });
    print_r($rowset->toArray());
     exit;
}

SQL statement is

SELECT `usersx`.* FROM `usersx` WHERE `user_id` = 2

SELECT MAX(field)

// use Zend\Db\TableGateway\TableGateway;
// use Zend\Db\Sql\Select;
// use Zend\Db\Sql\Expression;
public function indexAction() {
     $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
     $projectTable = new TableGateway('usersx', $adapter);
     $rowset = $projectTable->select(function(Select $select) {
         $select->columns(array(
           'maxUserId' => new Expression('MAX(user_id)')
         ));
        $select->where(array('is_active' => 1));
    });
    print_r($rowset->toArray());
    exit; 
}

SQL statement is

SELECT MAX(user_id) AS `maxUserId` FROM `usersx` WHERE `is_active` = :where1

SELECT MIN(field)

//use Zend\Db\TableGateway\TableGateway;
//use Zend\Db\Sql\Select;
//use Zend\Db\Sql\Expression;
public function indexAction() {
     $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
     $projectTable = new TableGateway('usersx', $adapter);
     $rowset = $projectTable->select(function(Select $select) {
         $select->columns(array(
         'minUserId' => new Expression('MIN(user_id)')
       ));
      $select->where(array('is_active' => 1));
   });
   print_r($rowset->toArray());
   exit;
}

SQL statement is

SELECT MIN(user_id) AS `minUserId` FROM `usersx` WHERE `is_active` = 1

SELECT AVG(field)

//use Zend\Db\TableGateway\TableGateway;
//use Zend\Db\Sql\Select;
//use Zend\Db\Sql\Expression;
public function indexAction() {
    $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
    $projectTable = new TableGateway('usersx', $adapter);
    $rowset = $projectTable->select(function(Select $select) {
        $select->columns(array(
            'avg' => new Expression('AVG(user_id)')
        ));
        $select->where(array('is_active' => 1));
    });
    print_r($rowset->toArray());
    exit;
}

SQL statement is

SELECT AVG(user_id) AS `avg` FROM `usersx` WHERE `is_active` = 1

SELECT SUM(field)

    //use Zend\Db\TableGateway\TableGateway;
    //use Zend\Db\Sql\Select;
    //use Zend\Db\Sql\Expression;
    public function indexAction() {
        $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $projectTable = new TableGateway('usersx', $adapter);
        $rowset = $projectTable->select(function(Select $select) {
            $select->columns(array(
             'sum' => new Expression('SUM(user_id)')
            ));
            $select->where(array('is_active' => 1));
        });
        print_r($rowset->toArray());
        exit;
    }

SQL statement is

SELECT SUM(user_id) AS `sum` FROM `usersx` WHERE `is_active` = 1

Inner Join with where condition

    //use Zend\Db\TableGateway\TableGateway;
    //use Zend\Db\Sql\Select;
    public function indexAction() {
        $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $projectTable = new TableGateway('users', $adapter);
        $rowset = $projectTable->select(function(Select $select) {
            $select->join(array('p' => 'projects'),"p.user_id = users.user_id");
            $select->where(array('users.is_active' => 1));
        });
        print_r($rowset->toArray());
        exit;
    }

SQL statement is

SELECT `users`.*, `p`.* FROM `users` INNER JOIN `projects` AS `p` ON `p`.`user_id` = `users`.`user_id` WHERE `users`.`is_active` = 1

Left join with where condition

    //use Zend\Db\TableGateway\TableGateway;
    //use Zend\Db\Sql\Select;
    public function indexAction() {
        $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $projectTable = new TableGateway('users', $adapter);
        $rowset = $projectTable->select(function(Select $select) {
            $select->join(array('p' => 'projects'),"p.user_id = users.user_id",array('*'),'left');
            $select->where(array('users.is_active' => 1));
        });
        print_r($rowset->toArray());
        exit; 
    }

SQL statement is

SELECT `users`.*, `p`.* FROM `users` LEFT JOIN `projects` AS `p` ON `p`.`user_id` = `users`.`user_id` WHERE `users`.`is_active` = 1

Where – OR

    //use Zend\Db\TableGateway\TableGateway;
    //use Zend\Db\Sql\Select;
    public function indexAction() {
        $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $projectTable = new TableGateway('users', $adapter);
        $rowset = $projectTable->select(function(Select $select) {
            $select->where(array('is_locked' => 0));
            $select->where(array('is_active' => 1),\Zend\Db\Sql\Where::OP_OR);
        });
        print_r($rowset->toArray());
        exit;
    }

SQL statement is

SELECT `users`.* FROM `users` WHERE `is_locked` = 0 OR `is_active` = 1

Where – AND

    //use Zend\Db\TableGateway\TableGateway;
    //use Zend\Db\Sql\Select;
    public function indexAction() {
        $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $projectTable = new TableGateway('users', $adapter);
        $rowset = $projectTable->select(function(Select $select) {
            $select->where(array('is_locked' => 0));
            $select->where(array('is_active' => 1),\Zend\Db\Sql\Where::OP_AND);
        });
        print_r($rowset->toArray());
        exit;
    }

SQL statement is

SELECT `users`.* FROM `users` WHERE `is_locked` = 0 AND `is_active` = 1

Where -Combined And – OR

    //use Zend\Db\TableGateway\TableGateway;
    //use Zend\Db\Sql\Select;
    public function indexAction() {
        $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $projectTable = new TableGateway('users', $adapter);
        $rowset = $projectTable->select(function(Select $select) {
            $select->where(array('is_locked' => 0,'is_active' => 1),\Zend\Db\Sql\Where::COMBINED_BY_AND);
            $select->where(array('role_id' => 1),\Zend\Db\Sql\Where::OP_OR);
        });
        print_r($rowset->toArray());
        exit; 
    }

SQL statement is

SELECT `users`.* FROM `users` WHERE `is_locked` = 0 AND `is_active` = 1 OR `role_id` = 1

Where Combined OR – AND

    //use Zend\Db\TableGateway\TableGateway;
    //use Zend\Db\Sql\Select;
    public function indexAction() {
        $adapter = $this->getServiceLocator()->get('Zend\Db\Adapter\Adapter');
        $projectTable = new TableGateway('users', $adapter);
        $rowset = $projectTable->select(function(Select $select) {
            $select->where(array('is_locked' => 0,'is_active' => 1),\Zend\Db\Sql\Where::COMBINED_BY_OR);
            $select->where(array('role_id' => 1),\Zend\Db\Sql\Where::OP_AND);
        });
        print_r($rowset->toArray());
        exit;
    }

SQL statement is

SELECT `users`.* FROM `users` WHERE `is_locked` = 0 OR `is_active` = 1 AND `role_id` = 1 

That’s it.