Doctrine2 Joining tables with WITH clause

Question

I have one User entity, and one UserData entity which links to User through a foreign key.

The User entity has the following mapping:

    /**
 * @ORM\OneToOne(targetEntity="UserData", cascade={"persist", "remove"})
 * @var integer
 */
protected $userData;

And UserData has a simple ID field:

    /**
 * @ORM\Id
 * @ORM\Column(type="integer")
 * @ORM\GeneratedValue(strategy="AUTO")
 * @var integer
 */
protected $id;

Ok; I want to retrieve one User entity whose UserData has field 'NIN' value 'A00000000'; I'm trying to use this DQL query:

SELECT u FROM eCommerceUserBundle:User u JOIN eCommerceUserBundle:UserData ud ON u.userData = ud.id WHERE ud.NIN = :NIN

But I get this error:

Expected Doctrine\ORM\Query\Lexer::T_WITH, got 'ON'

So...I try to quit the ON clause, like this:

SELECT u FROM eCommerceUserBundle:User u JOIN eCommerceUserBundle:UserData ud WHERE ud.NIN = :NIN

Now the problem is the raw query that Doctrine is making:

    SELECT 
  u0_.id AS id0, 
  u0_.email AS email1, 
  u0_.password AS password2, 
  u0_.created AS created3, 
  u0_.lastLogin AS lastLogin4, 
  u0_.confirmed AS confirmed5, 
  u0_.blocked AS blocked6, 
  u0_.particularData_id AS particularData_id7, 
  u0_.institutionalData_id AS institutionalData_id8, 
  u0_.administratorData_id AS administratorData_id9 
FROM 
  user u0_ 
  INNER JOIN userData u1_ ON (u1_.NIN = ?)

So it appears that Doctrine is pushing my WHERE clause into ON clause...

What can I do?

Uriziel Uriziel · Accepted Answer · 2013-08-22T10:36:24

DQL QUERY should go like this:

SELECT u, ud 
FROM eCommerceUserBundle:User u 
JOIN u.userData ud
WHERE ud.NIN = :NIN

You can either select users along with his userData or without it by removing ud from SELECT clause - array of User objects will be returned nonetheless, but with userData filled in from the beginning - not selecting it will first fire up new query to the DB on $user->getUserData().

Doctrine2 Joining tables with WITH clause

1 Answers