Adding SELECT options to Zend_Db_Select

[Hector Virgen]

Is there a way to add SELECT options to a Zend_Db_Select object? For example, I want to be able to produce the following SELECT statement:

SELECT SQL_CALC_FOUND_ROWS * FROM table LIMIT 10;

More generally, I need a way to add options between the "SELECT" and the column list. I know there is a built-in method to add "DISTINCT" but MySQL also supports many other modifiers such as SQL_NO_CACHE, SQL_BIG_RESULT, HIGH_PRIORITY, etc.

http://dev.mysql.com/doc/refman/5.0/en/select.html

I'm almost certain that all of the options are not supported by all of the various database engines, so if there was a method that allowed you to pass in your options by argument it could come in very handy. For example:

$select = $table->select()->addOption('SQL_NO_CACHE')->addOption('HIGH_PRIORITY')->from(......

Technically, this would also allow "DISTINCT" to be added using addOption().

I've considered subclassing Zend_Db_Select to handle this, but I would have to redefine __toString() to support this.

-Hector

[Bill Karwin]

Hector Virgen wrote:

I want to be able to produce the following SELECT statement:

SELECT *SQL_CALC_FOUND_ROWS* * FROM table LIMIT 10;

I've considered subclassing Zend_Db_Select to handle this, but I would
have to redefine __toString() to support this.

SQL_CALC_FOUND_ROWS is a MySQL proprietary thing, and adding it to Zend_Db_Select would break on other RDBMS brands.  In general, Zend_Db_Select supports only ANSI SQL syntax (except for the limit() functionality).

Take a closer look at Zend_Db_Select.  It has been refactored in recent months to make it easier to do what you're describing in a subclass.  You shouldn't have to change __toString(), just do this:
- add a key to the $_partsInit array for the option you want; it probably belongs before DISTINCT
- add functions  sqlCalcFoundRows() and _renderSqlCalcFoundRows()

See functions distinct() and _renderDistinct() as examples.

Regards,
Bill Karwin

[Hector Virgen]

Thanks, Bill. So in order to fully support MySQL I suppose I'd have to write a total of 20 new methods that all basically do the same thing (they each add an option after the "SELECT" portion of the statement)? That's 2 each for:

ALL, DISTINCTROW, HIGH_PRIORITY, STRAIGHT_JOIN, SQL_SMALL_RESULT, SQL_BIG_RESULT, SQL_BUFFER_RESULT, SQL_CACHE, SQL_NO_CACHE, SQL_CALC_FOUND_ROWS

Also, subclassing Zend_Select introduces other problems. For example, when I use $table->select(), it internally creates an instance of Zend_Db_Table_Select, which extends Zend_Db_Select, therefore not using my subclass My_Db_Select.

-Hector

Bill Karwin wrote:

Hector Virgen wrote:
  

I want to be able to produce the following SELECT statement:

SELECT *SQL_CALC_FOUND_ROWS* * FROM table LIMIT 10;

I've considered subclassing Zend_Db_Select to handle this, but I would 
have to redefine __toString() to support this.

    

SQL_CALC_FOUND_ROWS is a MySQL proprietary thing, and adding it to
Zend_Db_Select would break on other RDBMS brands.  In general,
Zend_Db_Select supports only ANSI SQL syntax (except for the limit()
functionality).

Take a closer look at Zend_Db_Select.  It has been refactored in recent
months to make it easier to do what you're describing in a subclass.  You
shouldn't have to change __toString(), just do this:
- add a key to the $_partsInit array for the option you want; it probably
belongs before DISTINCT
- add functions  sqlCalcFoundRows() and _renderSqlCalcFoundRows()

See functions distinct() and _renderDistinct() as examples.

Regards,
Bill Karwin
  

[Bill Karwin]

Hector Virgen wrote:

Thanks, Bill. So in order to fully support MySQL I suppose I'd have to
write a total of 20 new methods that all basically do the same thing
(they each add an option after the "SELECT" portion of the statement)?

Right, but I'd be shocked and amazed if you actually use more than two of those SELECT options in your entire life.

Hector Virgen wrote:

Also, subclassing Zend_Select introduces other problems. For example,
when I use $table->select(), it internally creates an instance of
Zend_Db_Table_Select, which extends Zend_Db_Select, therefore not using
my subclass My_Db_Select.

Yep, that's a problem.  You may not be able to use the extra SQL options in your Zend_Db_Select_Mysql class when using the $table->select() method.  But standard use of $table->select(), without the extra options, will still work of course.

You could copy Zend_Db_Table_Select and create a new class that extends your enhanced Select class.  That's up to you, if you think you really need those MySQL options when using the Table interface.  If it were me, I'd exercise the better part of valor, forget about SQL_CALC_FOUND_ROWS, and just use a "SELECT COUNT(*)" query instead.

Regards,
Bill Karwin

[Hector Virgen]

I've tried implementing your solution but I'm having trouble with the late static binding of the array Zend_Db_Select::$_partsInit. I don't think there's a way around this issue without upgrading to PHP 5.3, but I may be wrong. This is why I mentioned I would have to redefine __toString() -- I'd have to do a substr_replace() on parent::__toString() to insert SQL_CALC_FOUND_ROWS into the SELECT query.

And, yes, I actually use the extra options in my select queries. I prefer SQL_CALC_FOUND_ROWS when I'm building complex queries that use subqueries and "having" clauses, which would be a waste of cpu cycles to repeat the same complex query twice just for the total count. MySQL will recognize that the query is different and repeat all of the joins and lookups all over again.

And there are a lot of benefits from properly using SQL_CACHE / SQL_NO_CACHE / HIGH_PRIORITY.

To be denied access to these options just because database X doesn't support it doesn't quite seem right to me.

-Hector

Bill Karwin wrote:

Hector Virgen wrote:
  

Thanks, Bill. So in order to fully support MySQL I suppose I'd have to 
write a total of 20 new methods that all basically do the same thing 
(they each add an option after the "SELECT" portion of the statement)? 

    

Right, but I'd be shocked and amazed if you actually use more than two of
those SELECT options in your entire life.


Hector Virgen wrote:
  

Also, subclassing Zend_Select introduces other problems. For example, 
when I use $table->select(), it internally creates an instance of 
Zend_Db_Table_Select, which extends Zend_Db_Select, therefore not using 
my subclass My_Db_Select.

    

Yep, that's a problem.  You may not be able to use the extra SQL options in
your Zend_Db_Select_Mysql class when using the $table->select() method.  But
standard use of $table->select(), without the extra options, will still work
of course.

You could copy Zend_Db_Table_Select and create a new class that extends your
enhanced Select class.  That's up to you, if you think you really need those
MySQL options when using the Table interface.  If it were me, I'd exercise
the better part of valor, forget about SQL_CALC_FOUND_ROWS, and just use a
"SELECT COUNT(*)" query instead.

Regards,
Bill Karwin
  

[Bill Karwin]

Hector Virgen wrote:

I've tried implementing your solution but I'm having trouble with the
late static binding of the array Zend_Db_Select::$_partsInit.

<?php

require_once 'Zend/Db/Adapter/Abstract.php';
require_once 'Zend/Db/Select.php';

class Zend_Db_Select_Mysql extends Zend_Db_Select
{

    const SQL_CALC_FOUND_ROWS = 'sqlCalcFoundRows';
    // add other options as needed

    public function __construct(Zend_Db_Adapter_Abstract $adapter)
    {
        /**
         * Use array_merge() instead of simply setting a key
         * because the order of keys is significant to the
         * rendering of the query.
         */
        self::$_partsInit = array_merge(
            array(
                self::SQL_CALC_FOUND_ROWS => false
                // add other options as needed
            ),
            self::$_partsInit
        );
        parent::__construct($adapter);
    }

    public function sqlCalcFoundRows($flag = true)
    {
        $this->_parts[self::SQL_CALC_FOUND_ROWS] = (bool) $flag;
        return $this;
    }

    protected function _renderSqlCalcFoundRows($sql)
    {
        if ($this->_parts[self::SQL_CALC_FOUND_ROWS]) {
            $sql .= ' SQL_CALC_FOUND_ROWS';
        }

        return $sql;
    }

}


Quick & dirty test code:

$db = Zend_Db::factory('mysqli', array('dbname'=>'test', 'username'=>'root', 'password'=>'xxxx'));

$select = new Zend_Db_Select_Mysql($db);
$select->from('foo');
$select->sqlCalcFoundRows();

print "SQL = " . $select . "\n";

Hector Virgen wrote:

And, yes, I actually use the extra options in my select queries.

I'm shocked and amazed!  (see I told you I would be :-)


Regards,
Bill Karwin

[Hector Virgen]

Thanks, Bill! I never thought to override the constructor :)

However this still leaves the problem with using $table->select(), which I use more often the Zend_Db::select(). I'm a little hesitant to copy/paste the entire Zend_Db_Table_Select class, so for now I am using substr_replace within __toString() to make it work.

Thanks for the help! :)

-Hector

Bill Karwin wrote:

Hector Virgen wrote:
  

I've tried implementing your solution but I'm having trouble with the 
late static binding of the array Zend_Db_Select::$_partsInit. 

    

<?php

require_once 'Zend/Db/Adapter/Abstract.php';
require_once 'Zend/Db/Select.php';

class Zend_Db_Select_Mysql extends Zend_Db_Select
{

    const SQL_CALC_FOUND_ROWS = 'sqlCalcFoundRows';
    // add other options as needed

    public function __construct(Zend_Db_Adapter_Abstract $adapter)
    {
	/**
	 * Use array_merge() instead of simply setting a key
	 * because the order of keys is significant to the
	 * rendering of the query.
	 */
	self::$_partsInit = array_merge(
	    array(
		self::SQL_CALC_FOUND_ROWS => false
		// add other options as needed
	    ),
	    self::$_partsInit
	);
	parent::__construct($adapter);
    }

    public function sqlCalcFoundRows($flag = true)
    {
        $this->_parts[self::SQL_CALC_FOUND_ROWS] = (bool) $flag;
        return $this;
    }

    protected function _renderSqlCalcFoundRows($sql)
    {
        if ($this->_parts[self::SQL_CALC_FOUND_ROWS]) {
            $sql .= ' SQL_CALC_FOUND_ROWS';
        }

        return $sql;
    }

}


Quick & dirty test code:

$db = Zend_Db::factory('mysqli', array('dbname'=>'test', 'username'=>'root',
'password'=>'xxxx'));

$select = new Zend_Db_Select_Mysql($db);
$select->from('foo');
$select->sqlCalcFoundRows();

print "SQL = " . $select . "\n";



Hector Virgen wrote:
  

And, yes, I actually use the extra options in my select queries. 

    

I'm shocked and amazed!  (see I told you I would be :-)


Regards,
Bill Karwin
  

[Simon Mundy]

Hi Hector

Could you please record this as an issue on the tracker - more so for extending Zend_Db_Select/Zend_Db_Table_Select so that we can look at a graceful way of implementing this solution.

FWIW, I don't bother with extending Zend_Db_Select for MySQL-specific functionality. Here's an example of how I retrieve using the FOUND_ROWS feature:-

        $model = new MyDbTable();

        $select = $model->select();

        $select->from($model->info(Zend_Db_Table::NAME), new Zend_Db_Expr('SQL_CALC_FOUND_ROWS *'));

        $rows  = $model->fetchAll($select);

        $cnt = $model->getAdapter()->fetchOne($db->select()

            ->from(null, new Zend_Db_Expr('FOUND_ROWS()')));

Cheers

Thanks, Bill! I never thought to override the constructor :)

However this still leaves the problem with using $table->select(), which I use more often the Zend_Db::select(). I'm a little hesitant to copy/paste the entire Zend_Db_Table_Select class, so for now I am using substr_replace within __toString() to make it work.

Thanks for the help! :)

-Hector

Bill Karwin wrote:

Hector Virgen wrote:
  

I've tried implementing your solution but I'm having trouble with the 
late static binding of the array Zend_Db_Select::$_partsInit. 

    

<?php

require_once 'Zend/Db/Adapter/Abstract.php';
require_once 'Zend/Db/Select.php';

class Zend_Db_Select_Mysql extends Zend_Db_Select
{

    const SQL_CALC_FOUND_ROWS = 'sqlCalcFoundRows';
    // add other options as needed

    public function __construct(Zend_Db_Adapter_Abstract $adapter)
    {
	/**
	 * Use array_merge() instead of simply setting a key
	 * because the order of keys is significant to the
	 * rendering of the query.
	 */
	self::$_partsInit = array_merge(
	    array(
		self::SQL_CALC_FOUND_ROWS => false
		// add other options as needed
	    ),
	    self::$_partsInit
	);
	parent::__construct($adapter);
    }

    public function sqlCalcFoundRows($flag = true)
    {
        $this->_parts[self::SQL_CALC_FOUND_ROWS] = (bool) $flag;
        return $this;
    }

    protected function _renderSqlCalcFoundRows($sql)
    {
        if ($this->_parts[self::SQL_CALC_FOUND_ROWS]) {
            $sql .= ' SQL_CALC_FOUND_ROWS';
        }

        return $sql;
    }

}


Quick & dirty test code:

$db = Zend_Db::factory('mysqli', array('dbname'=>'test', 'username'=>'root',
'password'=>'xxxx'));

$select = new Zend_Db_Select_Mysql($db);
$select->from('foo');
$select->sqlCalcFoundRows();

print "SQL = " . $select . "\n";



Hector Virgen wrote:
  

And, yes, I actually use the extra options in my select queries. 

    

I'm shocked and amazed!  (see I told you I would be :-)


Regards,
Bill Karwin
  

--

Simon Mundy | Director | PEPTOLAB

""" " "" """""" "" "" """"""" " "" """"" " """"" "  """""" "" "

202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000

Voice [phone] | Mobile [phone] | Fax [phone]

http://www.peptolab.com

[Hector Virgen]

http://framework.zend.com/issues/browse/ZF-3399

Thanks, Simon. That's a neat way to implement it without having to extend Zend_Db_Select.

-Hector

Simon Mundy wrote:

Hi Hector

Could you please record this as an issue on the tracker - more so for extending Zend_Db_Select/Zend_Db_Table_Select so that we can look at a graceful way of implementing this solution.

FWIW, I don't bother with extending Zend_Db_Select for MySQL-specific functionality. Here's an example of how I retrieve using the FOUND_ROWS feature:-

        $model = new MyDbTable();

        $select = $model->select();

        $select->from($model->info(Zend_Db_Table::NAME), new Zend_Db_Expr('SQL_CALC_FOUND_ROWS *'));

        $rows  = $model->fetchAll($select);

        $cnt = $model->getAdapter()->fetchOne($db->select()

            ->from(null, new Zend_Db_Expr('FOUND_ROWS()')));

Cheers

Thanks, Bill! I never thought to override the constructor :)

However this still leaves the problem with using $table->select(), which I use more often the Zend_Db::select(). I'm a little hesitant to copy/paste the entire Zend_Db_Table_Select class, so for now I am using substr_replace within __toString() to make it work.

Thanks for the help! :)

-Hector

Bill Karwin wrote:

Hector Virgen wrote:
  

I've tried implementing your solution but I'm having trouble with the 
late static binding of the array Zend_Db_Select::$_partsInit. 

    

<?php

require_once 'Zend/Db/Adapter/Abstract.php';
require_once 'Zend/Db/Select.php';

class Zend_Db_Select_Mysql extends Zend_Db_Select
{

    const SQL_CALC_FOUND_ROWS = 'sqlCalcFoundRows';
    // add other options as needed

    public function __construct(Zend_Db_Adapter_Abstract $adapter)
    {
	/**
	 * Use array_merge() instead of simply setting a key
	 * because the order of keys is significant to the
	 * rendering of the query.
	 */
	self::$_partsInit = array_merge(
	    array(
		self::SQL_CALC_FOUND_ROWS => false
		// add other options as needed
	    ),
	    self::$_partsInit
	);
	parent::__construct($adapter);
    }

    public function sqlCalcFoundRows($flag = true)
    {
        $this->_parts[self::SQL_CALC_FOUND_ROWS] = (bool) $flag;
        return $this;
    }

    protected function _renderSqlCalcFoundRows($sql)
    {
        if ($this->_parts[self::SQL_CALC_FOUND_ROWS]) {
            $sql .= ' SQL_CALC_FOUND_ROWS';
        }

        return $sql;
    }

}


Quick & dirty test code:

$db = Zend_Db::factory('mysqli', array('dbname'=>'test', 'username'=>'root',
'password'=>'xxxx'));

$select = new Zend_Db_Select_Mysql($db);
$select->from('foo');
$select->sqlCalcFoundRows();

print "SQL = " . $select . "\n";



Hector Virgen wrote:
  

And, yes, I actually use the extra options in my select queries. 

    

I'm shocked and amazed!  (see I told you I would be :-)


Regards,
Bill Karwin
  

--

Simon Mundy | Director | PEPTOLAB

""" " "" """""" "" "" """"""" " "" """"" " """"" "  """""" "" "

202/258 Flinders Lane | Melbourne | Victoria | Australia | 3000

Voice [phone] | Mobile [phone] | Fax [phone]

http://www.peptolab.com