为什么学说要把这个实体和自己结合起来?(不是唯一的表/别名)

Question

我有一个名为" tile "的实体，它与另一个名为" coins "的实体有多对多关系。

/**
 * @ORMManyToMany(targetEntity="Coin")
 * @ORMJoinTable(name="coin",
 *      joinColumns={@ORMJoinColumn(name="tile_id", referencedColumnName="tile_id")},
 *      inverseJoinColumns={@ORMJoinColumn(name="coin_id", referencedColumnName="coin_id")}
 *      )
 **/
protected $coins;

我有一个页面，列出瓷砖和所有相关的硬币。页面上可能有很多硬币和瓷砖，使用延迟加载可以在单个页面上放置100-300个数据库查询。我试图通过使用leftJoin来避免这种情况。

public function getUserTiles($id)
{                    
        $qb = $this->createQueryBuilder('b')
       ->select('b', 'z')
       ->leftJoin('b.coins', 'z')
       ->leftJoin('z.userInfo', 'u')
       ->add('where', "b.userId = ".$id." AND b.status = 'COMPLETED'")
       ->add('orderBy', "b.checkinDate DESC");
    return $qb->getQuery()
              ->getResult();      
}

这会给我以下错误:

*[2/2] DBALException: An exception occurred while executing '
SELECT t0_.tile_id AS tile_id0, t0_.quilt_id AS quilt_id1, t0_.user_id AS user_id2, t0_.comment AS comment3, t0_.checkout_date AS checkout_date4, t0_.checkin_date AS checkin_date5, t0_.x AS x6, t0_.y AS y7, t0_.status AS status8, t0_.completed_neighbors AS completed_neighbors9, t0_.required_completed_neighbors AS required_completed_neighbors10, t0_.visible AS visible11, t0_.visible_date AS visible_date12, t0_.count_towards_coins AS count_towards_coins13, c1_.coin_id AS coin_id14, c1_.user_id AS user_id15, c1_.tile_id AS tile_id16, c1_.comment AS comment17, c1_.date_given AS date_given18, c1_.status AS status19, c1_.origin AS origin20, t0_.quilt_id AS quilt_id21, t0_.user_id AS user_id22, c1_.tile_id AS tile_id23, c1_.user_id AS user_id24
FROM tile t0_ **LEFT JOIN coin *c1_* ON t0_.tile_id = c1_.tile_id
LEFT JOIN coin *c1_* ON c1_.coin_id = c1_.coin_id**
LEFT JOIN user_info u2_ ON c1_.user_id = u2_.user_id
WHERE t0_.user_id = 14 AND t0_.status = 'COMPLETED'
ORDER BY t0_.checkin_date DESC':
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'c1_'* 

可以看到，它试图使用相同的别名两次加入硬币表。我做错了什么?

Answer 1

尝试:

$this->createQueryBuilder('b')
    ->select('b')
    ->addSelect('z')
    ->leftJoin('b.coins', 'z')
    ->leftJoin('z.userInfo', 'u')
    ->where("b.userId = :id AND b.status = 'COMPLETED'")
    ->orderBy('b.checkinDate', 'DESC')
    ->setParameter('id', $id)
    ->getQuery('b.checkinDate DESC')
    ->getSingleResult();

Answer 2

我通过将manymany关系更改为onetmany来修复此问题，因为它应该在第一个地方。