Symfony新手求助：SQL转QueryBuilder及仓库选择问题

Hey there! Let's break this down for you step by step since you're new to Symfony—no worries, we've all stumbled through QueryBuilder at first.

First: Which Repository Should You Use? #

This boils down to your business intent:

• If you're trying to get a list of users (who have orders), putting this in UserRepository makes the most semantic sense. The end result is a collection of User entities, so keeping the query where you fetch users feels intuitive and aligns with how you'll likely use the data.
• You could write it in CommandeRepository too, but that's more focused on orders than the users themselves. Stick with UserRepository for clarity.

Converting Your SQL to QueryBuilder #

First, make sure your entities have the proper associations set up (this will make QueryBuilder way simpler than writing raw JOIN conditions):

• In your User entity, add a OneToMany relationship to Commande:

  /**
   * @ORM\OneToMany(targetEntity=Commande::class, mappedBy="user")
   */
  private $commandes;
  

• In your Commande entity, add a ManyToOne relationship back to User:

  /**
   * @ORM\ManyToOne(targetEntity=User::class, inversedBy="commandes")
   * @ORM\JoinColumn(nullable=false)
   */
  private $user;
  

With those associations in place, here's how to build the query in UserRepository:

// src/Repository/UserRepository.php
use Doctrine\ORM\EntityRepository;

class UserRepository extends EntityRepository
{
    public function findUsersWithOrders()
    {
        return $this->createQueryBuilder('u')
            // Select the user ID from commande and all user fields
            ->select('c.user_id', 'u')
            // Join users to their orders (uses the association we set up)
            ->innerJoin('u.commandes', 'c')
            // Add distinct to avoid duplicate user entries (since one user can have multiple orders)
            ->distinct()
            ->getQuery()
            ->getResult();
    }
}

A Quick Note on Your Original SQL #

Your original query uses a LEFT JOIN, but since you want users who have orders, an INNER JOIN is more appropriate—it automatically filters out users with no orders. If you really need the LEFT JOIN (e.g., to include orders that might not have a user attached), you can swap innerJoin for leftJoin and add a ->where('u.id IS NOT NULL') clause to filter out null user entries.

If You Did Want to Use CommandeRepository (Just for Reference) #

If for some reason you prefer to start from the Commande side, here's that version:

// src/Repository/CommandeRepository.php
use Doctrine\ORM\EntityRepository;

class CommandeRepository extends EntityRepository
{
    public function findUsersWithOrders()
    {
        return $this->createQueryBuilder('c')
            ->select('c.user_id', 'u')
            ->leftJoin('c.user', 'u')
            ->where('u.id IS NOT NULL')
            ->distinct()
            ->getQuery()
            ->getResult();
    }
}

Just remember: the UserRepository approach is cleaner for your specific use case of fetching users.

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