Reputation: 2982
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.
Upvotes: 1
Views: 346
Reputation: 2576
Instead of using:
->innerJoin('\\Entity\\Document', 'd', \Doctrine\ORM\Query\Expr\Join::ON, 'd.user_id = u.id')
Try using this:
->innerJoin('u.documents', 'd', \Doctrine\ORM\Query\Expr\Join::ON, 'd.user_id = u.id')
This is because Doctrine needs to know to which field in user
the documents
have to be joined. Knowing the field Doctrine will fetch the target entity and that way Doctine directly knows the class.
Upvotes: 3