How is it possible to map column names from a Doctrine sql query?

Question

I created a query with query builder like this:

$qb = $em->createQueryBuilder();
$qb->select(['u.id', 'u.name'])
   ->from(User::class, 'u')
   ->where('u.active = 1')
;
$sql = $qb->getQuery()->getSql();

The result looks like this:

SELECT u1_.user_id as s1, u1_.full_name as s2 FROM users u1_ WHERE u1_.is_active = 1

I would like to execute it as a native query, but I have to find out, how to map s1, s2 to id, name.

Answer 1

you have to use as inside your select

using your example =>

$qb = $em->createQueryBuilder();
$qb->select(['u.id as s1', 'u.name as s2'])
   ->from(User::class, 'u')
   ->where('u.active = 1');
$sql = $qb->getQuery()->getSql();

this maps id to s1 and name as s2

Answer 2

Not sure I understand your question but if you want to run a native query with, in the result, the columns id and name, you can replace them in the query, they're aliases so they can be anything you want :

SELECT u.user_id as id, u.full_name as name FROM users u WHERE u.is_active = 1