Re: [fw-db] Subselects using Zend_Db_Select]

Turns out, I didn't use Zend_Db_Select, I used my default DB adapter and used fetchPairs (thanks Jaka) and now it works like a charm. The code in the controller is only two lines:

$db = Zend_Db_Table_Abstract::getDefaultAdapter();

$this->view->post_count = $db->fetchPairs('SELECT status,COUNT(*) FROM articles WHERE status IN (0,1,2) GROUP BY status;');

And my view looks like this:

<ul>

  <li><a href="<?= $this->url(array('post_status'=>'all')) ?>">All Posts</a></li>

  <li><a href="<?= $this->url(array('post_status'=>'published')) ?>">Published (<?= $this->post_count[2] ?>)</a></li>

  <li><a href="<?= $this->url(array('post_status'=>'review')) ?>">Pending Review (<?= $this->post_count[1] ?>)</a></li>

  <li><a href="<?= $this->url(array('post_status'=>'draft')) ?>">Drafts (<?= $this->post_count[0] ?>)</a></li>

</ul>

Thank you all for helping.

-Henry

On Feb 18, 2009, at 2:53 PM, Jason Webster wrote:

Why do you have to use Zend_Db_Select to do this??
If it's a static query like that, that isn't built procedurally, there's nothing wrong with just
using the SQL statement.

On 18/02/2009 10:31 AM, Henry Umansky wrote:

Is there an easy way to run the following query using Zend_Db_Select

without using joins?

SELECT

(SELECT Count(*) FROM articles WHERE status=0) AS 'Draft',

(SELECT Count(*) FROM articles WHERE status=1) AS 'Review',

(SELECT Count(*) FROM articles WHERE status=2) AS 'Published'

I just need to get the count according to the record status.

-Henry