CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdoctrine
Sergii Nester
Sergii Nester

Reputation: 474

Doctrine adds unnecessary self-join

So, here is the problem i'm facing right now. I'm trying to do plain simple select from the one table and for some reason Doctrine adds self-referencing join to SQL query. I'm pretty sure that it will not affect query speed, nevertheless i'd prefer to get rid of it.

Here is how I'm building the query:

    $qb = $this->createQueryBuilder('ol');

    $qb->select('o')
        ->from('ApplicationPersonBundle:Occupation', 'o')
        ->where($qb->expr()->eq('o.person', '?1'))
        ->addOrderBy('o.toYear', 'DESC')
        ->addOrderBy('o.toMonth', 'DESC')
        ;

    $qb->setParameter(1, $person);

And here's what Doctrine generates: 

SELECT 
  o0_.id AS id0, 
  o0_.description AS description1, 
  o0_.from_month AS from_month2, 
  o0_.from_year AS from_year3, 
  o0_.to_month AS to_month4, 
  o0_.to_year AS to_year5, 
  o0_.person_id AS person_id6, 
  o0_.company_id AS company_id7, 
  o0_.position_id AS position_id8 
FROM 
  occupation o1_, 
  occupation o0_ 
WHERE 
  o0_.person_id = ? 
ORDER BY 
  o0_.to_year DESC, 
  o0_.to_month DESC

And I have no idea where that "FROM occupation o1_, occupation o0_" comes from. And it's even never uses that o1_ alias.

Meaningful part of the entity looks like this, no self-references or whatnot:

/**
 * Occupation
 *
 * @ORM\Table(name="occupation")
 * @ORM\Entity(repositoryClass="Application\Bundle\PersonBundle\Entity\OccupationRepository")
 */
class Occupation
{
/**
 * @var integer
 *
 * @ORM\Column(name="id", type="integer")
 * @ORM\Id
 * @ORM\GeneratedValue(strategy="AUTO")
 */
private $id;

/**
 * @var Person
 *
 * @ORM\OneToOne(targetEntity="Person")
 * @ORM\JoinColumn(name="person_id", referencedColumnName="id")
 */
protected $person;

}

Upvotes: 2

Views: 90

Answers (2)

Cerad
Cerad

Reputation: 48893

You are getting the qb from inside the occupation repository correct? No need to explicitly add select or from for the occupation entity. So something like this should work:

$qb = $this->createQueryBuilder('o'); // Change from o1, this is the occupation alias

$qb->
    ->where($qb->expr()->eq('o.person', '?1'))
    ->addOrderBy('o.toYear', 'DESC')
    ->addOrderBy('o.toMonth', 'DESC')
    ;

$qb->setParameter(1, $person);

Upvotes: 3

Mateusz Sip
Mateusz Sip

Reputation: 1280

Could you show us the second entity? Maybe it has also a JoinColumn annotation?

Upvotes: 0

Related Questions