14

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

18

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; 
    }
}
| improve this answer | |
  • 2
    \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
11

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

| improve this answer | |
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.

| improve this answer | |
  • Work, from controller. – Zulkhaery Basrul Mar 10 '16 at 7:40
  • 9
    Don't use the object manager but dependency injection instead. – Giel Berkers Jan 27 '17 at 13:49
1

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?

| improve this answer | |
  • 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 9:21
1
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);
| improve this answer | |
  • 2
    Don't inject the DI manager. Inject the dependency. Either Test\Vendor\Model\VendorFactory or Test\Vendor\Model\Vendor\Collection. – nevvermind Nov 9 '15 at 20:36
1
<?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); ?>
| improve this answer | |
0

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());
    }
| improve this answer | |
  • 1
    Please describe what your code is doing and how it is solving the OP's problem (what part of the code specifically). – 7ochem Apr 4 '16 at 12:40

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.