I've got a doctrine2 problem with a MySQL database:
I have a model User and a model Documents. Each User may have 0 or n Documents. Each Document is assigned to exactly one User. My models:
User
<?php namespace Entity; /** * @Entity(repositoryClass="Entity\Repository\UserRepository") * @Table(name="user") */ class User extends Object { /** * @Id @GeneratedValue(strategy="UUID") * @Column(type="string", length=36) * @var string */ protected $id; /** * @OneToMany(targetEntity="\Entity\Document", mappedBy="user") */ private $documents; public function __construct($options = array()) { $this->documents = new \Doctrine\Common\Collections\ArrayCollection; } } Document
<?php namespace Entity; /** * @Entity(repositoryClass="Entity\Repository\DocumentRepository") * @Table(name="document") */ class Document extends Object { /** * @Id @Column(type="string") * @var string */ protected $id; /** * @ManyToOne(targetEntity="\Entity\User", inversedBy="documents") * @JoinColumn(name="user_id", referencedColumnName="id") * @var User */ private $user; } Now I want to get the User of a given Document ID. The SQL-query would be:
SELECT u.* FROM `user` u INNER JOIN `document` d ON d.user_id = u.id WHERE d.id = 'mydocumentid' But this does't work:
$user = $queryBuilder ->select('u.*') ->from('\\Entity\\User', 'u') ->innerJoin('\\Entity\\Document', 'd', \Doctrine\ORM\Query\Expr\Join::ON, 'd.user_id = u.id') ->where('d.id = :documentId') ->setParameter('documentId', 'mydocumentid') ->setMaxResults(1) ->getQuery() ->getSingleResult(); Also the direct query doesn't work:
$query = $em->createQuery(' SELECT u.* FROM Entity\\User u INNER JOIN Entity\\Document d ON d.user_id = u.id WHERE d.id = "mydocumentid" '); Could you please help me to get this run?
Error message
[Semantical Error] line 0, col 66 near 'd ON d.user_id': Error: Identification Variable \Entity\Document used in join path expression but was not defined before. 
