I am trying to join table 'cataloginventory_stock_status' with order item table using OrderItemRepositoryInterface and searchCriteriaBuilder.
$searchCriteria = $this->searchCriteriaBuilder
->addFilter('product_type', 'simple', 'eq')
->create();
$collection = $this->orderItemRepository->getList($searchCriteria);
$collection->getSelect()->join(array('stock' => 'cataloginventory_stock_status'), 'main_table.product_id = stock.product_id', 'stock.stock_status')
->where('stock.stock_status = 1')
->columns('SUM(qty_ordered) as total_qty')
->group('main_table.product_id')
->order(array('total_qty DESC', 'name ASC'));
return $collection;
When i echo $collection->getSelect();
then its show write query, but got wrong data on template file.
Any solution for this please.
Best Answer
Just use collection directly by injecting it in your constructor instead of repository because actual implementations of
getList
method are not powerful enough to permit additional joins.