Count on Zend_Db_Select

Question

Say I have a random zend_db_select object.

How can I perform a count on that object, so I know the amount of items that meet the query.

I tried the following:

$data->TotalRecords = $select->columns(new Zend_Db_Expr('COUNT(*)'))->query()->fetch();

But this gives me the following error:

Message: No table has been specifiedfor the FROM clause

The query by itself works fine and returns a resultset.

Jackpot

Answer 1

There's a couple of ways of specifying the columns to fetch in a Zend_Db_Select. The following two product the same SQL

$select = $db->select()
             ->from('myTable', array())
             ->columns(array('TotalRecords' => new Zend_Db_Expr('COUNT(*)')));

$select = $db->select()
             ->from('myTable', array('TotalRecords' => new Zend_Db_Expr('COUNT(*)')));

The from method takes a first argument, the table name, and a second argument, an array of columns to fetch. If you're using an expression, you can specify a 'key' => Expr.

It's really easy to convert a Zend_Db_Select into a SQL string for debugging or use with other functions.

echo $select; // prints SELECT COUNT(*) AS `TotalRecords` FROM `myTable`

This uses a toString method, which is called automatically by Zend_Db fetch methods:

$total = $db->fetchOne($select); 

echo $total; //prints the number of rows matching the query

Where $db is an instance of Zend_Db.

Gavra

Answer 2

Use $select->__toString() method to output your generated query and see what is wrong with it.

If u dont have a from clause in your query add From() method to your select object.

Karli

Answer 3

If you use Zend_Db_Select, you have to call the from method to set the table name. With a Zend_Db_Table_Select, the table is passed in the constructor, so you don't need to call from.

Concertmaster

Answer 4

$select = $db->select();

$select->from(
    'table_name',
    array('cnt' => 'count(1)')
);

Eskilstuna

Answer 5

I just encountered the same issue and found out what is going wrong

the Zend_Db_Select::columns functions expects an Array instead of a Object or String (when the first parameter is an String or Object it'll probably use this as main table for the columns you give but Im not sure about that.).

Changing your code to

$data->TotalRecords = $select->columns(array(new Zend_Db_Expr('COUNT(*)')))->query()->fetch();

Will fix your issue

Ferdy