将原始 SQL 与 Doctrine 结合使用
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2774947/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using Raw SQL with Doctrine
提问by Levi Hackwith
I have some extremely complex queries that I need to use to generate a report in my application. I'm using symfony as my framework and doctrine as my ORM.
我有一些非常复杂的查询,需要使用它们在我的应用程序中生成报告。我使用 symfony 作为我的框架和教义作为我的 ORM。
My question is this:
我的问题是这样的:
What is the best way to pass in highly-complex sql queries directly to Doctrine without converting them to the Doctrine Query Language? I've been reading about the Raw_SQL
extension but it appears that you still need to pass the query in sections (like from()
). Is there anything for just dumping in a bunch of raw sql commands?
将高度复杂的 sql 查询直接传递给 Doctrine 而不将它们转换为 Doctrine 查询语言的最佳方法是什么?我一直在阅读有关Raw_SQL
扩展的信息,但似乎您仍然需要在部分(如from()
)中传递查询。有什么可以转储一堆原始 sql 命令的吗?
回答by Tom
$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute(" -- RAW SQL HERE -- ");
See the Doctrine API documentation for different execution methods.
请参阅 Doctrine API 文档以了解不同的执行方法。
回答by richsage
Yes. You can get a database handle from Doctrine using the following code:
是的。您可以使用以下代码从 Doctrine 获取数据库句柄:
$pdo = Doctrine_Manager::getInstance()->getCurrentConnection()->getDbh();
and then execute your SQL as follows:
然后按如下方式执行您的 SQL:
$query = "SELECT * FROM table WHERE param1 = :param1 AND param2 = :param2";
$stmt = $pdo->prepare($query);
$params = array(
"param1" => "value1",
"param2" => "value2"
);
$stmt->execute($params);
$results = $stmt->fetchAll();
You can use bound variables as in the above example.
您可以像上面的例子一样使用绑定变量。
Note that Doctrine won't automatically hydrate your results nicely into record objects etc, so you'll need to deal with the results being returned as an array, consisting of one array per row returned (key-value as column-value).
请注意,Doctrine 不会自动将您的结果很好地融合到记录对象等中,因此您需要处理作为数组返回的结果,每行返回一个数组(键值作为列值)。
回答by takeshin
I'm not sure what do you mean saying raw SQL, but you coud execute traditional SQL queries this way:
我不确定你说的原始 SQL是什么意思,但你可以这样执行传统的 SQL 查询:
...
// $this->_displayPortabilityWarning();
$conn = Doctrine_Manager::connection();
$pdo = $conn->execute($sql);
$pdo->setFetchMode(Doctrine_Core::FETCH_ASSOC);
$result = $pdo->fetchAll();
...
The following method is not necsessary, but it shows a good practice.
以下方法不是必需的,但它显示了一个很好的做法。
protected function _displayPortabilityWarning($engine = 'pgsql')
{
$conn = Doctrine_Manager::connection();
$driver = $conn->getDriverName();
if (strtolower($engine) != strtolower($driver)) {
trigger_error('Here we have possible database portability issue. This code was tested on ' . $engine . ' but you are trying to run it on ' . $driver, E_USER_NOTICE);
}
}
回答by Twelve47
You can also use Doctrine_RawSql(); to create raw SQL queries which will hydrate to doctrine objects.
你也可以使用 Doctrine_RawSql(); 创建原始 SQL 查询,这些查询将与学说对象结合。
回答by Nikola Petkanski
It should be noted, that Doctrine2 uses PDO as a base, thus I would recommend using prepared statements over plain old execute.
应该注意的是,Doctrine2 使用 PDO 作为基础,因此我建议使用准备好的语句而不是普通的旧执行。
Example:
例子:
$db = Doctrine_Manager::getInstance()->getCurrentConnection();
$query = $db->prepare("SELECT `someField` FROM `someTable` WHERE `field` = :value");
$query->execute(array('value' => 'someValue'));
回答by SMSM
Symfony insert raw sql using doctrine.
Symfony 使用学说插入原始 sql。
This in version Symfoney 1.3
这在 Symfoney 1.3 版本中
$q = Doctrine_Manager::getInstance()->getCurrentConnection();
$result = $q->execute($query);