php 使用 Doctrine 2 执行原始 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3325012/
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
Execute raw SQL using Doctrine 2
提问by Jiew Meng
I want to execute raw SQL using Doctrine 2
我想使用 Doctrine 2 执行原始 SQL
I need to truncate the database tables and initialize tables with default test data.
我需要截断数据库表并使用默认测试数据初始化表。
采纳答案by Jiew Meng
I found out the answer is probably:
我发现答案可能是：
A NativeQuery lets you execute native SQL, mapping the results according to your specifications. Such a specification that describes how an SQL result set is mapped to a Doctrine result is represented by a ResultSetMapping.
NativeQuery 允许您执行本机 SQL，根据您的规范映射结果。这种描述 SQL 结果集如何映射到 Doctrine 结果的规范由 ResultSetMapping 表示。
Source: Native SQL.
来源：本机 SQL。
回答by Jason Swett
Here's an example of a raw query in Doctrine 2 that I'm doing:
这是我正在做的 Doctrine 2 中的原始查询示例：
public function getAuthoritativeSportsRecords()
{
$sql = "
SELECT name,
event_type,
sport_type,
level
FROM vnn_sport
";
$em = $this->getDoctrine()->getManager();
$stmt = $em->getConnection()->prepare($sql);
$stmt->execute();
return $stmt->fetchAll();
}
回答by orourkedd
//$sql - sql statement
//$em - entity manager
$em->getConnection()->exec( $sql );
回答by fernandodof
I got it to work by doing this, assuming you are using PDO.
假设您使用的是 PDO，我通过这样做让它工作。
//Place query here, let's say you want all the users that have blue as their favorite color
$sql = "SELECT name FROM user WHERE favorite_color = :color";
//set parameters
//you may set as many parameters as you have on your query
$params['color'] = blue;
//create the prepared statement, by getting the doctrine connection
$stmt = $this->entityManager->getConnection()->prepare($sql);
$stmt->execute($params);
//I used FETCH_COLUMN because I only needed one Column.
return $stmt->fetchAll(PDO::FETCH_COLUMN);
You can change the FETCH_TYPE to suit your needs.
您可以更改 FETCH_TYPE 以满足您的需要。
回答by Eric Leschinski
How to execute a raw Query and return the data.
如何执行原始查询并返回数据。
Hook onto your manager and make a new connection:
连接到您的经理并建立新的连接：
$manager = $this->getDoctrine()->getManager();
$conn = $manager->getConnection();
Create your query and fetchAll:
创建您的查询并 fetchAll：
$result= $conn->query('select foobar from mytable')->fetchAll();
Get the data out of result like this:
从结果中获取数据，如下所示：
$this->appendStringToFile("first row foobar is: " . $result[0]['foobar']);
回答by Toby Batch
I had the same problem. You want to look the connection object supplied by the entity manager:
我有同样的问题。您想查看实体管理器提供的连接对象：
$conn = $em->getConnection();
You can then query/execute directly against it:
然后，您可以直接对其进行查询/执行：
$statement = $conn->query('select foo from bar');
$num_rows_effected = $conn->exec('update bar set foo=1');
See the docs for the connection object at http://www.doctrine-project.org/api/dbal/2.0/doctrine/dbal/connection.html
在http://www.doctrine-project.org/api/dbal/2.0/doctrine/dbal/connection.html查看连接对象的文档
回答by badzilla
In your model create the raw SQL statement (example below is an example of a date interval I had to use but substitute your own. If you are doing a SELECT add ->fetchall() to the execute() call.
在您的模型中创建原始 SQL 语句（下面的示例是我必须使用但替换您自己的日期间隔的示例。如果您正在执行 SELECT 添加 ->fetchall() 到 execute() 调用。
$sql = "DELETE FROM tmp
WHERE lastedit + INTERVAL '5 minute' < NOW() ";
$stmt = $this->getServiceLocator()
->get('Doctrine\ORM\EntityManager')
->getConnection()
->prepare($sql);
$stmt->execute();
回答by Alexis Finn
You can't, Doctrine 2 doesn't allow for raw queries. It may seem like you can but if you try something like this:
你不能，Doctrine 2 不允许原始查询。看起来你可以，但如果你尝试这样的事情：
$sql = "SELECT DATE_FORMAT(whatever.createdAt, '%Y-%m-%d') FORM whatever...";
$em = $this->getDoctrine()->getManager();
$em->getConnection()->exec($sql);
Doctrine will spit an error saying that DATE_FORMAT is an unknown function.
Doctrine 会抛出一个错误，指出 DATE_FORMAT 是一个未知函数。
But my database (mysql) does know that function, so basically what is hapening is Doctrine is parsing that query behind the scenes (and behind your back) and finding an expression that it doesn't understand, considering the query to be invalid.
但是我的数据库 (mysql) 确实知道该函数，所以基本上发生的是 Doctrine 在幕后（和在你背后）解析该查询并找到它不理解的表达式，考虑到查询无效。
So if like me you want to be able to simply send a string to the database and let it deal with it (and let the developer take full responsibility for security), forget it.
因此，如果像我一样，您希望能够简单地向数据库发送一个字符串并让它处理它（并让开发人员承担全部安全责任），那就忘记它吧。
Of course you could code an extension to allow that in some way or another, but you just as well off using mysqli to do it and leave Doctrine to it's ORM buisness.
当然，您可以编写一个扩展以某种方式允许这样做，但是您最好使用 mysqli 来完成它并将 Doctrine 留给它的 ORM 业务。
