Zend: Select object: How do I replace the selected columns set by from()?

Question

first of all, that's what I'm trying to do:

In one of my classes in the library I want to count the total amount of rows of a search result. The class uses a select object set by the appendant model of the search result. My problem is now, this select() has already set the requested columns by from(), but to simply count the rows I just want to select the id, because the website has to to be performant. I can't simply change the values of the object, because I'm using it in the library and the variables are protected. Unfortunately, Zend has no function for the mySql count command and I don't want to use static mySql code, because it could be, that we switch our database system in the future.

Now here's my question:

Is there any possibility by Zend_Select how I could change the selected columns?

Answer 1

Try this:

$select->reset(Zend_Db_Select::COLUMNS)
       ->from('thetable', 'COUNT(*)');

replacing the 'thetable' with the correct table name.

Answer 2

This is from a project and isn't tested, but one of these should work.

$select->from(array("table_name" => "table_name"), array("my_col" => "COUNT(id)"));

OR

$select->from(array("table_name"), array("my_col" => "COUNT(id)"));

This is the same as

SELECT COUNT(id) as my_col FROM table_name 

Hope that helps

Jake

Answer 3

This one didn't work for me (I needed to select only from one joined table):

$select->reset(Zend_Db_Select::COLUMNS)
   ->from('thetable', 'COUNT(*)');

Maybe because I had some joins. But nevertheless, here's the solution: to use reset() and then columns():

$select->setIntegrityCheck(false)
        ->from(['t1' => 'table1'])
        ->join(['t2' => 't2'], 't1.id = t2.t1_id')
        ->reset(Zend_Db_Select::COLUMNS)
        ->columns('t1.*');

Just FYI, the version of Zend Framework is 1.12