Nested/subquery select in a join query

Question

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

Rama Chandran M · Accepted Answer · 2017-07-03 10:59:13Z

9

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());

edited Jul 3, 2017 at 10:59

answered Jun 30, 2017 at 19:21

Rama Chandran M

3,26515 gold badges24 silver badges38 bronze badges

any doubts please ask :)

Rama Chandran M
– Rama Chandran M

2017-06-30 19:22:09 +00:00
Commented Jun 30, 2017 at 19:22
it gives exactly the same query.

androniennn
– androniennn

2017-06-30 21:58:51 +00:00
Commented Jun 30, 2017 at 21:58
Any idea please? I'm really stuck... Thank you.

androniennn
– androniennn

2017-07-01 11:15:29 +00:00
Commented Jul 1, 2017 at 11:15
1

Sure I will provide quert

Rama Chandran M
– Rama Chandran M

2017-07-01 11:28:44 +00:00
Commented Jul 1, 2017 at 11:28
1

I updated the query please check it

Rama Chandran M
– Rama Chandran M

2017-07-01 17:38:06 +00:00
Commented Jul 1, 2017 at 17:38

| Show 6 more comments

medina · Accepted Answer · 2020-05-28 02:04:59Z

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');

Stack Exchange Network

Nested/subquery select in a join query

2 Answers 2

Your Answer

Linked

Hot Network Questions

Nested/subquery select in a join query

2 Answers 2

Your Answer

Sign up or log in

Post as a guest

Linked

Related

Hot Network Questions