[FIXED] cakephp how to avoid the repeating columns in query result

Issue

I have a query like

$p = $this->Products->findById( $id )
              ->select(['name', 'description', 'category_id', 's.name', 'pp.price'])
              ->join([
                'table' => 'sizes',
                'alias' => 's',
                'type' => 'INNER',
                'conditions' => 's.category_id = Products.category_id',
            ])
            ->join([
                'table' => 'products_prices',
                'alias' => 'pp',
                'type' => 'LEFT',
                'conditions' => 'pp.size_id = s.id AND pp.product_id = Products.id',
            ]);

The problem is if the product has 10 different sizes, 10 rows will be produced with repeating name, description, category_id elements

Is there a way to rewrite it so the size's name and prices are delivered as array as a sub-array?

Solution

Maybe just use Containable instead of Join? You can run a find() on your Products and contain Sizes and ProductPrices. This would get you the array/sub-array you're looking for.

The only down-side is that it would still pull products even if they don't have a matching size.

It's tough to tell what you're really going for, since you're joining on category_id, which isn't really referenced. Maybe if you explain in more detail what the goal of this query is, we could provide a better answer on how to achieve.

Answered By - Dave