Aggregating fields to Fetched Objects in Doctrine2

Question

EDIT:

• Further research: Looks like the answer lies on changin default Hydrator for a customized. Doctrine2 allows you to change it just by sending his name as a parameter:

  $query->getResult('CustomHydrator');

Dont forget to register it first, in your config.yml file:

doctrine:
      orm:
         hydrators:
                 CustomHydrator: \your\bundle\Hydrators\CustomHydrator

• My relationship between Blog and Comment entity is one to many. 1 Blog has N Comments

---

After researching about how to add an extra field to a fetched object in Doctrine 2 I found Aggregate Fields, it is a good article but just talk about to get balance from a single Account, it never says what we should do when working with an array of Accounts, it may sound silly but let me explain my situation.

In my case is not about accounts and entries, is about blogs and comments.

What I'm trying to do is to list a number of blogs and just show how many comments it has without loading any comment information, in other words I want to translate this query to the Doctrine2 World.

'SELECT b.*, COUNT( b.id ) AS totalComments FROM  `blogs` b LEFT JOIN comments c ON b.id = c.blog_id GROUP BY b.id LIMIT 8'

and the result that I expect is an array of Blog Objects with a totalComments attribute setted correctly, like this:

array (size=8)
  0 => 
    object Blog
      'id' => int 330
      'title' => string 'title blog'

      // Added field, not visible in table DB. Came through query COUNT() statement
      'totalComments' => int 5 
      // ... more attributes

  1 => ...
  //more object blogs
  );

I just can't achieve this, the best I could do was this:

Creating and fetching Query:

$qb = $this->createQueryBuilder('b')
        ->select('b, c')
        ->addSelect('count(b.id) as nComments')
        ->leftJoin('b.comments', 'c')
        ->groupBy('b.id')

        return $qb->getQuery()->getResult();

and the result I'm getting is an array of arrays, where position 0 has Blog Object and position "totalComments"

// var_dump($result)
array (size=8)
  0 => 
    array(2) =>
      0 =>  
          object Blog
          'id' => int 330
          'title' => string 'title blog'
          // ... more attributes

      "totalComments" => int 5

  1 => ...
  );

I also tried to make my own Hydrator but I just started using Doctrine2 and found myself kinda lost.

I hope been enough clear. I can give any other information if needed.

Thanks in advance!

Answer 1

You either have to name the fields you want, or have a mixed result ( like your 2nd example). So for a flat array:

$qb = $this->createQueryBuilder('b')
    ->select('b.title, b.author')
    ->addSelect('count(c.id) as nComments')
    ->leftJoin('b.comments', 'c')
    ->groupBy('b.id')

return $qb->getQuery()->getArrayResult();

Or a mixed result:

$qb = $this->createQueryBuilder('b')
    ->select('b')
    ->addSelect('count(c.id) as nComments')
    ->leftJoin('b.comments', 'c')
    ->groupBy('b.id')

return $qb->getQuery()->getResult();

Answer 2

After few days I came up with this solution.

I had to add a totalComments attribute to my Blog Entity Class and his get/set methods, and tweak a bit my getLatestBlogs function:

function getLatestBlogs(){
    $qb = $this->createQueryBuilder('b')
        ->select('b, c')
        ->addSelect('count(b.id) as totalComments')
        ->leftJoin('b.comments', 'c')
        ->groupBy('b.id');
    $result = $qb->getQuery()->getResult();

    //tweaking original result
    foreach($result as $row){ 
        $row[0]->setTotalComments($row['totalComments']);
        $blogList[] = $row[0];
    }
    return $blogList;
}

Doing it this way I finally get a simple array of Blog Objects, and it just took an extra loop.

After this I realized that would be nice to have a general function who can work with any Entity, so I made the next function:

function fixResult($qResult){ //Receives $qb->getQuery()->getResult();
    if(is_array($qResult)){
        $list = array();
        $keys = array_keys($qResult[0]); //Getting all array positions from first row
        $object = $qResult[0][0]; //Getting the actual object fetched
        foreach($keys as $key){ //Searching for existing set methods in the Object
            $method = "set".ucfirst($key);
            if(method_exists($object,$method))
                $methods[$key] = $method;
        }
        foreach($qResult as $row){ //Calling set methods for every row fetched and storing into a new array
            foreach($methods as $key => $met){
                $row[0]->$met($row[$key]);
                $list[] = $row[0];
            }
        }
        return $list;
    }
    else return false;
}

I hope somebody else find it useful.