SELECT '0' AS variable ... with Zend_Db_Select

Question

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

I'm not much of a Zend guru and have only been playing around with it recently. I'm sure the Zend purists will shoot me down (and probably with good reason) but when the pure SQL to express a query is smaller (and simpler looking) than the Zend formatted equivelent I like to keep the query in raw SQL. Though this is probably partly because I have X years more experience in complex SQL than I have in Zend DB code — Peter M, Mar 03 '10 at 01:03
@Peter M: I agree with you! I often advise people that they are not *required* to use Zend_Db_Select. There's little benefit to doing so when just writing literal SQL is easier and clearer. I worked on the code in Zend_Db_Select when I worked on the ZF project, and I even wrote a note in the doc, explaining that using Zend_Db_Select is not mandatory. — Bill Karwin, Oct 01 '10 at 18:48
The benefit of Zend_Db_Select is when you have application logic that conditionally adds clauses to a query. It's a way to help you ensure correct SQL syntax when building a query dynamically. But when I write a complete SQL query that doesn't need to be "built," I just put it in a PHP here-document. — Bill Karwin, Oct 01 '10 at 18:52
I find that using Zend_Db_Select makes reuse much easier, and increases consistency across the application. — webjawns.com, Oct 06 '10 at 20:54

score 2 · Accepted Answer · answered Feb 25 '10 at 20:21

2

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"))

answered Feb 25 '10 at 20:21

webjawns.com

2,300
2
14
34

1

You can also do it this way: ->columns(array('provisionalQty' => new Zend_Db_Expr('0'), 'productType' => 'ballast')); – David Snabel-Caunt Mar 03 '10 at 00:56

typeoneerror · Answer 2 · 2010-02-25T20:32:06.717

0

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."

edited Feb 25 '10 at 20:32

answered Feb 25 '10 at 20:10

typeoneerror

55,990
32
132
223

Thanks for the response, but that was a no go. Fortunately, I just figured out a way to do this! – webjawns.com Feb 25 '10 at 20:20
Ah, sorry, that should've been an array. That's the syntax. Check the manual "Adding Expression Columns" http://framework.zend.com/manual/en/zend.db.select.html – typeoneerror Feb 25 '10 at 20:33
I understand the syntax of Zend_Db_Select, I just didn't quite understand how to include values that were not part of any table. Zend_Db attaches the table name as a prefix to the values within the from() and columns() methods by default. That was where the problem was... Zend_Db_Expr allows the use of a statement without attaching the table name. Zend_Db_Expr seems to be the way to go. – webjawns.com Mar 05 '10 at 16:29

score 0 · Answer 3 · answered Nov 03 '21 at 13:20

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"')
    ]);

SELECT '0' AS variable ... with Zend_Db_Select

3 Answers3