2

I have this working SQL query :

select prd_brand.* from prd_brand
inner join
(
    select distinct value from catalog_product_entity_int
    where 
    rowid in (select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1)
)t
on prd_brand.brand_id = t.value

How can I nest this select query in the join in Magento 2 ?

I did it like this :

$subquery = new \Zend_Db_Expr('SELECT DISTINCT value from catalog_product_entity_int where  rowid in (select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1)');
    $brandCollection->join(array(
                        'cpei' => 'catalog_product_entity_int'),
                        'cpei.value = main_table.brand_id',
                    array('subquery' => $subquery));

But it gives this query :

    SELECT `main_table`.*, 
SELECT DISTINCT value from catalog_product_entity_int 
where rowid in 
(select rowid from catalog_product_entity_int where attribute_id = 97 and value = 1) AS `subquery` 
FROM `prd_brand` AS `main_table` 
INNER JOIN `catalog_product_entity_int` AS `cpei` ON cpei.value = main_table.brand_id
6

Can you try like below. It will surely work. i have checked in my local :).

$subquery = new \Zend_Db_Expr('(SELECT DISTINCT value from catalog_product_entity_int where  row_id in (select row_id from catalog_product_entity_int where attribute_id = 97 and value = 1))');

$brandCollection->getSelect()->join( array( 't' => $subquery ), 'main_table.brand_id = t.value', array());
11
  • any doubts please ask :) Jun 30 '17 at 19:22
  • it gives exactly the same query. Jun 30 '17 at 21:58
  • Any idea please? I'm really stuck... Thank you. Jul 1 '17 at 11:15
  • 1
    Sure I will provide quert Jul 1 '17 at 11:28
  • 1
    I updated the query please check it Jul 1 '17 at 17:38
0

For what it's worth I've implemented this way. It works on Magento >= 2.3.

# use Magento\Sales\Api\Data\OrderInterface;
# use Magento\Sales\Api\Data\OrderInterfaceFactory;
# use Magento\Framework\App\ResourceConnection;

    public function __construct(
        ...
        ResourceConnection $resource,
        OrderCollectionFactory $orderCollectionFactory,
        OrderInterfaceFactory $orderInterfaceFactory
    ) {
        ...
        $this->resource = $resource;
        $this->orderCollectionFactory = $orderCollectionFactory;
        $this->orderInterfaceFactory = $orderInterfaceFactory;
    }

       $salesOrderTable = $this->getResource()->getTableName('sales_order');
        $salesOrderItemTable = $this->getResource()->getTableName('sales_order_item');

        $lastOrder = $this->orderInterfaceFactory->create()
            ->loadByIncrementId($orderIncrementId);

        if(!$lastOrder->getId()) {
            return false;
        }

        $orderCollection = $this->orderCollectionFactory->create();

        $subQuery = new \Zend_Db_Expr(sprintf("(SELECT created_at FROM %s WHERE increment_id = '%s')",
            $salesOrderTable,
            $orderIncrementId));

        $orderCollection->getSelect()
            ->join(
                ['sales_item' => $salesOrderItemTable],
                'main_table.entity_id = sales_item.order_id'
            );

        $orderCollection->addFieldToFilter('main_table.created_at', [
                'gt' => $subQuery,
                ])
            ->addFieldToFilter('main_table.state', ['in' => [
                Order::STATE_NEW,
                Order::STATE_PROCESSING,
                Order::STATE_PAYMENT_REVIEW,
                Order::STATE_PENDING_PAYMENT,
                Order::STATE_HOLDED,
                Order::STATE_COMPLETE,
            ]])
            ->addFieldToFilter('sales_item.sku', $sku);

        $orderCollection->getSelect()
            ->reset(\Zend_Db_Select::COLUMNS)
            ->columns(['SUM(sales_item.qty_ordered - sales_item.qty_refunded) AS sold']);

    if(!$orderCollection->getSize()) {
        return false;
    }

    return $orderCollection->getFirstItem()->getData('sold');

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.