实体的Symfony子查询

Question

Have a problem with subquery with symfony.

What I try to do - I have a table with users and a table with posts.

Posts                  Users
id|author|content     id|username

I want create subquery to get user name by id.

/**
 * @return array
 */
public function findAll()
{
    return $this->getEntityManager()->createQuery(
        'SELECT a, (SELECT u.username
         FROM BackendBundle:User u WHERE u.id = a.author) as authorName
         FROM BackendBundle:Article a'
    )->getResult();
}

Result:

What am I doing wrong? What is the best way to join column from other table by id? Maybe i can use annotations?

Thx for any help.

Answer 1

You don't need a subquery here, what you need is a simple (INNER) JOIN to join Users with their Articles.

$em->createQuery("SELECT a FROM Article JOIN a.author'");

You don't even need an on clause in your join, because Doctrine should already know (through annotations on your entities or a separate yaml file), that the article.author field relates to user.id.

Edit:

I assume you have a User entity that is One-To-Many related to the Article entity.

class User
{
    // ...
    /**
     * @OneToMany(targetEntity="Article", mappedBy="author")
     */
    private $articles;
    // ...
}

class Article
{
    // ...
    /**
     * @var User 
     * @ManyToOne(targetEntity="User", inversedBy="articles")
     */
    private $author;
    // ...
}

Please refer to doctrines association mapping documentation: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/association-mapping.html