MySql query string to sql object

Question

Using Zend Framework 2 is possible to get a sql string from a tablegateway object, like this:

public function test(){
   $sql = $this->tableGateway->getSql();
   $select = $sql->select();
   $select->where(array('title' => 'abracadabra'));
   $select->where
     ->NEST->
           equalTo('id', 1)
               ->OR->
           equalTo('artist', 'Tony')
     ->UNNEST;

   $select->limit(1);

   return $this->tableGateway->selectWith($select);
}

//code ignored here//

echo $sql->getSqlstringForSqlObject($select); 

I was wondering if it could be possible to do the opposite, and basically set a query string and then get back a tableGateway object, the reason for this is that sometimes is needed to set a where clause dynamically, without having to know where to place/append it, example:

On a single query:

$sql = "select * from tbl_test";

if($isDynamic)
    $sql .= ' where test_id=1';

But on more complex query:

$sql = "select * from tbl_test inner join (select * from tbl_test 1) x on 1=1 where test_id is not null group by test_id";

//OF COURSE IT WILL FAIL.
if($isDynamic)
    $sql .= ' where test_id=1';

Using zf2 I could potentially do something like

• Setting the query string to a sql object.
• Then simply do $obj->where(array('test_id'=>1));

Answer 1

To solve this problem I overloaded method Zend\Db\Sql\Select.

For your example of a complex query, I proceed as follows:

Your query : select * from tbl_test inner join (select * from tbl_test 1) x on 1=1 where test_id is not null group by test_id
My query : SELECT * FROM (select * from tbl_test inner join (select * from tbl_test 1) x on 1=1 where test_id is not null group by test_id) tmp WHERE test_id=1;

If your complex query use alias tmp then change in the method renderTable(). Here is how my class is used (change the namespace as need):

use SbmPdf\Model\Db\Sql\Select;
$sql = "select * from tbl_test inner join (select * from tbl_test 1) x on 1=1 where test_id is not null group by test_id";
$select = new Select($sql);
$select->where(array('id' => 1));

Here is my class (change the namespace as need):

/**
 * Surcharge de la classe Zend\Db\Sql\Select pour exécuter une requête complexe basée sur une chaine SQL
 *
 * On a ajouté la méthode setRecordSource qui permet de passer une chaine SQL complète comme source de donnée.
 * 
 * Lorsque la chaine SQL recordSource est donnée (constructeur ou setter), elle remplace la table indiquée dans FROM. 
 * En définitive, la méthode from() sera ignorée si la méthode recordSource() est appelée.
 * Par contre, si on n'appelle pas cette nouvelle méthode, la classe aura un comportement normal (à condition de ne
 * rien passer au constructeur).
 * 
 * Ensuite, pour exécuter la requête, il faut pratiquer de la façon suivante :
 *   $sqlString = $select->getSqlString($dbAdapter->getPlatform());
 *   $rowset = $dbAdapter->query($sqlString, \Zend\Db\Adapter\Adapter::QUERY_MODE_EXECUTE);
 * 
 * @project sbm
 * @package SbmPdf/Model/Db/Sql
 * @filesource Select.php
 * @encodage UTF-8
 * @author DAFAP Informatique - Alain Pomirol ([e-mail])
 * @date 19 août 2015
 * @version 2015-1
 */
namespace SbmPdf\Model\Db\Sql;

use Zend\Db\Adapter\Driver\DriverInterface;
use Zend\Db\Adapter\ParameterContainer;
use Zend\Db\Adapter\Platform\PlatformInterface;
use Zend\Db\Sql\Select as ZendSelect;

class Select extends ZendSelect
{

  private $recordSource;

  /**
   * On passe le recordSource (sql string) par le constructeur. On ne peut plus passer la table.
   *
   * @param string $recordSource            
   */
  public function __construct($recordSource = null)
  {
    parent::__construct();
    $this->recordSource = $recordSource;
  }

  /**
   * On peut aussi le passer par le setter
   *
   * @param string $recordSource            
   * @return \SbmPdf\Model\Db\Sql\Select
   */
  public function setRecordSource($recordSource)
  {
    $this->recordSource = $recordSource;
    return $this;
  }

  /**
   * Surcharge de la méthode
   *
   * (non-PHPdoc)
   * 
   * @see \Zend\Db\Sql\Select::processSelect()
   */
  protected function processSelect(PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
  {
    $expr = 1;
    if (empty($this->recordSource)) {
        list ($table, $fromTable) = parent::resolveTable($this->table, $platform, $driver, $parameterContainer);
    } else {
        list ($table, $fromTable) = $this->resolveTable($this->recordSource, $platform);
    }
    // process table columns
    $columns = array();
    foreach ($this->columns as $columnIndexOrAs => $column) {
        if ($column === self::SQL_STAR) {
            $columns[] = array(
                $fromTable . self::SQL_STAR
            );
            continue;
        }

        $columnName = $this->resolveColumnValue(array(
            'column' => $column,
            'fromTable' => $fromTable,
            'isIdentifier' => true
        ), $platform, $driver, $parameterContainer, (is_string($columnIndexOrAs) ? $columnIndexOrAs : 'column'));
        // process As portion
        if (is_string($columnIndexOrAs)) {
            $columnAs = $platform->quoteIdentifier($columnIndexOrAs);
        } elseif (stripos($columnName, ' as ') === false) {
            $columnAs = (is_string($column)) ? $platform->quoteIdentifier($column) : 'Expression' . $expr ++;
        }
        $columns[] = (isset($columnAs)) ? array(
            $columnName,
            $columnAs
        ) : array(
            $columnName
        );
      }

      // process join columns
      foreach ($this->joins as $join) {
        $joinName = (is_array($join['name'])) ? key($join['name']) : $join['name'];
        $joinName = parent::resolveTable($joinName, $platform, $driver, $parameterContainer);

        foreach ($join['columns'] as $jKey => $jColumn) {
            $jColumns = array();
            $jFromTable = is_scalar($jColumn) ? $joinName . $platform->getIdentifierSeparator() : '';
            $jColumns[] = $this->resolveColumnValue(array(
                'column' => $jColumn,
                'fromTable' => $jFromTable,
                'isIdentifier' => true
            ), $platform, $driver, $parameterContainer, (is_string($jKey) ? $jKey : 'column'));
            if (is_string($jKey)) {
                $jColumns[] = $platform->quoteIdentifier($jKey);
            } elseif ($jColumn !== self::SQL_STAR) {
                $jColumns[] = $platform->quoteIdentifier($jColumn);
            }
            $columns[] = $jColumns;
        }
    }

    if ($this->quantifier) {
        $quantifier = ($this->quantifier instanceof ExpressionInterface) ? $this->processExpression($this->quantifier, $platform, $driver, $parameterContainer, 'quantifier') : $this->quantifier;
    }

    if (! isset($table)) {
        return array(
            $columns
        );
    } elseif (isset($quantifier)) {
        return array(
            $quantifier,
            $columns,
            $table
        );
    } else {
        return array(
            $columns,
            $table
        );
    }
  }

  protected function resolveTable($table, PlatformInterface $platform, DriverInterface $driver = null, ParameterContainer $parameterContainer = null)
  {
    $alias = null;

    if (is_array($table)) {
        $alias = key($table);
        $table = current($table);
    } else {
        $alias = 'tmp';
    }
    $fromTable = $platform->quoteIdentifier($alias);
    $table = $this->renderTable($table, $fromTable, false);
    if ($alias) {} else {
        $fromTable = $table;
    }

    if ($this->prefixColumnsWithTable && $fromTable) {
        $fromTable .= $platform->getIdentifierSeparator();
    } else {
        $fromTable = '';
    }

    return array(
        $table,
        $fromTable
    );
  }

  protected function renderTable($table, $alias = null, $parent = true)
  {
    if ($parent) {
        return parent::renderTable($table, $alias);
    } else {
        if (empty($alias)) {
            $alias = 'tmp';
        }
        return "($table) AS $alias";
    }
  }
}

Answer 2

You can use directly where clause with your cording.

public function test(){ 
$sql = $this->tableGateway->getSql();
$select = $sql->select();
$select->where(array('title' => 'abracadabra'));
$select->where
 ->NEST->
       equalTo('id', 1)
           ->OR->
       equalTo('artist', 'Tony')
 ->UNNEST;

 if($isDynamic){
    $select->where(array('test_id' => '1'));
 }

 $select->limit(1);

 return $this->tableGateway->selectWith($select);
 }

You can use SQL like this