Zend Framework 2.0 (ZF2) Advanced database operations

Zf2 completely rewritten the ZEND1 database components, but the manuals give examples that are so weak that they can only organize some zend\db\tablegateway use cases as follows:

The preferred operation DB approach is to inherit tablegateway, for example, now there is a posts table that needs to be manipulated to create a new class as follows

Class Posts extends Zend\db\tablegateway\tablegateway

{

}

Instantiating this class and passing in the Zend\db\adapter\adapter to connect to the database is not the focus here, assuming that the instantiated Tablegateway is $posttable, we need to do this posts table:

WHERE Chain Operations

ZF2 recommended Query mode is chained operation

$select = $postTable->getsql ()->select ();

$select->where (' ID > 5 ')->order (' id DESC ')->limit (10);

$resultSet = $postTable->selectwith ($select);

$result = $resultSet->toarray ();

WHERE closure Operation

ZF2 also supports closed-package operations, which can be rewritten as closures in the following ways:

$select = $postTable->getsql ()->select ();

$select->where (function ($where) {

$where->lessthan (' id ', 10);

$where->greaterthan (' id ', 5);

return $where;

})->order (' id DESC ')->limit (10);

WHERE and compound conditions

And when the where condition is a compound condition, you can write this:

$select->where (

Array (' ID > 30 ')

)->where (

Array (' ID < 10 ')

);

The following SQL will be generated

Select "Posts". * from "posts" WHERE ID > ID < 10;

WHERE OR Compound condition

If you want to change the default link for a where query to and, you can write this:

$select->where (

Array (' ID > 30 ')

)->where (

Array (' ID < ten '), \zend\db\sql\where::op_or

);

Sql:

Select "Posts". * from "posts" WHERE ID > ID < 10;

You can also write this, the effect is the same. This is accomplished by magic Method __call ().

$where = $select->where;

$where->lessthan (' id ', 10);

$where->or;

$where->greaterthan (' id ', 30);

WHERE Complex conditions

If the query condition is further complicated, the closure operation is more flexible than chain operations, such as:

$select->where (function ($where) {

$subWhereForId = Clone $where;

$subWhereForTitle = Clone $where;

$subWhereForId->lessthan (' id ', 10);

$subWhereForId->or;

$subWhereForId->greaterthan (' id ', 20);

$where->addpredicate ($subWhereForId);

$subWhereForTitle->equalto (' title ', ' A ');

$subWhereForTitle->or;

$subWhereForTitle->equalto (' title ', ' B ');

$where->addpredicate ($subWhereForTitle);

return $where;

});

Equivalent to SQL

Select "Posts". * from "Posts" WHERE ("id" < ' or "id" > ") and (" title "= ' A ' or" title "= ' B ');

Improvement of Evaengine

In Evaengine, you can use a complete chain operation, and the first example can be written in Evaengine:

$result = $postTable->where (' ID > 5 ')->order (' id DESC ')->limit ()->find ();

Original address: Http://avnpc.com/pages/advanced-database-select-usage-in-zf2