Join a subquery using Zend Framework 2 TableGateway

Question

I am trying to execute a query with Zend Framework 2 where I have a SELECT inside a JOIN statement. So far, what I tried, but putting the SELECT object in the first join () parameter does not seem to work. I resorted to this approach, since I need to order the results first before doing any groupings. Any ideas on how to make it work?

public function getSearchKeyword($keyword, $limit) { $select = $this->keywords->getSql()->select(); $subquery = $this->pages->getSql()->select(); $subWhere = new \Zend\Db\Sql\Where(); $subWhere->equalTo('delete_flag', 'n') ->equalTo('published_flag', 'y'); $subquery->where($subWhere); $where = new \Zend\Db\Sql\Where(); $where->like('keyword', '%' . $keyword . '%') ->equalTo('delete_flag', 'n'); $select->columns(array('display' => 'keyword', 'url')) ->join(array('sub' => $subquery), 'sub.page_id = keywords.page_id', array()) ->where($where) ->group(array('keywords.page_id', 'keywords.keyword')) ->order(array('rank', 'keyword')) ->limit($limit); $row = $this->tableGateway->selectWith($select); return $row; } 

The query I'm trying to write is below:

 SELECT keywords.keyword AS display, keywords.url FROM keywords INNER JOIN ( SELECT * FROM pages WHERE published_flag = 'y' AND delete_flag = 'n' ORDER BY page_id DESC ) pages ON pages.page_id = keywords.page_id WHERE published_flag = 'y' AND delete_flag = 'n' AND keywords.keyword LIKE '%?%' GROUP BY display, page_id; 

Answer 1

I worked on the same problem and did not find a standard way to solve it. So I got a working, but not standard zf2

• Create a small interface for managing Db connections
• Implements it as a small class to get a PDO connection object for your database
• execute arbitrary requests

Code example

 // Filename: /module/MyTools/src/MyTools/Service/DbModelServiceInterface.php namespace MyTools\Service; interface DbModelServiceInterface { /** * Will return the result of querying the curret database * * @param type $query * @result mixed */ public function dbQuery($query); /** * Will return a connection object that links to curret database * * @result mixed */ public function getConnection(); } 

The class that implements the interface. It creates and offers a PDO connection. Note It needs additional code to close conns and refine security ... It checks and is fully functional. the code:

 // Filename: /module/MyTools/src/MyTools/Service/DbModelServiceMySql.php namespace MyTools\Service; use MyTools\Service\DbModelServiceInterface; use PDO; class DbModelServiceMySql implements DbModelServiceInterface { protected $driverConfig; protected $connection; protected $isconnected = FALSE; protected $dbname = ''; /** * Creates a connection to main database */ public function __construct() { $driverConfig = self::getDriverDef(); $this->driverConfig = $driverConfig; // new PDO($driverConfig['dsn'], $driverConfig['username'], $driverConfig['password']); $this->_connect(); } protected function _connect(){ $dsn = (isset($this->driverConfig['dsn'])) ? $this->driverConfig['dsn'] : ''; $username = (isset($this->driverConfig['username'])) ? $this->driverConfig['username'] : ''; $password = (isset($this->driverConfig['password'])) ? $this->driverConfig['password'] : ''; if( ($dsn) && ($username) && ($password)){ $options = [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', ]; try { $this->connection = new PDO($dsn, $username, $password, $options); $this->isconnected = TRUE; $this->_setdbname($dsn); } catch (Exception $ex) { throw new RuntimeException('YOUR ERROR MESSAGE.'); } } return $this->isconnected; } protected function _setdbname($dsn){ if($dsn){ $chunks = explode(';', ''.$dsn); foreach($chunks as $chunk){ if(strpos('***'.$chunk, 'dbname') > 2){ $nombre = explode('=', $chunk); $this->dbname = $nombre[1]; break; } } } } /** * {@inheritDoc} */ public function dbQuery($query) { if($this->connection){ $resultset = $this->connection->query($query); if($resultset){ return $resultset->fetchAll(PDO::FETCH_ASSOC); }else{ return ['Error' => 'YOUR CUSTOM ERROR MESSAGE.']; } }else{ return ['Error' => 'OTHER CUSTOM ERROR MESSAGE']; } } public static function getDriverDef() { $autoloadDir = __DIR__ . '../../../../../../config/autoload/'; $credentialsdb = include $autoloadDir . 'local.php'; $globaldb = include $autoloadDir . 'global.php'; $def = (isset($globaldb['db'])) ? $globaldb['db'] : array(); $credentials = (isset($credentialsdb['db'])) ? $credentialsdb['db'] : $credentialsdb; return array_merge($def, $credentials); } /** * {@inheritDoc} */ public function getConnection() { if($this->connection){ return $this->connection; }else{ return 'Error: YOUR CUSTOM ERROR MESSAGE'; } } /** * {@inheritDoc} */ public function getDbName(){ return $this->dbname; } } 

You now have a class that you can create elsewhere to fulfill the queries you need.

usage: code:

 $myQuery = 'the very very complex query you need to execute' $myDbConn = new MyTools\Service\DbModelServiceMySql(); $result = $myDbConn->dbQuery($myQuery); 

If you succeed, you get the resulting array of pairs columnName => value

Answer 2

You can try this.

  $select->columns(array('display' => 'keyword', 'url')) ->join(array('sub' => 'pages'), 'sub.page_id = keywords.page_id', array(), $select::JOIN_INNER) ->where($where) ->group(array('keywords.page_id', 'keywords.keyword')) ->order(array('rank', 'keyword')) ->limit($limit); 

Answer 3

In your code, you get all the keywords that page_id is in sub page_id, where delete_flag = 'n' and published_flag = 'y'.

 join(..., 'sub.page_id = keywords.page_id', array()) 

If you don't need page table columns, you can use IN instead of JOIN.
For example, when you need to know what keywords are, what pages you have to use the JOIN, but when you need to know what are the keyboard on any page, you can use the IN instruction. Anyway:
There is no standard way in ZF2, but you can try the following code.

 public function getSearchKeyword($keyword, $limit) { $select = $this->keywords->getSql()->select(); $subquery = $this->pages->getSql()->select(); $subWhere = new \Zend\Db\Sql\Where(); $subWhere->equalTo('delete_flag', 'n') ->equalTo('published_flag', 'y'); $subquery->columns(array('page_id')) ->where($subWhere); $where = new \Zend\Db\Sql\Where(); $where->like('keyword', '%' . $keyword . '%') ->equalTo('delete_flag', 'n') ->in('keywords.page_id', $subquery); $select->columns(array('display' => 'keyword', 'url')) ->where($where) ->group(array('keywords.page_id', 'keywords.keyword')) ->order(array('rank', 'keyword')) ->limit($limit); $row = $this->tableGateway->selectWith($select); return $row; } 

Answer 4

I ran into a similar problem. Since FROM table and Subquery FROM table were different, I got an error. My workaround was to extract the SQL and create the statement.

  $sql = $select->getSqlString(new \Zend\Db\Adapter\Platform\Mysql()); $stmt = $this->getAdapter()->createStatement($sql); $stmt->prepare($sql); $result = $stmt->execute(); $resultSet = new ResultSet(); \\ Class Zend\Db\ResultSet\ResultSet $resultSet->initialize($result);