CODEWITHSUNDEEP
phpmysqlsymfonydoctrine-ormdoctrine
RedactedProfile
RedactedProfile

Reputation: 2808

Optimizing a doctrine query to prevent additonal queries

I'm building a system that has an update wall, where you only see Friends posts. A "Friend" here is classified by a set of two users who have each added eachother to their friends list.

The friend association is handled very simply by a Doctrine ManyToMany association, like this:

User.php

//.. other declarations
/**
 * @var ArrayCollection
 * @ORM\ManyToMany(targetEntity="User")
 * @ORM\JoinTable(name="user_friends")
 */
protected $friends;
//.. more unrelated stuff

and in my query to get a users friends updates (including themselves)

UpdateRepository.php

/**
 * Get aggregated list of updates for a user and their friends in chronological order
 * @param User $user
 * @return array
 */
public function getWallUpdates(User $user)
{
    $qb = $this->getEntityManager()->createQueryBuilder('p');
    $qb->select(['p'])->from($this->getEntityName(), 'p');

    $criteria = $qb->expr()->andX();

    // Filter based on Friends
    $friends = [$user];
    foreach($user->getFriends() as $friend) // collect eligible friends
        if($friend->getFriends()->contains($user))
            $friends[] = $friend;

    $criteria->add('p.account IN (:friends)');
    $qb->setParameter('friends', $friends);

    // Filter based on Chronology

    // Filter based on Privacy
    $criteria->add('p.privacy IN (:privacy)');
    $qb->setParameter('privacy', [1, 3]);

    $qb->where($criteria);

    $query = $qb->getQuery();
    $result = $query->getResult();

    return $result;
}

Now this works but the problem is that the line if($friend->getFriends()->contains($user)) while necessary to know if $user exists in another users friends list, generates a new query for every single person in $users friends list.

I don't mind an extra query or two to accomplish this, but I need to talk about scale here. Potentially a person on this application could have hundreds of friends. I can't have hundreds of queries (tiny as they may be) executing each time this page loads, which could potentially be a lot.

What's the best possible solution for making this better?

For now I can continue development, as it does function correctly.

EDIT

for sake of brevity, I did in fact try going the EXTRA_LAZY route of adding EXTRA_LAZY to $friends ManyToMany declaration in my User entity. According to the documentation, if using the EXTRA_LAZY fetch mode, using Collection#contains(entity) wouldn't trigger a query.

CLEARLY the problem here stems from the fact that $users friends each need to have their friends list collected in order to check. And that makes sense me.

Still is there a way to bring this all into a single operation?

Upvotes: 3

Views: 798

Answers (1)

dk80
dk80

Reputation: 556

If you add joins for a user's friends and their friends when you fetch the $user object then it should cut out any lazy loading. So for example you could have a custom repository for the user as follows:

use Doctrine\ORM\EntityRepository;

class UserRepository extends EntityRepository {

    public function fetchFriendsOfFriends($userId) {
        return $this->_em->createQuery('SELECT u, f, ff FROM User u '
                . 'LEFT JOIN u.friends f '
                . 'LEFT JOIN f.friends ff '
                . 'WHERE u.id = :userId')
            ->setParameter('userId', $userId)
            ->getSingleResult();
    }
}

The get your user entity to use this repository:

/**
 * @ORM\Entity(repositoryClass="UserRepository")
 */
class User {
...

Then when you fetch your $user use the new fetch method:

$user = $em->getRepository('User')->fetchFriendsOfFriends($userId);

I've not tested it out but I would assume it would cut out the need for lazy loading.

Upvotes: 1

Related Questions