:::: MENU ::::
Posts tagged with: query

Magento 2 use custom query

Today we will discuss how in Magento 2 custom query works. Sometime magento 2 model is not enough to get data from database as per our requirements. So to fulfill this requirement we use custom query. In this example we will use select custom query, custom delete query, custom insert query and custom update query. So let’s start with example.

Get connection

First of all we will establish a connection to execute the query.

 $this->_resources = \Magento\Framework\App\ObjectManager::getInstance()->get('Magento\Framework\App\ResourceConnection');          
        $connection= $this->_resources->getConnection();

Insert Query

It is simple to use insert query just set the values for columns. Use the inset method.

$array=array(‘column1’=>'column1','column2'=>'column2');

$connection->insert($this->_resources->getTableName(test_table), $array);

Select Query

Selecting table data in custom query you can use following code.

$select = $connection->select()->from(['o' =>  $this->_resources->getTableName(test_table)]);
$result = $connection->fetchAll($select);

       foreach ($result as $data) {
           echo $data['id'];
            }

Update Query

Updating table data in custom query you can use following code.

$id = 1;
 $sql = "Update " . $this->_resources->getTableName('test_table') . " Set `title` ='test title' where `id` = ".$id;
     $connection->query($sql);

Delete Query

To delete table data in custom query you can use following code.

$id = 1;
$condition = $this->getConnection()->quoteInto('id = ?', $id);
            $this->getConnection()->delete($this->getTable('test_table'), $condition);

Join Table

To join table in custom query you can use following code.

        $select = $connection->select()->from(['o' =>  $this->_resources->getTableName('test_table')])->joinleft(
        ['test_table1' => $this->_resources->getTableName('test_table1')],
        'o.id = test_table1.id',
        ['column1','column2']
    );
Author: Qaisar Satti
Category: Magento 2
Last Modified: July 12, 2018


Magento 2 Find value comma separated string column

Today we discuss Magento 2 find value comma separated string column. Sometime you need to search in comma separated values in database. So for that we use the Finset to search in comma separated values. Let’s take the example of product collection. Let’s spose the field name test_sku have the comma separated values. So here is example.

protected $_productCollectionFactory;
public function __construct(
        \Magento\Catalog\Model\ResourceModel\Product\CollectionFactory $productFactory
    ) {
        $this->_productCollectionFactory = $productFactory;
    }
    public function getProductCollection()
    {
       $sku=’test’;    
      return   $this->_productCollectionFactory->create()->addFieldToFilter('test_sku', array('finset' => $sku));
     
     }
Author: Qaisar Satti
Category: Magento 2
Last Modified: July 12, 2018