JOIN on a subquery with Zend Framework 2 TableGateway

Question

I'm trying to do a query with Zend Framework 2 where I have a SELECT inside a JOIN statement. So far, here's what I've tried, but injecting the SELECT object into the first parameter of join() doesn't seem to be working. I've resorted to such an approach since I need to order the results first before doing any grouping. Any ideas on how to get it working?

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 was working around the same problem and did not found a standard way to solve it. So I got a working but not zf2 standard one

1. Create a small interface to mannage Db conections
2. Implements it as a small class to get a connection PDO object to your database
3. execute your arbitrary querys

Code sample

// 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 implementing the interface. It creates and offers a PDO connection. Note: It needs extra code to close conns and to perfeorm security adm... It test it and is completely functional. 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;
    }
}

Now you have a class you can instantiate elsewhere to perform the querys you need.

use: code:

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

If success you got a resulset array of pairs columnName => value

Answer 2

I've faced a similar issue. Since the FROM table and Subquery's FROM table were different i got an error. My workaround was to extract the SQL and create a 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);

Answer 3

In your code, you are getting all keywords which page_id's is in sub page_id where delete_flag = 'n' and published_flag = 'y'.

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

When you don't need any columns of pages table, you can use IN instead of JOIN.
For example when you need to know which keywords are in which pages, you should use JOIN, but when you need to know which keyboards are in any pages, you can use IN statement.
Anyway :
There is no standard way in ZF2 but you can try 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

You can try this one.

  $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);