Symfony 3 - Doctrine错误的查询结构 - 多表

Question

I have a problem with Doctrine woring in my Symfony project. That's my code:

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Doctrine\Common\Persistence\ObjectManager;
use Factory\MainBundle\Entity\Lastauction;
use Factory\MainBundle\Entity\Offers;

class AuctionListController extends Controller
{
    private $em;    
    private $qb;
    private $offer;

    public function getAuctionListAction()
    {
        $this->em = $this->getDoctrine()->getEntityManager();
        $this->qb = $this->em->createQueryBuilder();
        getAllAccount();
        for($offset=0; $offset<20000; $offset++)
        {
            $this->insertOffers();
        }   
        return new Response('Offers list generated');
    }   

    private function getAllAccount()
    {
        $this->qb->select('ac.mail')
            ->from('FactoryMainBundle:Account', 'ac');
        $result = $this->qb->getQuery()->getResult();
        $this->accounts = array_column($result, 'mail');
    }   


    private function insertOffers() 
    {
        foreach($this->offers as $id => $offer)
        {
            if($this->checkIfExsistOffer($offer))
            {
                echo $offer .'<br>';
                $offerObj = new Offers();
                $offerObj->setOffer($offer);
                $offerObj->setPage(2);
                $this->em = $this->getDoctrine()->getManager();
                $this->em->persist($offerObj);  
                if (($id % $this->batchSize) == 0) 
                {
                    $this->em->flush();
                    $this->em->clear();
                }   
            }
        }
        $this->em->flush();
        $this->em->clear();
        $this->offers = array();
    }   

    private function checkIfExsistOffer($offerUrl)
    {
        $this->qb->select('o.id')
            ->from('FactoryMainBundle:Offers', 'o')         
            ->where("o.offer=:offer")
            ->setParameter('offer', $offerUrl);
        $result = $this->qb->getQuery()->getResult();
        return (isset($result['0']['id'])) ? 1 : 0;
    }

}

And I get this error:

[Semantical Error] line 0, col 134 near 'o WHERE o.of': Error: 'o' is already defined.

[2/2] QueryException: [Semantical Error] line 0, col 134 near 'o WHERE o.of': Error: 'o' is already defined.
[1/2] QueryException: SELECT o.id FROM FactoryMainBundle:Account ac, FactoryMainBundle:Lastauction la, FactoryMainBundle:Offers o, FactoryMainBundle:Offers o WHERE o.offer='123'

Why Doctrine takes tables from other queries?

Answer 1

Breakdown of your codeflow regarding $this->qb:

1) In getAuctionListAction: $this->qb = $this->em->createQueryBuilder();

2) In getAllAccount:

$this->qb->select('ac.mail')
         ->from('FactoryMainBundle:Account', 'ac');

3) In checkIfExsistOffer (through insertOffers), 20.000x due to your loop in getAuctionListAction:

$this->qb->select('o.id')
     ->from('FactoryMainBundle:Offers', 'o')
     ->where("o.offer=:offer")
     ->setParameter('offer', $offerUrl);

Long Story short: You're adding multiple tables to your $this->qb querybuilder object (through $this->qb->from(...)), that's why they all get queried.