2

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.

2

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.

public function __construct(\Magento\Sales\Model\ResourceModel\Order\Item\CollectionFactory $collectionFactory)
{
    $this->orderItemCollectionFactory = $collectionFactory;
}

...

$collection = $this->orderItemCollectionFactory->create();
$collection->addFieldToFilter('product_type', 'simple');
$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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.