Doctrine DBAL - SELECT join two tables, with a prefix in the keys of the result

Question

I'm currently using doctrine dbal for my database queries. I'm also using the querybuilder. And I was wondering how I could select two tables and get the results in an array that has prefixed keynames for one of the tables joined.

Here is an example of the desired result:

$data   = [
    key1 => 'value1',
    key2 => 'value2',
    key3 => 'value3',
    key4 => 'value4',
    table2.key1 => 'value1',
    table2.key2 => 'value2',
    table2.key3 => 'value3',
]

Thanks

Answer 1

I have just faced with the same problem and I resolved it in next way:

$result = $this->createQueryBuilder('comments')
        ->select([
            'comments.id as id',
            'comments.post_id as postId',
            'comments.userId as userId',
            'comments.userName as userName',
            'comments.userEmail as userEmail',
            'comments.parentId as parentId',
            'comments.postedAt as postedAt',
            'comments.status as status',
            'comments.comment as comment',
            'user.fullname as user_userName',
            'user.email as user_email',
       ])
       ->leftJoin(
              'Entity\User', 'user',
              Query\Expr\Join::WITH,
              'comments.userId = user.id'
      )
      ->where('comments.post_id=:postId')->setParameter('postId', $postId)
      ->getQuery()
      ->getResult(Query::HYDRATE_ARRAY)
;

So, to add prefixes to table data, just declare them into "select" statement as

table.var1 as prefix_var1, table.var2 as prefix_var2

and the result will be

[
    prefix_var1 => value,
    prefix_var2 => value,
]

one more thing that you can do, when you declare select as:

table1, table2.var1 as prefix_var1, table2.var2 as prefix_var2

you will get the next result

[
    0 => [ // it's come from table1
           var1 => value,  
           var2 => value,
    ],
    prefix_var1 => value, // it's come from table2
    prefix_var2 => value, // it's come from table2
]