Doctrine get array without prefix or suffix

Question

I am trying to get join data from doctrine returned in a flat array. This is possible using different methods, however I keep having either table aliases prefixed to my column names, or an index suffixed to my column names. Examples follow:

My query (using query builder):

$qb->select('gl, m.MAINCAT, i.IMAGE_NAME')
    ->from('Entities\GroupLevel', 'gl')
    ->join('Entities\Maincat', 'm', Expr\Join::WITH, 'gl.MAINCAT_ID = m')
    ->innerJoin('Entities\Image', 'i', Expr\Join::WITH, 'gl.IMAGE_ID = i')
    ->where('gl.USER_GROUP_ID = 7 AND gl.MAINCAT_ID = 2');
$query = $qb->getQuery();

Using $query->getResult(AbstractQuery::HYDRATE_SCALAR) my output looks like this:

array(12) {
  ["gl_GROUP_LVL_ID"]=>
  int(4)
  ["gl_PARENT_LVL_ID"]=>
  int(0)
  ["gl_DESCRIPTION"]=>
  string(18) "Second description"
  ["gl_MAINCAT_ID"]=>
  int(2)
  ["gl_USER_GROUP_ID"]=>
  int(7)
  ["gl_IMAGE_ID"]=>
  int(6)
  ["gl_ORPHAN_GROUP"]=>
  int(0)
  ["gl_ANIMATION_ID"]=>
  int(-1)
  ["gl_SEQUENCE_NO"]=>
  int(6)
  ["gl_IMAGINE_ID_DESCRIPTION"]=>
  string(25) "GROUP_LEVEL_DESCRIPTION_6"
  ["MAINCAT"]=>
  string(1) "F"
  ["IMAGE_NAME"]=>
  string(12) "some pic.png"
}

The problem is I don't want the table alias "gl_" appended. So after some reading I saw that it is possible to write your own custom hydrators. So I made my hydrator as follows.

class HydrateScalarCustom extends AbstractHydrator
{
    protected function hydrateAllData()
    {
        return $this->_stmt->fetchAll(\PDO::FETCH_ASSOC);
    }
}

However, even though I use \PDO::FETCH_ASSOC it suffixes the columns with a 0 based index like this:

array(12) {
  ["GROUP_LVL_ID_0"]=>
  string(1) "4"
  ["PARENT_LVL_ID_1"]=>
  string(1) "0"
  ["DESCRIPTION_2"]=>
  string(18) "Second description"
  ["MAINCAT_ID_3"]=>
  string(1) "2"
  ["USER_GROUP_ID_4"]=>
  string(1) "7"
  ["IMAGE_ID_5"]=>
  string(1) "6"
  ["ORPHAN_GROUP_6"]=>
  string(1) "0"
  ["ANIMATION_ID_7"]=>
  string(2) "-1"
  ["SEQUENCE_NO_8"]=>
  string(1) "6"
  ["IMAGINE_ID_DESCRIPTION_9"]=>
  string(25) "GROUP_LEVEL_DESCRIPTION_6"
  ["MAINCAT_10"]=>
  string(1) "F"
  ["IMAGE_NAME_11"]=>
  string(12) "some pic.png"
}

Please let me know if you have any ideas. I have heard that with PDO, if your column names are not unique, it may add the index to your column names. However, all the columns are unique. I DO NOT want to iterate over returned data and strip off prefixes/suffixes, rather get it returned the correct way.

Thanks in advance.

Answer 1

I have found a temporary solution, though not ideal. Using $query->getResult(AbstractQuery::HYDRATE_SCALAR) and specifying each column from the group levels table $qb->select('m.MAINCAT, i.IMAGE_NAME, gl.GROUP_LVL_ID, gl.PARENT_LVL_ID ... ') will return the columns without prefixes. Ideally I would not need to specify each column to fetch.