How to Execute Raw SQL Queries Using Doctrine 2?

Executing Raw SQL with Doctrine 2

Doctrine 2 provides a convenient way to interact with a database using Object Relational Mapping (ORM). However, there may be situations where you need to execute raw SQL queries directly. This article demonstrates how to execute raw SQL using Doctrine 2.

Consider a scenario where you want to truncate database tables and initialize them with default test data. To achieve this, you can execute the following raw SQL queries:

TRUNCATE TABLE table_name;
INSERT INTO table_name (column1, column2, ...) VALUES (value1, value2, ...);

In Doctrine 2, you can execute raw SQL queries using the following steps:

1. Retrieve the Doctrine Entity Manager:

$em = $this->getDoctrine()->getManager();

2. Prepare the SQL statement:

$sql = "TRUNCATE TABLE table_name";
$stmt = $em->getConnection()->prepare($sql);

3. Execute the SQL statement:

$stmt->execute();

You can also retrieve the results of the query by using the fetchAll() method:

$results = $stmt->fetchAll();

Example

The following code snippet illustrates how to execute a raw SQL query in 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();
}   

The above is the detailed content of How to Execute Raw SQL Queries Using Doctrine 2?. For more information, please follow other related articles on the PHP Chinese website!