Re: [fw-general] Conditions in TableGateway JOIN ON clause

[Brad Waite]

The following function works:

public function fetchProducts() {
    $products = new ProductsTable();
    $expression = new \Zend\Db\Sql\Expression('i.ProductID = ' . 
$products->table . '.ItemID AND i.ItemID < 5');

    $select = $this->products->getSql()->select();
    $select->columns(array($select::SQL_STAR))
           ->join(array('i' => 'Items'), $expression, array(), 'left')
           ->where->isNull('i.ItemID')
           ->where->greaterThan('Qty', 0);

    $newProducts = $products->selectWith($select);

    return $newProducts;
}

Is there a mechanism for defining an alias for the TableGateway's table so that 
I could do something like this:

  $expression = new \Zend\Db\Sql\Expression('i.ProductID = p.ItemID AND 
i.ItemID < 5');

In other words, alias 'Products' to 'p' for use in the Expression.

Thanks,

-Brad

On 3/21/2013 5:58 PM, Brad Waite wrote:
> How do you use expressions in the ON clause of a JOIN with a TableGateway? In 
> the following code, the "5" is being
> quoted with brackets by the Sqlsrv adapter.
> 
> class ProductsTable extends AbstractTableGateway
> {
>     protected $table = 'Products';
> }
> 
> 
> public function fetchProducts() {
>     $products = new ProductsTable();
>     $newProducts = $products->select(
>         function (Select $select) {
>             $select->join(
>                 array('i' => 'Items'),
>                 'i.ProductID = Products.ItemID AND Products.ItemID < 5',
>                 $select::SQL_STAR,
>                 $select::JOIN_LEFT
>             );
> 
>             $select->where->isNull('i.ItemID');
>             $select->where->greaterThan('Products.Qty', 0);
>         });
>     return $products;
> }
> 

-- 
List: fw-general@lists.zend.com
Info: http://framework.zend.com/archives
Unsubscribe: fw-general-unsubscr...@lists.zend.com