How to call Direct SQL Queries and join to collection In Magento2

  • I just want to call direct sql query in Magento2 as like in magento1.x

  • In you block or model files you need to initialize resource then you need to call connection

    that is

    protected $_resource;

    and

    public function __construct(
        \Magento\Backend\Block\Template\Context $context,
        \Magento\Framework\App\Resource $resource,
        array $data = []
    ) {
        $this->_resource = $resource;
        parent::__construct($context, $data);
    }
    

    for connection

    protected function getConnection()
    {
        if (!$this->connection) {
            $this->connection = $this->_resource->getConnection('core_write');
        }
    
        return $this->connection;
    }
    

    below is example in block file

    <?php
    /**[email protected]*/
    namespace Sugarcode\Test\Block;
    
    class Joinex extends \Magento\Framework\View\Element\Template
    {
        protected $_coreRegistry = null;
        protected $_orderCollectionFactory = null;
        protected $connection;
        protected $_resource;
    
        public function __construct(
            \Magento\Backend\Block\Template\Context $context,
            \Magento\Framework\Registry $registry,
            \Magento\Framework\App\Resource $resource,
            \Magento\Sales\Model\Resource\Order\CollectionFactory $orderCollectionFactory,
            array $data = []
        ) {
            $this->_orderCollectionFactory = $orderCollectionFactory;
            $this->_coreRegistry = $registry;
            $this->_resource = $resource;
            parent::__construct($context, $data);
        }
    
    
    
        public function _prepareLayout()
        {
            return parent::_prepareLayout();
        }
    
        protected function getConnection()
        {
            if (!$this->connection) {
                $this->connection = $this->_resource->getConnection('core_write');
            }
            return $this->connection;
        }
    
        public function getDirectQuery()
        {
            $table=$this->_resource->getTableName('catalog_product_entity'); 
            $sku = $this->getConnection()->fetchRow('SELECT sku,entity_id FROM ' . $table);
            return $sku;
        }
    
        public function getJoinLeft()
        {
              $orders = $this->_orderCollectionFactory->create();
              $orders->getSelect()->joinLeft(
                ['oce' => 'customer_entity'],
                "main_table.customer_id = oce.entity_id",
                [   
                    'CONCAT(oce.firstname," ", oce.lastname) as customer_name',
                    'oce.firstname',
                    'oce.lastname',
                    'oce.email'
                ]
            );
    
            //$orders->getSelect()->__toString(); $orders->printlogquery(true); exit;
            return $orders; 
        }
    }
    

    `\Magento\Framework\App\Resource` does not exist (at least not in 2.1.3). Don't you mean `ResourceConnection`?

    Please update the answer accordingly to newer version as this seems to be right, but not working in Magento 2.1.5.

License under CC-BY-SA with attribution


Content dated before 7/24/2021 11:53 AM