phpmysqlzend-frameworkzend-db-select

SELECT '0' AS variable ... with Zend_Db_Select

I am trying to create a select statement that uses the following structure:

$db
    ->select()  
    ->from(  
        array('i' => ...),  
        array('provisional', 'itemID', 'orderID'))  
    ->columns(array("'0' AS provisionalQty", "'ballast' AS productType"))  
    ->joinLeft(  
        array('o' => ...),  
        'i.orderID = o.orderID', array())  
    ->joinLeft(  
        array('f' => ...),  
        'i.productID = f.fixtureID AND f.supplierID = o.supplierID', array())  
    ->joinLeft(  
        array('b' => ...),  
        'f.lampTechnology = b.lampTechnology ' .  
        ' AND f.lampCount = b.lampCount ' .  
        ' AND f.ballastVoltage = b.ballastVoltage ' .  
        ' AND b.supplierID = o.supplierID')  
    ->where('i.orderID = ?', $oObj->orderID, Zend_Db::INT_TYPE)  
    ->where('!i.hidden AND i.productType = ? AND !i.provisional', 'fixture')

The equivalent in MySQL would look something like this (which works fine)...

SELECT '0' AS provisionalQty, 'ballast' AS productType, i.* FROM ... LEFT JOIN ... WHERE ...;

This, however, does not work as expected. The $db->columns() method expects there to be a table attached to each column even the 'pseudo'-columns. Any ideas?

-Chris

Solution

  • Because of the way this class works, it seems that using a string/array looks for a specific table, whereas the Zend_Db_Expr class does not require an actual table.

    $db
    ->select()
    ->columns(new Zend_Db_Expr("'0' AS provisionalQty, 'ballast' AS productType"))