CODEWITHSUNDEEP
phpsymfonydoctrine-orm
Alexandre Tranchant
Alexandre Tranchant

Reputation: 4580

How to optimize number of Doctrine queries when using an inner Join?

I have to simple Entity: Log and User. Log has a ManyToOne relationship with Entity.

Log:
  type: entity
  repositoryClass: LogRepository
  id:
    id:
      type: integer
      generator:
        strategy: AUTO
  fields:
    message:
      type: string      
  manyToOne:
    user:
      targetEntity: User
      joinColumns:
        user_id:
          referencedColumnName: id

My use case is to show the list of the logs and one or two information about the user (like his name and his mail for example)

If I use the findall method, Symfony debug toolbar shows me that Doctrine performs a lot of queries. One query gives me the logs and one query is performed for each user! It is not good of course because I can have thousand logs in my view. I don't want to overload my database server. This problem seems very simple to solve. But I'm searching for a while and the results seems to be "bad practices".

So I began by writing a new method in the LogRepository class using the querybuilder:

public function getLog(){
        $qb = $this->createQueryBuilder('l')
            ->select('l')
            ->innerJoin(
                'ApplicationSonataUserBundle:User', 'u', 
                Expr\Join::WITH,'l.user = u.id')
        ;
        return $qb->getQuery()->getResult();
}

I still had the same problem. I have changed the select parameters on my method to :

public function getLog(){
        $qb = $this->createQueryBuilder('l')
            ->select('l','u')
            ->innerJoin('ApplicationSonataUserBundle:User','u', 
                        Expr\Join::WITH,'l.user = u.id')
        ;
        return $qb->getQuery()->getResult();
}

Eureka? OK, I only have one query but my method didn't return only Log, BUT User too... So my Twig template crashes because my loop contains User, not only Log. When this is a User, my view crash because I want to write message fields. (Log.message exists. But User.message is not a valid field, of course)

It works pretty good, if I change one more time my method with a loop to filter my results :

public function getLog(){
        $qb = $this->createQueryBuilder('l')
            ->select('l','u')
            ->innerJoin('ApplicationSonataUserBundle:User','u',
                        Expr\Join::WITH,'l.user = u.id')
        ;
        //THE STRANGE LOOP
        $results = array();
        foreach ($qb->getQuery()->getResult() as $result){
            if ($result instanceof Log){
                $results[] = $result;
            }
        };
        return $results;
}

I have only one query, it is what I'm searching for. My twig template doesn't crash, because my array contains only Log.

So what's the matter? It works, but I think this is not the good/best practices.

Someone can explain me a better way, a better practice to use an inner join query, to minimize the performed query and have an ArrayCollection result which contains only instance of Log?

Upvotes: 4

Views: 955

Answers (1)

Wilt
Wilt

Reputation: 44422

It should not be necessary to use the loop. Try like this:

public function getLog(){
    $qb = $this->createQueryBuilder('l')
        ->select('l','u')
        ->innerJoin('l.user', 'u');
    $logs = $qb->getQuery()->getResult();
    return $logs;
}

It should only return $logs with a populated (fetch joined) association user.

Upvotes: 5

Related Questions