+ Reply to Thread
Results 1 to 4 of 4

Thread: Zend_DB_Select using a SELECT string

  1. #1
    morthomtech is offline Junior Member
    Join Date
    Nov 2008
    Location
    Canada
    Posts
    7

    Default Zend_DB_Select using a SELECT string

    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?

  2. #2
    SirAdrian's Avatar
    SirAdrian is offline Member
    Join Date
    Apr 2008
    Posts
    87

    Default

    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]

  3. #3
    morthomtech is offline Junior Member
    Join Date
    Nov 2008
    Location
    Canada
    Posts
    7

    Default

    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.

  4. #4
    SirAdrian's Avatar
    SirAdrian is offline Member
    Join Date
    Apr 2008
    Posts
    87

    Default

    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.

+ Reply to Thread

Similar Threads

  1. How to un-quote a string retrieved by fetchRow?
    By mesh2005 in forum Databases
    Replies: 2
    Last Post: 10-19-2009, 06:50 PM
  2. Customization of query string in URL
    By go2riz in forum Web & Web Services
    Replies: 4
    Last Post: 10-06-2009, 03:11 AM
  3. getting the string to read as zend syntax
    By jigen7 in forum General Q&A on Zend Framework
    Replies: 2
    Last Post: 06-09-2009, 06:01 PM
  4. select inside select?
    By sllik in forum Databases
    Replies: 1
    Last Post: 05-23-2008, 11:49 AM
  5. PDF file with UTF-8 string
    By LukAs9 in forum Mail, Formats & Search
    Replies: 1
    Last Post: 08-24-2007, 06:55 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts