CODEWITHSUNDEEP
symfonydoctrineentity
Karro
Karro

Reputation: 121

With ManyToOne relation entities, how may i access a foreign key id?

I'm trying to access to a foreign key stored in an entity using doctrine and querybuilder.

I got an entity named User which is linked to another entity called Client with a ManyToOne relationship. I wanted to build a querybuilder that get me the field client_id in user table, that match the id of a client.

My User Entity :

/**
* AppBundle\EntityAppBundle\Entity\User
*
*  @ORM\Table(name="user")
*  @ORM\Entity(repositoryClass="AppBundle\Repository\UserRepository")
*/

class User extends FOSUser
{
 /**
  * @var integer $id
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 */
protected $id;

/**
 * @var string
 *
 * @ORM\Column(name="nom", type="string", length=255)
 *
 *
 */
private $nom;
/**
 *
 * @ORM\ManyToMany(targetEntity="AppBundle\Entity\Etablissement", inversedBy="users")
 *
 */
private $etablissements;
/**
 *
 * @ORM\ManyToOne(targetEntity="Client", inversedBy="users")
 *
 */
private $client;

My Client Entity :

/**
* AppBundle\EntityAppBundle\Entity\Client
*
*  @ORM\Table(name="client")
*  @ORM\Entity()
*/
class Client{

/**
 * @var integer $id
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="IDENTITY")
 */
private $id;
/**
 * @var string
 *
 * @ORM\Column(name="nom", type="string", length=255)
 *
 *
 */
private $nom;
/**
 * @var string
 *
 * @ORM\Column(name="adresse", type="string", length=255)
 *
 *
 */

 /**
 *
 * @ORM\OneToMany(targetEntity="AppBundle\Entity\User", mappedBy="client", 
 cascade={"persist"}, orphanRemoval=true)
 *
 */
 private $users;

In my database, my entity user has the client_id in foreign key column.

So in my queryBuilder in UserRepository, I do :

public function findClientIdViaUserId($myUserId, $myClientID)
{
return $this->createQueryBuilder('e')
        ->from('AppBundle:User', 'i')
        ->join('AppBundle:Client', 'c')
        ->where('c.id = :myClientID')
        ->andWhere('e.id = :myUserId')
        ->setParameter('myuserId', $myUserId)
        ->setParameter('myClientId', $myClientID)
        ->getQuery()
        ->getOneOrNullResult();
}

I expect to get the id of the client_id for a user_id. Let's say that i wanted to get one client_id with the user_id called 1. With my queryBuilder i got an error like : [Syntax Error] line 0, col 67: Error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got ','

How may I process to get the client_id from the user_id ?

Thank you for your replies !

Upvotes: 0

Views: 408

Answers (2)

nyded
nyded

Reputation: 51

Why don't you use EntityManager ?

$em = $this->getDoctrine()->getManager();

$user = $em->getRepository('AppBundle:User')->find(YOURUSERID);

$client = $user->getClient();

Upvotes: 1

yTko
yTko

Reputation: 175

You made a mistake in chaining of Doctrine methods. You've already set an alias for User entity by calling createQueryBuilder('e'). Repository knows about entity it linked to. When you call from('AppBundle:User', 'i') - an alias for User entity is i now. That's why Doctrine is throwing an error about wrong syntax in resulting DQL.

So, try this piece of code:

return $this
    ->createQueryBuilder('e')
    ->join('e.client', 'c')
    ->where('c.id = :myClientID')
    ->andWhere('e.id = :myUserId')
    ->setParameter('myUserId', $myUserId)
    ->setParameter('myClientId', $myClientID)
    ->getQuery()
    ->getOneOrNullResult();

Upvotes: 1

Related Questions