如何在Symfony 3.3的Doctrine中编写DQL关联查询？

Hey there! Let's get that query working for you. I'll walk you through a few reliable methods to translate your raw SQL into Doctrine-compatible code, starting with the most recommended approaches.

First, let's assume you have two entity classes set up: Article (mapping to the Articles table) and Packet (mapping to the Packets table). Make sure your entity fields are properly mapped to the database columns (e.g., Article has an id field, Packet has an article_id field).

Method 1: Using Doctrine QueryBuilder (Recommended) #

QueryBuilder is Doctrine's preferred way to build queries programmatically—it's readable and leverages your entity mappings.

Case 1: If your Packet entity has a direct association to Article #

If you've set up a ManyToOne association in your Packet entity (linking to Article), your code will look like this:

// In your controller or repository class
$em = $this->getDoctrine()->getManager();

$queryBuilder = $em->createQueryBuilder();
$results = $queryBuilder
    ->select('p.articleId', 'a.description') // Replace with your actual entity property names
    ->from('AppBundle:Packet', 'p') // Update to your entity's namespace (e.g., AppBundle\Entity\Packet)
    ->join('p.article', 'a') // 'p.article' refers to the association property in Packet
    ->getQuery()
    ->getResult();

Case 2: If you only have the article_id field in Packet (no association) #

If you haven't set up an entity association yet, you can explicitly define the join condition:

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

$queryBuilder = $em->createQueryBuilder();
$results = $queryBuilder
    ->select('p.articleId', 'a.description')
    ->from('AppBundle:Packet', 'p')
    ->join(
        'AppBundle:Article', 
        'a', 
        \Doctrine\ORM\Query\Expr\Join::WITH, 
        'p.articleId = a.id'
    )
    ->getQuery()
    ->getResult();

Method 2: Using DQL (Doctrine Query Language) #

DQL is similar to SQL but uses entity names and properties instead of table/column names. It's great if you prefer writing query strings directly:

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

$dql = 'SELECT p.articleId, a.description
        FROM AppBundle:Packet p
        JOIN AppBundle:Article a WITH p.articleId = a.id';

$query = $em->createQuery($dql);
$results = $query->getResult();

Method 3: Raw SQL (Fallback Option) #

If you need to use your exact SQL statement (not recommended unless necessary), you can execute a native query:

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

$sql = 'SELECT p.article_id, a.description FROM Articles a JOIN Packets p ON p.article_id=a.id';
$stmt = $connection->prepare($sql);
$stmt->execute();

// Fetch results as an associative array
$results = $stmt->fetchAll();

Quick Troubleshooting Tip #

Double-check your entity mappings to make sure:

• The Article entity's id field is correctly mapped to the id column in the Articles table.
• The Packet entity's articleId field matches the article_id column in the Packets table.
• If using associations, the ManyToOne annotation in Packet points to the correct Article entity.

内容的提问来源于stack exchange，提问作者Ale TheFe