Help With Select Query In ZF

Question

Ok. This version of select works:

    $select = $this->select();
    $select->setIntegrityCheck(false);
    $select->from(array('u' => $this->_name),
                  array('u.id', 'u.username', 'u.avatar_path',
                        '(SELECT COUNT(*) FROM media WHERE user_id = u.id) media_count'));
    $where = "u.status = 'active' AND u.avatar_path <> 'images/photo-thumb.gif' AND u.show_on_homepage = 1";
    $where .= " AND (u.status_message IS NOT NULL OR u.profile_theme_id IS NOT NULL)";
    $select->where($where);
    return $this->fetchAll($select);

Now what I did was I just added a single condition on the 7th line (media_count > 0) so it looks like this:

    $select = $this->select();
    $select->setIntegrityCheck(false);
    $select->from(array('u' => $this->_name),
                  array('u.id', 'u.username', 'u.avatar_path',
                        '(SELECT COUNT(*) FROM media WHERE user_id = u.id) media_count'));
    $where = "u.status = 'active' AND u.avatar_path <> 'images/photo-thumb.gif' AND u.show_on_homepage = 1";
    $where .= " AND (u.status_message IS NOT NULL OR u.profile_theme_id IS NOT NULL OR media_count > 0)";
    $select->where($where);
    return $this->fetchAll($select);

And this already doesn't work and I get an error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'm.media_count' in 'where clause'

When I remove the condition it works. I also tried using u.media_count without success.

EDIT: The media_count value works. I have left out the condition (media_count > 0) and then I did var_dump on the result set and the key media_count is in the array with correct value.

EDIT2: I have simplified the selects above (I have removed unimportant lines).

Answer 1

I'm not specifically familiar with the zend-framework, but I think the problem is that media_count is not an actual column of the table. Try replacing

media_count > 0

with

(SELECT COUNT(*) FROM media WHERE user_id = u.id) > 0