CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Freid001
Freid001

Reputation: 2850

Doctrine join entities

I'm trying to make some efficient improvements when querying for an entity which has a relationship to another entity.

Entity A:

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

/**
 * @var string
 * @ORM\Column(name="name", type="string", length=225)
 * @Assert\NotBlank(message="Please enter a name for your profile.")
 */
protected $display_name;

/**
 * @ORM\OneToOne(targetEntity="Posts", mappedBy="profile", cascade={"all"}, fetch="LAZY")
 */
protected $posts;

Entity B:

/**
 * @var integer
 * @ORM\Column(name="profile_id", type="integer")
 * @ORM\Id
 */
protected $profile_id;

/**
 * @ORM\OneToOne(targetEntity="Profile", inversedBy="posts")
 * @ORM\JoinColumn(name="profile_id", referencedColumnName="id")
 */
protected $profile;

/**
 * @var string
 * @ORM\Column(name="content", type="string")
 */
protected $content;

When I count the number of queries being executed I get two, I guess being doctrine runs two separate queries for each entity even through they have a relationship.

I am currently fetching entity A like so: 

public function fetchById($id)
{
    return $this->createQueryBuilder('p')
        ->where('p.id = :id')
        ->setParameter('id', $id)
        ->getQuery()
        ->getOneOrNullResult();
}

And then calling entity B like so:

 $profile = $profileRepository->fetchById($user->getUserId());
 $lastpost = $profile->getPosts()[0];

But I want to be able to join the second entity in this query so that I can just call one query rather than two. I was hoping to do something like this: 

public function fetchById($id)
{
    return $this->createQueryBuilder('p')
        ->select('p','pp')
        ->leftJoin(Posts::class, 'pp', \Doctrine\ORM\Query\Expr\Join::WITH, 'p.id = pp.profile_id')
        ->where('p.id = :id')
        ->setParameter('id', $id)
        ->getQuery()
        ->getResults();
}

However the left join returns an array of two entities. This is not what I am wanting, because I want to still be able to call for example the getPosts() method in entity A.

I essentially want to populate entity A, including all the related entities. But by only executing one query rather than two, is this possible in doctrine?

Upvotes: 2

Views: 7097

Answers (1)

kero
kero

Reputation: 10638

Some time ago I had a similar scenario (not with 1-1 but 1-n though), which I solved like this:

// ...Repository
public function findAllForUserWithAll(\AppBundle\Entity\User $u)
{
    $query = $this->getEntityManager()->createQueryBuilder('ca1')
        ->add('select', 'c, s, i, m')
        ->add('from', 'AppBundle:Contact c')
        ->leftJoin('c.skills', 's')
        ->leftJoin('c.interests', 'i')
        ->leftJoin('c.metAt', 'm')
        ->where('c.user = :user')
        ->orderBy('c.lastname', 'ASC')
        ->setParameters([
            'user' => $u,
        ])
        ->getQuery();

    return $query->getResult();
}

This was in Symfony 2.8.* with Doctrine ^2.4.8 - and the result is one query with 3 joins (instead of 4 queries). Probably not the best code and honestly all the magic happened under the hood. Also your code looks alot alike. But maybe this is expected due to fetch="LAZY" (on entity A) - which my entity does not have?

Upvotes: 2

Related Questions