Zend2 combine tablegateway array

Question

I'm new to Zend2, and want to combine two tablegateway objects

I have two tables: prices and sizes. Every price has multiple sizes. I want to join these tables into an array, so I can list the prices with the sizes in it.

For example:

array(
        1 => array(
                'price' => 45,
                'description' => 'Lorem ipsum',
                'sizes' => array(
                    1 => '16',
                    2 => '17',
                    3 => '20',
                    4 => '21'
                )
        ),
        2 => array(
                'price' => 50,
                'description' => 'Lorem ipsum',
                'sizes' => array(
                    1 => '34',
                    2 => '12',
                    3 => '21',
                    4 => '50'
                )
        )
    )

My PricesTable.php:

public function getPricesbyJewel($jewel)
{
    $rowset = $this->tableGateway->select(array('jewelid' => $jewel));
    return $rowset;
}

My SizesTable.php

public function getSizesbyPrice($price)
{
    $rowset = $this->tableGateway->select(array('priceid' => $price));
    return $rowset;
}

How I list the prices (so without the sizes in it)

$jewelPrices = array('jewelryPrices' => $this->getPricesTable()->getPricesbyJewel($jewel->id));
$jewelSizes = array('jewelrySizes' => $this->getSizesTable()->getSizesbyPrice($priceID);

How to list the sizes into the prices as an array of those tables in the controller?

try this http://stackoverflow.com/questions/14354802/tablegateway-with-multiple-from-tables?rq=1 — Ruwantha, Apr 06 '14 at 18:38

Royw · Accepted Answer · 2014-04-08T07:57:02.097

Fixed it with an inner join:

PriceTable.php

public function getPricesbyJewel($jewel)
{
    $sql = new Sql($this->tableGateway->getAdapter());

    $select = $sql->select();
    $select->from('jewelry_prices')
        ->join('jewelry_sizes', 'jewelry_prices.id=jewelry_sizes.priceID')
        ->where('jewelry_prices.jewelid='.$jewel);
    $resultSet = $this->tableGateway->selectWith($select);
    return $resultSet;
}

Controller:

$prices = array('jewelryPrices' => array());
foreach($this->getPricesTable()->getPricesbyJewel($jewel->id) as $key => $price){
            if(!array_key_exists($price->priceID, $prices['jewelryPrices'])){
                $prices['jewelryPrices'][$price->priceID] = array(
                    'orderNote' => $price->orderNote,
                    'price'     => $price->price,
                    'description' => $price->description,
                    'sizes'     => array()
                );
                array_push($prices['jewelryPrices'][$price->priceID]['sizes'], $price->size);
            } else {
                array_push($prices['jewelryPrices'][$price->priceID]['sizes'], $price->size);
            }
        }

Zend2 combine tablegateway array

1 Answers1