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

Question

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

score 20 · Accepted Answer · edited Oct 07 '18 at 22:40

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
/**pradeep.kumarrcs67@gmail.com*/
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? — Giel Berkers, Jan 28 '17 at 21:08
Please update the answer accordingly to newer version as this seems to be right, but not working in Magento 2.1.5. — Max, Mar 28 '17 at 23:17

score 12 · Answer 2 · answered Dec 29 '15 at 14:05

you have use old call for beta version core_write and core_read in rc is like this :

 protected  _resource;
  public function __construct(Context $context,
\Magento\Framework\App\ResourceConnection $resource)
  {
    $this->_resource = $resource;
    parent::__construct($context);

  }

get adapter :

$connection = $this->_resource->getConnection(\Magento\Framework\App\ResourceConnection::DEFAULT_CONNECTION);

get table and select:

$tblSalesOrder = $connection->getTableName('sales_order');
$result1 = $connection->fetchAll('SELECT quote_id FROM `'.$tblSalesOrder.'` WHERE entity_id='.$orderId);

complete course from here

score 7 · Answer 3 · edited Dec 14 '16 at 13:31

7

I have achieved this in following way. I have a custom file where I am creating object of it and it worked. Check it once.

class Sample extends \Magento\Framework\App\Http implements \Magento\Framework\AppInterface
{

    public function sampleMethod()
    {
        $connection = $this->_objectManager->create('\Magento\Framework\App\ResourceConnection');
        $conn = $connection->getConnection();
        $select = $conn->select()
            ->from(
                ['o' => 'catalog_category_entity_varchar']
            )
            ->where('o.value=?', '2');
        $data = $conn->fetchAll($select);
        print_r($data);
    }

}

Try and let me know if it works for you.

edited Dec 14 '16 at 13:31

Josh Davenport-Smith

949
1
10
22

answered Dec 21 '15 at 11:47

Rahul Anand

539
4
10

Work, from controller. – Zul Mar 10 '16 at 07:40
10

Don't use the object manager but dependency injection instead. – Giel Berkers Jan 27 '17 at 13:49

score 2 · Answer 4 · answered Apr 29 '19 at 13:09

<?php 

 $objectManager = \Magento\Framework\App\ObjectManager::getInstance(); 
 $resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
 $connection = $resource->getConnection();
 $tableName = $resource->getTableName('table_name');
 $attribute_information = "Select * FROM " . $tableName; //check for the  custom attribute condition". WHERE id = " . $manufacture . ";";
// fetchOne it return the one value
$result = $connection->fetchOne($attribute_information); ?>

https://www.rakeshjesadiya.com/direct-sql-queries-in-magento-2/ check all the direct query. — Rakesh Jesadiya, Mar 13 '20 at 06:06

Rakesh Jesadiya · Answer 5 · 2016-02-10T10:12:07.293

2

For Join Query,
   protected $_objectManager; 
   public function __construct(
        \Magento\Framework\ObjectManagerInterface $objectManager,
        \Test\Vendor\Model\ResourceModel\Vendor $resourceModel
    ) {
        $this->resourceModel = $resourceModel;
        $this->_objectManager = $objectManager;
    }

$collection = $this->_objectManager->create('Test\Vendor\Model\Vendor')->getCollection();
$vendor_id = 5; //get dynamic vendor id
        $collection->getSelect()->join('secondTableName as s2','main_table.entity_id = s2.vendor_id', array('*'))->where("main_table.entity_id = ".$vendor_id);

edited Feb 10 '16 at 10:12

answered Nov 04 '15 at 07:11

Rakesh Jesadiya

42,221
18
132
183

2

Don't inject the DI manager. Inject the dependency. Either Test\Vendor\Model\VendorFactory or Test\Vendor\Model\Vendor\Collection. – nevvermind Nov 09 '15 at 20:36

score 1 · Answer 6 · answered Dec 29 '15 at 13:58

Does not work for me :(

Here is my block file:

<?php
namespace Silver\Customize\Block;
use \Magento\Framework\View\Element\Template;

class Main extends Template
{    

    protected $connection;
    protected $_resource; 

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


    protected function _prepareLayout()
    {
    $this->setMessage('Hello');
    $this->setName($this->getRequest()->getParam('name'));    

    }

    public function getGoodbyeMessage()
{
    return 'Goodbye World';
}

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

}

I get this error: Object DOMDocument should be created.

What am I missing?

not an answer. Also, I am not sure what doesn't work. You don't use $connection->fetchAll() for example anywhere — Maciej Paprocki, Jul 26 '16 at 09:21

score 0 · Answer 7 · answered Apr 04 '16 at 12:17

try this one :

    //for print log on custom log file.


    $writer = new \Zend\Log\Writer\Stream(BP . '/var/log/mylog.log');
    $logger = new \Zend\Log\Logger();
    $logger->addWriter($writer);
    $logger->info('Query cron  srarting...: ');
    try{
        $themeId=273;
        $this->_resources = \Magento\Framework\App\ObjectManager::getInstance()
        ->get('Magento\Framework\App\ResourceConnection');
        $connection= $this->_resources->getConnection();

        $negotiateTable = $this->_resources->getTableName('table_name');
        $sql = "Select * FROM " . $negotiateTable;//". WHERE id = " . $themeId . ";";
        $result = $connection->fetchAll($sql);
        foreach ($result as $item){
            $logger->info('Query cron  query data...: '.json_encode($item));
        }

    }catch (\Exception $e){
        $logger->info('Query cron  query data exception'.$e->getMessage());
    }

Please describe what your code is doing and how it is solving the OP's problem (what part of the code specifically). — 7ochem, Apr 04 '16 at 12:40

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

7 Answers7

Linked

Related