使用Doctrine ORM转换原生SQL查询获取实体时结果异常求助

Hey there! Let's break down why your Doctrine query is only returning one result instead of the two you get with raw SQL, and fix it up step by step.

First: Fix the Critical Condition Mistake #

Your raw SQL targets r.userId = 2681, but your Doctrine query has ->andWhere('r.id = :user') — this is the biggest issue! You're comparing the Registration ID to your user identifier, not the user ID linked to the Registration.

Depending on how your Registration entity is mapped:

• If you have a ManyToOne association to User named user, use:

  ->andWhere('r.user = :user')
  

• If you directly map the userId column as a field in Registration, use:

  ->andWhere('r.userId = :userId')
  

Make sure your setParameter matches too — if you're passing a User entity, keep setParameter('user', $user); if you're passing a numeric ID, use setParameter('userId', $userId).

Second: Clean Up the QueryBuilder Order #

Doctrine's QueryBuilder works best when you define joins first, then add your filters. Your original code adds andWhere clauses before the corresponding joins, which can lead to unexpected logic. Here's the corrected, cleaner version:

return $this->createQueryBuilder('c')
    // Join the related entities first
    ->leftJoin('c.traineeships', 't')
    ->leftJoin('t.registrations', 'r')
    // Now apply the user filter
    ->andWhere('r.user = :user')
    ->setParameter('user', $user)
    ->getQuery()
    ->execute();

Bonus: Handle Duplicate Entities (If Needed) #

Doctrine automatically deduplicates identical Course entities in results (since a single course might be linked to multiple traineeships/registrations). If you want to match the raw SQL's behavior of returning rows even for duplicate courses, use array hydration instead of entity hydration:

return $this->createQueryBuilder('c')
    ->leftJoin('c.traineeships', 't')
    ->leftJoin('t.registrations', 'r')
    ->andWhere('r.user = :user')
    ->setParameter('user', $user)
    ->getQuery()
    // Return raw arrays instead of deduplicated entities
    ->getResult(\Doctrine\ORM\Query::HYDRATE_ARRAY);

Why This Works #

The original condition mismatch was filtering for registrations whose ID matched your user ID — which was only hitting one result. Fixing that condition alone should get you the two expected Course entities. Adjusting the join order makes the query logic clearer and aligns with how Doctrine processes joins.

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