createQueryBuilder / Join Column - Symfony

Question

我有一个麻烦来实现这个SQL请求:

SELECT *
FROM project
LEFT JOIN user 
ON project.idAuthor=user.id 
WHERE project.isVisible = 1 AND
user.role = 'agency'

到一个简单的Symfony查询生成器:

 $query = $this->createQueryBuilder('p')
            ->leftJoin('WebAwardsBundle:User', 'u')
            ->where('p.isVisible = 1')
            ->andwhere("u.role = 'agency'")
            ->orderBy('p.id', 'DESC')
            ->getQuery();

这个查询的响应给了我所有的项目，包括项目当角色!==代理…

我不知道该把ON project.idAuthor=user.id放在哪里

映射:

项目:

    /**
 * Project
 *
 * @ORMTable(name="project")
 *@ORMEntity(repositoryClass="WebAwardsBundleRepostoryProjectRepository")
 */
class Project
{
    ...
/**
 * @var int
 *
 * @ORMManyToOne(targetEntity="User", inversedBy="projects")
 * @ORMJoinColumn(name="idAuthor", referencedColumnName="id")
 */
private $idAuthor;
...

User:

/**
 * User
 *
 * @ORMTable(name="user")
 * @ORMEntity(repositoryClass="WebAwardsBundleRepositoryUserRepository")
 */
class User implements UserInterface, Serializable{
...
 /**
     * @ORMOneToMany(targetEntity="Project", mappedBy="idAuthor")
     */
    private $projects;
...

Answer 1

实现这个SQL请求的正确答案:

SELECT *
FROM project
LEFT JOIN user 
ON project.idAuthor=user.id 
WHERE project.isVisible = 1 AND
user.role = 'agency'

是连接实体(项目)的正确列。在本例中，idAuthor to user):

$query = $this->createQueryBuilder('p')
        ->join('p.idAuthor', 'u')
        ->where('p.isVisible = 1')
        ->andWhere("u.role = 'agency'")
        ->orderBy('p.id', 'DESC')
        ->getQuery();