Optimizing Doctrine Query to Avoid Extra Queries
I am creating a system with an updated wall where you only see messages from friends. A "friend" is here classified as a set of two users, each of whom adds each other to their friends list.
A friendly association is handled very simply by a Doctrine association ManyToMany
, for example:
User.php
//.. other declarations
/**
* @var ArrayCollection
* @ORM\ManyToMany(targetEntity="User")
* @ORM\JoinTable(name="user_friends")
*/
protected $friends;
//.. more unrelated stuff
and in my request to get updates of users' friends (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 one works , but the problem is that the line if($friend->getFriends()->contains($user))
, if you need to know whether $user
users exist in another friends list, generates a new request for each individual person in $user
the friends list.
I don't mind an additional request or two to accomplish this, but I need to talk about scale here. Potentially, a person in this application can have hundreds of friends. I can't have hundreds of requests (however they are) executed every time this page is loaded, which could potentially be a lot.
What's the best possible solution for this?
Now I can proceed with development as it works correctly.
EDIT
for the sake of brevity, I actually tried to go the route of EXTRA_LAZY
adding EXTRA_LAZY
friends to my ManyToMany $ declaration in my custom entity. According to the documentation, if you are using fetch mode EXTRA_LAZY
, using Collection#contains(entity)
will not trigger a request.
The NET problem here stems from the fact that friends $user
need their friends list to be collected for review. And it makes sense.
Is there a way to bring it all into one operation?
source to share
If you add connections for users' friends and their friends when getting the $ user object, then it should cut out any lazy loading. For example, you might have a custom repository for a user like this:
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();
}
}
Get your custom object to use this repository:
/**
* @ORM\Entity(repositoryClass="UserRepository")
*/
class User {
...
Then when you get your $ user, use the new fetch method:
$user = $em->getRepository('User')->fetchFriendsOfFriends($userId);
I have not tested it, but I would guess that it will eliminate the need for lazy loading.
source to share