Magento 2：运行自定义SQL查询

本文介绍了如何在Magento 2中编写/运行/执行自定义SQL查询Magento\Framework\App\ResourceConnection。为此，我们需要实例化类。您必须将此资源类注入模块的Block / Model / Controller类构造函数中。之后，您可以使用该对象运行自定义sql查询。

在此示例中，我将仅使用对象管理器实例化资源类。

假设，我有一个名为表mytest与领域id，name，age，和email。

CREATE TABLE IF NOT EXISTS `mytest` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` tinyint(3) NOT NULL,
  `email` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

以下示例代码将插入，更新，删除和从表中选择记录mytest。

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('mytest'); // the table name in this example is 'mytest'
 
// INSERT DATA
$name = 'Mukesh Chapagain';
$age = 99;
$email = 'mukesh@example.com';
$sql = "INSERT INTO " . $tableName . " (id, name, age, email) VALUES ('', '$name', $age, '$email')";
$connection->query($sql);
 
$sql = "INSERT INTO " . $tableName . " (id, name, age, email) VALUES ('', 'Hello World', 88, 'hello@example.com')";
$connection->query($sql);
 
// SELECT DATA
$sql = "SELECT * FROM " . $tableName;
$result = $connection->fetchAll($sql); 
echo '<pre>'; print_r($result); echo '</pre>';  
 
// UPDATE DATA
$id = 1; // table row id to update
$sql = "UPDATE " . $tableName . " SET name = 'Your Name', email = 'your-email@example.com' WHERE id = " . $id;
$connection->query($sql);
 
// DELETE DATA
$id = 1; // table row id to delete
$sql = "DELETE FROM " . $tableName . " WHERE id = " . $id;
$connection->query($sql);

在上面的代码中，当我们使用fetchAllfunction 运行select查询时，将获得以下输出。它返回包含表字段名称和值的多维数组。

Array
(
    [0] => Array
        (
            [id] => 1
            [name] => Mukesh Chapagain
            [age] => 99
            [email] => mukesh@example.com
        )
 
    [1] => Array
        (
            [id] => 2
            [name] => Hello World
            [age] => 88
            [email] => hello@example.com
        )
 
)

除了自己编写整个选择查询，我们还可以使用Magento和提供的SQL SELECT查询生成功能Zend_Db_Select。这是一个示例代码：

$objectManager = \Magento\Framework\App\ObjectManager::getInstance();
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('mytest'); // the table name in this example is 'mytest'
 
// SELECT DATA
$id = 2;
$fields = array('id', 'name');
$sql = $connection->select()
                  ->from($tableName) // to select all fields
                  //->from($tableName, $fields) // to select some particular fields                  
                  ->where('id = ?', $id)
                  ->where('age > ?', 30); // adding WHERE condition with AND
                     
$result = $connection->fetchAll($sql); 
echo '<pre>'; print_r($result); echo '</pre>'; 

您可以详细类看到这个Magento\Framework\DB\Select存在vendor/magento/framework/DB/Select.php。

/**
* Class for SQL SELECT generation and results.
*
* @method \Magento\Framework\DB\Select from($name, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select join($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinInner($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinLeft($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinNatural($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinFull($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinRight($name, $cond, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select joinCross($name, $cols = ‘*’, $schema = null)
* @method \Magento\Framework\DB\Select orWhere($cond, $value = null, $type = null)
* @method \Magento\Framework\DB\Select group($spec)
* @method \Magento\Framework\DB\Select order($spec)
* @method \Magento\Framework\DB\Select limitPage($page, $rowCount)
* @method \Magento\Framework\DB\Select forUpdate($flag = true)
* @method \Magento\Framework\DB\Select distinct($flag = true)
* @method \Magento\Framework\DB\Select reset($part = null)
* @method \Magento\Framework\DB\Select columns($cols = ‘*’, $correlationName = null)
*
*/

注意：如果要在自定义外部文件中运行脚本，则需要编写以下代码以实例化ObjectManager。否则，您可能会收到以下错误：

致命错误：找不到类'Magento \ Framework \ App \ ObjectManager'

因此，这是解决方案。在下面的代码中，我已eav_entity_type使用自定义sql脚本从表中选择了数据。

use Magento\Framework\App\Bootstrap;
 
//require __DIR__ . '/app/bootstrap.php'; // you can use this if your custom file is in your magneto root directory
 
$rootDirectory = '/var/www/html/magento2'; // YOUR MAGENTO ROOT DIRECTORY
require $rootDirectory . '/app/bootstrap.php';
 
$params = $_SERVER;
 
$bootstrap = Bootstrap::create(BP, $params);
 
$objectManager = $bootstrap->getObjectManager();
 
$resource = $objectManager->get('Magento\Framework\App\ResourceConnection');
$connection = $resource->getConnection();
$tableName = $resource->getTableName('eav_entity_type'); // accessing table 'eav_entity_type'
 
// SELECT DATA
$sql = "SELECT * FROM " . $tableName;
$result = $connection->fetchAll($sql); 
echo '<pre>'; print_r($result); echo '</pre>'; 

希望这可以帮助。谢谢。