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

Answer 1

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; 
 }
}

• Giel Berkers: \Magento\Framework\App\Resource does not exist (at least not in 2.1.3). Don't you mean ResourceConnection?
• Max: Please update the answer accordingly to newer version as this seems to be right, but not working in Magento 2.1.5.

Answer 2

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

Answer 3

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.

• Zul: Work, from controller.
• Giel Berkers: Don't use the object manager but dependency injection instead.

Answer 4

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

• nevvermind: Don't inject the DI manager. Inject the dependency. Either Test\Vendor\Model\VendorFactory or Test\Vendor\Model\Vendor\Collection.

Answer 5

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

• Rakesh Jesadiya: rakeshjesadiya.com/direct-sql-queries-in-magento-2 check all the direct query.

Answer 6

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?

• Maciej Paprocki: not an answer. Also, I am not sure what doesn't work. You don't use $connection->fetchAll() for example anywhere

Answer 7

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

• 7ochem: Please describe what your code is doing and how it is solving the OP's problem (what part of the code specifically).