Doctrine - relation of three tables

Question

I have trouble with writing Doctrine Query Builder.

I have a Posts table which is related to the AccountToken table like:

class Posts

/**
 * @ORM\ManyToOne(targetEntity="App\Entity\AccountToken")
 * @ORM\JoinColumn(name="account_token_id", referencedColumnName="uuid", nullable=false)
 */
protected $accountTokenId;

I am writing a query builder where I want to pull all the results which are related to that table via UUID and have a specific status. The type value is defined in the AccountToken table.

In that table, type has relation also and it's defined in AccountToken table:

 class AccountToken
    
    /**
     * @ORM\ManyToOne(targetEntity="App\Entity\Types")
     * @ORM\JoinColumn(name="type_id", referencedColumnName="id", nullable=false)
     */
    protected $type;

It's a tricky one for me because I have to relations.

Does someone know how to pull the results:

"Return me posts which have the type='success' value".

 $this->createQueryBuilder('p')
            ->select('p')
            ->join('App\Entity\AccountToken', 'at', Expr\Join::WITH, 'at.uuid = p.accountTokenId')
            ->join('App\Entity\ChannelType', 'ct', Expr\Join::WITH, 'ct.id = at.type')
            ->where('p.accountTokenId = :accountTokenId')
            ->setParameter('successStatus', Types::STATUS_SUCCESS)
            ->getQuery()
            ->getResult();

This is my draft of how I am trying to accomplish it but as I am new with Doctrine, don't have an idea how to join a table that has value in its related table. It's confusing.

Answer 1

Firstly you don't have any parameter successStatus in your query.

I think you over complicate the things.

return $this->createQueryBuilder('p')
            ->leftJoin('p.accountToken', 'a')
            ->leftJoin('a.type', 'ct')
            ->where('ct.successStatus = :successStatus')
            ->setParameter('successStatus', Types::STATUS_SUCCESS)
            ->getQuery()
            ->getResult()
            ;