如何使用dbSelect将此Mysql查询实现到Zend框架1中

Question

SELECT 
            CONCAT(us.user_id,' ', us.name),
            UPPER(sc.so_number) Order_no ,
            sh.upc UPC,re.label Error,
            (SELECT count(*) FROM order_checker_scan scan WHERE scan.so_number =sh.so_number and scan.upc=sh.upc and scan.user_id!=0
        and DATE_FORMAT(scan_time,'%Y-%m-%d') >= '2015-11-01' ) AS
        prev_data, 
            (select CONCAT(u.user_id,' ', u.name) from users u,picklist_history p where u.user_id=p.user_id and
        p.so_number=sh.so_number limit 1) as picker,
            sh.item_key Times,
            DATE_FORMAT(sc.date_started,'%b%d %Y %h:%i%p') datetime,sh.qty_required QTY 
            FROM 
            order_checker_short sh,
            order_checker_header 
            sc,order_checker_short_reason re,
            users us 
            WHERE sh.so_number=sc.so_number AND 
            sh.reason_id=re.reason_id AND 
            sc.created_by=us.user_id And 
            sc.created_by!=0 AND 
            DATE_FORMAT(date_started,'%Y-%m-%d') between '2015-11-16' and '2015-11-17' AND 
            sh.reason_id !=0 AND 
            sh.upc !=1 
            GROUP BY sc.so_number,sh.upc 
            ORDER BY sc.date_started DESC, sc.so_number DESC , sh.upc ASC

Answer 1

Please test the following:

// 1st subselect
$prevDataSelect = $db->select()
    ->from(array('scan' => 'order_checker_scan'), array('count(*)'))
    ->where('scan.so_number = sh.so_number')
    ->where('scan.upc = sh.upc')
    ->where('scan.user_id != 0')
    ->where("DATE_FORMAT(scan_time,'%Y-%m-%d') >= '2015-11-01'");

// 2nd subselect
$pickerSelect = $db->select()
    ->from(array('u' => 'users', 'p' => 'picklist_history'), array("CONCAT(u.user_id,' ', u.name)"))
    ->where('u.user_id = p.user_id')
    ->where('p.so_number = sh.so_number')
    ->limit(1);

// Main selection
$mainSelect = $db->select()
    ->from(
        // tables
        array(
            'sh' => 'order_checker_short',
            'sc' => 'order_checker_header',
            're' => 'order_checker_short_reason',
            'us' => 'users',
        ),
        // columns
        array(
            'SomeName'  => "CONCAT(us.user_id, ' ', us.name)",
            'Order_no'  => 'UPPER(sc.so_number)',
            'UPC'       => 'sh.upc',
            'Error'     => 're.label',
            'prev_data' => new Zend_Db_Expr('(' . $prevDataSelect . ')'),
            'picker'    => new Zend_Db_Expr('(' . $pickerSelect . ')'),
            'Times'     => 'sh.item_key',
            'datetime'  => "DATE_FORMAT(sc.date_started,'%b%d %Y %h:%i%p')",
            'QTY'       => 'sh.qty_required',
        )
    )
    // AND WHERE clauses
    ->where('sh.so_number = sc.so_number')
    ->where('sh.reason_id = re.reason_id')
    ->where('sc.created_by = us.user_id')
    ->where('sc.created_by != 0')
    ->where("DATE_FORMAT(date_started, '%Y-%m-%d') between '2015-11-16' and '2015-11-17'")
    ->where('sh.reason_id != 0')
    ->where('sh.upc != 1')
    // GROUP BY clause
    ->group(array('sc.so_number', 'sh.upc'))
    ->order(array('sc.date_started DESC', 'sc.so_number DESC', 'sh.upc ASC'));

If doesn't work please tell me what's the output of $mainSelect->assemble()