Doctrine ORM serialization after join

Question

I guys I am working diwth doctrine JOINS and I came across a problem.

I have done a query with joins and it seems ok, the results are good! but the output is terrible

public function getMarketcapData($page = -1, $limit = -1){
      $pageAndCount = $page>-1 && $limit>0;
      $qb = $this->getEntityManager()->createQueryBuilder();

      $q  = $qb->select('cc, count(cr) as coinRawsCount, cmc as relatedCategory')
              ->from('AppBundle:CoinClean', 'cc')
              ->leftJoin("AppBundle:CoinRaw", "cr",  'WITH', 'cc = cr.coinClean')
              ->leftJoin("AppBundle:CoinMapCategory", "cmc", 'WITH', 'cc.relatedCategory = cmc')
              ->andWhere('cc = cr.coinClean')
              ->andWhere('cc.relatedCategory = cmc')
              ->groupBy('cc.id')
      ;

      if($pageAndCount) $q = $q->setFirstResult($page*$limit)->setMaxResults($limit);

      $q= $q->orderBy('cc.rank', "ASC")->getQuery();

      $result = $q->getResult();
      if($pageAndCount){
          $qb = $this->getEntityManager()->createQueryBuilder();
          $q = $qb->select('count(u.id)')->from('AppBundle:CoinClean', 'u')->getQuery();
          return new PageResult($result, (int)$q->getSingleScalarResult(), $page, $limit );
      }else{
          return $result;
      }
  }

here you have the output: it is an array made by array + obejcts

data:[
   [ // first query result
    {data from cc} // ony one object
   ], 
   {// first query result from join
     "coinRawsCount": 4,
     "relatedCategory": {...}
   },
   [ // second query result
     {data from cc2} // ony one object
   ], 
   {// second query result from join
    "coinRawsCount": 4,
    "relatedCategory": {...}
   }
]

my goal is to condense all in

"data":[
  {
    "data": {...},
    "coinRawsCount": 4,
    "relatedCategory": {...}
  },
  {
    "data": {...},
    "coinRawsCount": 4,
    "relatedCategory": {...}
  },
]

Any ideas?

user_1166880 · Answer

You could loop over the result and merge on every other row, e.g. like this:

$rows = $q->getResult();
$result = []

foreach ($rows as $index => $row) {
    if ($row instanceof App\Entity\CoinClean) {
        // Skip first row and merge data when reading next row
        continue;
    }
    $result[] = array_merge(
        ['data' => $rows[$index - 1]],
        $row
    );
}

return $result;

Assuming the first row is always the entity, we will skip this entry using continue;. The next row should contain an array with the additional data. We can then take the previous offset ($index - 1) to fetch the entry and the current row and merge them together. You might have to do additional checks.

Keep in mind that you will loop over a potentially large array and since you copy the data over to a new array this might use up a considerable amount of memory. So you might have to profile and optimize it, if you notice poor performance.

Other options would be to perform a native SQL query and then just map the output using a ResultSetMappingBuilder or to use custom hydration, i.e. create a new custom DTO-object for that query, that better fits the resulting data than the original entity structure, using DQL SELECT NEW App\Dto\MyModel FROM ...your query...'.

Doctrine ORM serialization after join

1 Answers