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
SHANU
5th February 2025
I can see that you are an expert at your field! I am launching a website soon, and your information will be very useful for me.. Thanks for all your help and wishing you all the success in your business. 爱思助手
Write a comment ...
Post comment
Cancel
hunain
5th February 2025
very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! 4d
Write a comment ...
Post comment
Cancel
hunain
5th February 2025
very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! 먹튀검증 very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! 해외문자
Write a comment ...
Post comment
Cancel
hunain
5th February 2025
very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! 토토사이트 very interesting post.this is my first time visit here.i found so mmany interesting stuff in your blog especially its discussion..thanks for the post! 먹튀검증
Write a comment ...
Post comment
Cancel
Nimra
4th February 2025
This is very educational content and written well for a change. It's nice to see that some people still understand how to write a quality post!除蟲服務
Write a comment ...
Post comment
Cancel
jack
2nd February 2025
I just concept it will be an example to write incase everyone else was basically experiencing difficulity considering and yet I'm sure a little bit of suspicious considerably more than simply morning allowed to position manufacturers not to mention talks about concerning in this case. 먹튀폴리스
Write a comment ...
Post comment
Cancel
Nimra
2nd February 2025
Wow! Such an amazing and helpful post this is. I really really love it. It's so good and so awesome. I am just amazed. I hope that you continue to do your work like this in the future alsobest coffee maker 2025
Write a comment ...
Post comment
Cancel
jack
30th January 2025
Thank you again for all the knowledge you distribute,Good post. ink slot gacor Thank you again for all the knowledge you distribute,Good post. agenolx Thank you again for all the knowledge 먹튀없는사이트 Thank you again for all the knowledge you distribute, 토토사이트
Write a comment ...
Post comment
Cancel
SHANU
27th January 2025
I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. 토토사이트
Write a comment ...
Post comment
Cancel
SHANU
27th January 2025
I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. situs toto 4d I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. situs toto 4d
Write a comment ...
Post comment
Cancel
SHANU
27th January 2025
I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. situs toto togel 4D I just couldn't leave your website before telling you that I truly enjoyed the top quality info you present to your visitors? Will be back again frequently to check up on new posts. situs toto
Write a comment ...
Post comment
Cancel
Kunal
27th January 2025
Experience the ultimate pleasure with Chanakyapuri Escorts. Our Escorts in Chanakyapuri are selected to provide the best satisfaction. Book now for an unforgettable encounter.
Write a comment ...
Post comment
Cancel
Nguyen
23rd January 2025
68 game bài have games to suit every player when playing Casino Online. Not only do we have hundreds of Casino games, we also offer many bonuses and promotions to our members. Readmore: 68 game bài
Write a comment ...
Post comment
Cancel
SHANU
18th January 2025
A very awesome blog post. We are really grateful for your blog post. You will find a lot of approaches after visiting your post. Salam jp
Write a comment ...
Post comment
Cancel
SHANU
18th January 2025
I admire what you have done here. I like the part where you say you are doing this to give back but I would assume by all the comments that this is working for you as well. vanillagift.com
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