11th June 2020
Magento 2 Direct SQL Queries in Zend Format
Eecrets Magento
Sometimes due to some project requirements, we need to directly communicate with Magento2 Database and we have to write SQL queries for that. This post will help you to write direct SQL Queries in Zend Format.
public function __construct(
\Magento\Framework\App\ResourceConnection $resourceConnection
) {
$this->resourceConnection = $resourceConnection;
}
$connection = $this->resourceConnection->getConnection();
$tableName = $this->resourceConnection->getTableName('customer_history');
$tableName2 = $this->resourceConnection->getTableName('order_history');
OR
Using Object Manager Directly:
$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName =$resource->getTableName('customer_history');
$tableName2 =$resource->getTableName('order_history');
Select Queries:
Syntax:
$select = $connection->select()
->from(
['p' => $tableName])
->where('p.column_name=?', $value)
->where('p.column_name2 >=?', $value)
->order('p.column_name3 DESC')
->limit($pagesize, $offset);
$data = $connection->fetchAll($select);
Example:
$select = $connection->select()
->from(
['p' => $tableName])
->where('p.customer_id=?', 5)
->where('p.status =?', 1)
->order('p.createdat DESC')
->limit(10, 0);
To get only selected columns from Table:
$select = $connection->select()
->from( ['p' => $tableName],['p.order_id', 'p.status'])
->where('p.customer_id=?', 5)
->where('p.status =?', 1)
->order('p.createdat DESC')
->limit(10, 0);
$data = $connection->fetchAll($select);
Perform Select Query with JOIN
$select = $connection->select()
->from(
['p' => $tableName], ['p.customer_id', 'p.status', 'p.order_id','o.ordertotal']
)
->join(
['o' => $tableName2], 'main_table.order_id = order.id', ['']
)
->where('p.order_id = (?)', '455');
$data = $connection->fetchAll($query);
Insert Query:
Syntax:
$data = ["column_name"=>$value,"column_name2"=>$value2,'column_name3'=>$value3]; $connection->insert($tableName, $data);
Example :
$data = ["customer_id"=>'22',"status"=>1,'order_id'=>'455']; $lastInsertedID= $connection->insert($tableName, $data);
Update Query:
Syntax:
$data = ["column_name3"=>$value3]; $where = ['column_name = ?' => $value, 'column_name2 = ?' => $value2]; $updatedRows=$connection->update($tableName, $data, $where);
Example:
$data = ["status"=>1]; $where = ['customer_id = ?' => '22', 'order_id = ?' => '455']; $updatedRows=$connection->update($tableName, $data, $where);
Delete Queries
Syntax:
$connection->delete( $tableName, ['column_name = ?' => $value, 'column_name2 = ?' => '0'] );
Example :
$connection->delete( $tableName, ['customer_id = ?' => '22', 'status = ?' => '0'] );
1
0
hunain
23rd December 2024
Good website! I truly love how it is easy on my eyes it is. I am wondering how I might be notified whenever a new post has been made. I have subscribed to your RSS which may do the trick? Have a great day! slot online
Write a comment ...
Post comment
Cancel
jack
19th December 2024
You can be permitted to content artists, however it is not connections, in the event they are simply endorsed and additionally relating to question. 트립닷컴 할인
Write a comment ...
Post comment
Cancel
jack
17th December 2024
Unlock a great welcome bonus on Melbet with the "MEGA200" promo code. Receive up to 200€ for sports betting and enjoy a 130% bonus on your first deposit to maximize your betting rewards. comment créer son code promo melbet
Write a comment ...
Post comment
Cancel
jack
16th December 2024
Get started on 1xBet with the 1XBONO25 code for a 100% bonus up to $130. New users can boost their betting experience and try out sports or casino games with this valuable bonus. código promocional 1xbet perú
Write a comment ...
Post comment
Cancel
jack
15th December 2024
The 1WinMax500 code offers new players a 500% bonus up to $1025 and 70 free spins. No verification required to claim your bonus, and enjoy regular promotions and special rewards at 1Win. 1win bonus code no deposit
Write a comment ...
Post comment
Cancel
Nguyen
14th December 2024
Kết quả trúng thưởng Xổ Số An Giang được xác định bằng cách đem so sánh dãy số trong tờ vé số với kết quả mở thưởng của nhà đài. dự đoán xsag
Write a comment ...
Post comment
Cancel
jack
14th December 2024
Le meilleur code promo 1xBet: 1X200FOX, vous pourrez obtenir un bonus de bienvenue 100% jusqu'à $130 sur le sport et jusqu'à $1,500 de bonus et 150 tours gratuits sur le casino. Le bonus sera activé seulement une fois que vous aurez effectué un premier paiement. Ce bonus de bienvenue sera équivalent à 100 % sur votre compte, jusqu’à environ $130 ou un montant équivalent à leur devise. 1xbet congo
Write a comment ...
Post comment
Cancel
Nguyen
14th December 2024
có không ít người vẫn chưa biết cách chơi XSTN. Vậy hãy cùng Dự đoán xổ số An Giang tìm hiểu cách chơi và kinh nghiệm tăng khả năng trúng thưởng nhé dự đoán xổ số an giang
Write a comment ...
Post comment
Cancel
jack
7th December 2024
I just got here onto your blog page whereas keeping solely to some degree submits. Decent strategy for following that, I will be bookmarking without delay grab a accomplish soars. mix parlay
Write a comment ...
Post comment
Cancel
jack
5th December 2024
This really is therefore stunning as well as innovative. I simply adore the actual colours as well as whomever will get this within the postal mail is going to be grinning. bandar togel online
Write a comment ...
Post comment
Cancel
jack
3rd December 2024
This really which means delightful not to mention original. I just absolutely adore typically the styles not to mention anyone who will become it again in your mailing could be cheerful. login slot88
Write a comment ...
Post comment
Cancel
jack
30th November 2024
Actually sound, great, fact-filled details the following. The content Don't ever fail, understanding that undoubtedly is valid the following at the same time. An individual constantly lead to an appealing examine. Is it possible to notify Now i'm pleased?: )#) Maintain the nice posts. slot gacor gampang menang
Write a comment ...
Post comment
Cancel
jack
30th November 2024
Actually sound, great, fact-filled details the following. The content Don't ever fail, understanding that undoubtedly is valid the following at the same time. An individual constantly lead to an appealing examine. Is it possible to notify Now i'm pleased?: )#) Maintain the nice posts.
Write a comment ...
Post comment
Cancel
jack
23rd November 2024
The Debox digital book offers strategies to recognize and process emotions effectively, as outlined at https://debox.Co/debox-digital-book/. Readers learn techniques for identifying triggers of emotional pain, a crucial step for self-healing. The book provides exercises to practice mindfulness and self-compassion, essential for addressing negative self-talk. Debox Method healing trauma process
Write a comment ...
Post comment
Cancel
SHANU
20th November 2024
Fine place, a an exceptionally amazing webpage that there is at this point, stick to beneficial financial job, shall be to come back. iptv uk
Write a comment ...
Post comment
Cancel
Please rotate your device
We don't support landscape mode on your device. Please rotate to portrait mode for the best view of our site