How to make COUNT AS in Zend_Db_Select?

Question

So I have this query:

select u.*, count(mi.media_id) as media_count
from user as u
left join media_items as mi on mi.user_id = u.user_id
where u.is_enabled = 1 and
group by u.user_type
having media_count > 0;

I'm trying to translate it to a Zend_Db_Select. So far I have everything but the count of the media. This is what I tried:

$select->from(array('u' => 'user'), array('*', new Zend_Db_Expr('COUNT(mi.media_id) AS media_count'))
       ->joinLeft('mi' => 'media_items'), 'mi.user_id = u.user_id')
       ->where('u.is_enabled = 1')
       ->group('u.user_type')
       ->having('media_count > 0');

This gets me the error:

"Mysqli prepare error: Unknown column 'media_count' in 'having clause'"

How do I create this statement the Zend way?

Also, I outputted the query that this created and ran it on MySQLWorkBench and it ran just fine.

Edit I've just tried:

->columns('media_count' => new Zend_Db_Expr('COUNT(mi.media_id)'))

Same error.

When you say you outputted the query and ran it to test, do you mean you did echo $select? And this produced the right SQL? — Tim Fountain
– Tim Fountain, Commented Aug 21, 2013 at 18:15
Weird then, not sure why this doesn't work. >having('COUNT(mi.media_id) > 0'); should work as a workaround — Tim Fountain
– Tim Fountain, Commented Aug 21, 2013 at 19:21
Yup, I tried that finally and that works. Was hoping to figure out why the other way doesn't work. — kailoon
– kailoon, Commented Aug 21, 2013 at 19:26

Fouad Fodail · Accepted Answer · 2013-08-21 19:09:05Z

1

This should work properly :

$select->from(array('u' => 'user'), array('*', 'media_count' => 'count(mi.media_id)'))
   ->joinLeft('mi' => 'media_items'), 'mi.user_id = u.user_id')
   ->where('u.is_enabled = 1')
   ->group('u.user_type')
   ->having('media_count > 0');

answered Aug 21, 2013 at 19:09

Fouad Fodail

2,6431 gold badge18 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to make COUNT AS in Zend_Db_Select?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related