how to build nested select on zend db

Question

Hi i have trouble with this query

SELECT * FROM(
    SELECT `b`.*,`owner`.firstname,`owner`.lastname,`owner`.email,
    (
        SELECT COUNT(`ps`.profile_id)   FROM `profile` AS `ps`
        LEFT JOIN `xref_store_profile_brand` AS `xbp` ON `xbp`.profile_id = `ps`.profile_id
        WHERE `xbp`.brand_id = b.brand_id AND ps.role = 'salesrep' AND `xbp`.store_id IS NULL
    ) AS `salesrepTotal`,
    (
        SELECT GROUP_CONCAT(`ms`.firstname)   FROM `profile` AS `ps`
        LEFT JOIN `xref_store_profile_brand` AS `xbp` ON `xbp`.profile_id = `ps`.profile_id
        LEFT JOIN `member` AS `ms`ON `ms`.member_id = `ps`.member_id
        WHERE `xbp`.brand_id = `b`.brand_id AND ps.role = 'salesrep' AND `xbp`.store_id IS NULL
    ) AS `salesrep`,
    (
        SELECT COUNT(`s`.store_id) FROM `store` AS `s`
        LEFT JOIN `xref_store_profile_brand` AS `xbs` ON `xbs`.store_id = `s`.store_id
        WHERE `xbs`.brand_id = `b`.brand_id AND `xbs`.brand_id IS NOT NULL
    ) AS `storeTotal`,
    (
        SELECT GROUP_CONCAT(`s`.name) FROM `store` AS `s`
        LEFT JOIN `xref_store_profile_brand` AS `xbs` ON `xbs`.store_id = `s`.store_id
        WHERE `xbs`.brand_id = `b`.brand_id AND `xbs`.brand_id IS NOT NULL
    ) AS `store`

    FROM `brand` AS `b`
    LEFT JOIN
    (
        SELECT `m`.firstname,`m`.lastname,`m`.email,`xspb`.brand_id FROM `member` AS `m`
        LEFT JOIN `profile` as `p` ON `p`.member_id = `m`.member_id AND `p`.role = 'designer' AND `p`.isPrimary = 1
        LEFT JOIN `xref_store_profile_brand` AS `xspb` ON `xspb`.profile_id = `p`.profile_id AND `xspb`.store_id IS NULL
    ) AS `owner` ON `owner`.brand_id =`b`.brand_id

    GROUP BY `b`.brand_id
) AS `final`

how can i convert this in to Zend_Db_Select object?

Th main problem is this part

SELECT `b`.*,`owner`.firstname,`owner`.lastname,`owner`.email,
    (
        SELECT COUNT(`ps`.profile_id)   FROM `profile` AS `ps`
        LEFT JOIN `xref_store_profile_brand` AS `xbp` ON `xbp`.profile_id = `ps`.profile_id
        WHERE `xbp`.brand_id = b.brand_id AND ps.role = 'salesrep' AND `xbp`.store_id IS NULL
    ) AS `salesrepTotal`,

possible duplicate of Zend_Db_Table subquery

Ankur
– Ankur

2015-07-21 07:14:23 +00:00
Commented Jul 21, 2015 at 7:14 — Ankur
– Ankur, Commented Jul 21, 2015 at 7:14

Alex · Accepted Answer · 2013-11-01 14:22:09Z

2

You need to use Zend_Db_Expr objects in your query and array structures for select AS.

below is the solution you are looking for:

<?php

$db = Zend_Db_Table::getDefaultAdapter();

//  inner query
$sqlSalesRepTotal = $db->select()
        ->from(array('ps' => 'profile'))
        ->joinLeft(array('xbp' => 'xref_store_profile_brand'), 'xbp.profile_id = ps.profile_id')
        ->where('xbp.brand_id = b.brand_id')
        ->where('ps.role = ?', 'salesrep')
        ->where('xbp.store_id IS NULL');

//  main query
$sql = $db->select()
        ->from(array('b' => 'brand'), array(
            //  NOTE: have to add parentesis around the expression
            'salesrepTotal' => new Zend_Db_Expr("($sqlSalesRepTotal)")
        ))
        ->where('....')
        ->group('brand_id');


//  debug
var_dump($db->fetchAll($sql));

answered Nov 1, 2013 at 14:22

Alex

6,4652 gold badges27 silver badges26 bronze badges

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

Collectives™ on Stack Overflow

how to build nested select on zend db

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related