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())
->join开发者_高级运维Left(
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
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"))
I think you're using column select incorrectly here. It should be something like:
// "ballast AS productType, 0 as provisionalQty"
$db->columns(array("productType" => "ballast", "provisionalQty" => 0));
Just remember the columns abstraction is reverse of the normal AS statement and you shouldn't have to include "AS."
For those who are googling and looking for Zend 2 or Laminas solution, here is my code:
$sql = new Laminas\Db\Sql\Sql($this->adapter);
$select = $sql->select();
$select->from(['i' => ...])
->columns([
'id' => 'id',
'provisionalQty' => new Laminas\Db\Sql\Predicate\Expression('"0"'),
'productType' => new Laminas\Db\Sql\Predicate\Expression('"ballast"')
]);
精彩评论