Zend_DB_Select using a SELECT string

[morthomtech]

Hi All

I would like to create a Zend_DB_Select object to
be used with Pagination

The Zend_DB_Select must be created from the sql below


SELECT member_id, name, email,
(select count(*) as downloads FROM download_table
where member_id = ?)
FROM member where member_id = ?

The list should look like this

member_id name email downloads
2 John jx@x.com 6
3 Jane jt@x.com 20
4 tom tt@x.com 10

How do I create a select object using a string
with the select query above?

[SirAdrian]

Try something like this.

[php]$fields = array(
'member' => array(
'member_id',
'name',
'email'
),
'download_table' => array(
'downloads' => 'count(download_table.downloadid)'
)
);

$select = $this->db->select()
->from('member', $fields['member'])
->joinLeftUsing('download_table', 'member_id', $fields['download_table'])
->where('member.member_id = ?', $id)
->group('member.member_id');[/php]

[morthomtech]

Hi SirAdrian,

Thank you very much for your suggestion it worked as expected.

How do you account for a case where the sub-query is


select count(*) as downloads FROM download_table
where rating > 0)

OR

select count(*) as downloads FROM download_table
where rating > 4)

Where the download_table have fields
member_id, rating etc

In other words how do you account for
where clause in the subquery.

[SirAdrian]

You could try adding either: [php]->where('download_table.rating > ?', $minRating)[/php] or [php]->having('download_table.rating > ?', $minRating)[/php]

Seems to be more of an SQL issue. You do not need a subquery for this, just an aggregate query.