2

I use doctrine to fetch data from the database. In my repository, I build the query 

createQueryBuilder('p')
->select('p, bc, cp')
->innerJoin('p.customer', 'bc')
->innerJoin('bc.person', 'cp')

It works well for me, I get the data. But how can I say to doctrine to give me not all fields, but only certain fields, like p.id, p.name, cp.firstname etc?

Thank's for your answers.

olek07
  • 513
  • 2
  • 7
  • 21
  • [How can I select specific Columns with createQueryBuilder in ORM Symfony2?] (https://stackoverflow.com/questions/12543650/how-can-i-select-specific-columns-with-createquerybuilder-in-orm-symfony2) – ino Dec 12 '17 at 19:06
  • [https://stackoverflow.com/questions/27726879/select-specific-columns-from-a-repository-in-doctrine-2] (Select specific columns from a repository in Doctrine 2) – ino Dec 12 '17 at 19:08
  • 1
    It doesn't work for me: I write select(['p.id', 'bc', 'cp']) and I get an error: [Semantical Error] line 0, col -1 near 'SELECT p.id,': Error: Cannot select entity through identification variables without choosing at least one root entity alias. – olek07 Dec 12 '17 at 19:15

3 Answers3

2
$query->createQueryBuilder()
->select('p.id','p.name', 'bc.xyz', 'cp.xyz')
->from('entity','p')
->innerJoin('p.customer', 'bc')
->innerJoin('bc.person', 'cp');

This works for me

jack
  • 589
  • 8
  • 22
0

I have this line and works like a charm ->select('p', 'p.id', 'cp.id') you need to select one root, in this case my root is p and then you can choose certain fields, Good luck !

Jasson Rojas
  • 299
  • 3
  • 12
  • Take a look on the generated SQL-statement (not DQL). You will see, that doctrine has generated the SQL-Statement with ALL fields from the table with alias p and additionally one more time the fields p.id. – olek07 Dec 12 '17 at 19:56
  • in that case you need to do someting like this: $order = $this->em->createQuery('SELECT o.id AS orderId, i.id AS itemID FROM PayBundle:Order o LEFT JOIN o.items i')->getResult(); if you need more documentation you can check this link: http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html – Jasson Rojas Dec 12 '17 at 20:17
  • if i do: `$query = $this->em->createQuery('SELECT fp.id, bc, cp.firstname FROM \FactoKisBundle\Entity\Facto\Process fp INNER JOIN fp.customer bc INNER JOIN bc.person cp');` I receive SELECT fp.id,': Error: Cannot select entity through identification variables without choosing at least one root entity alias. – olek07 Dec 12 '17 at 20:34
  • 1
    you need to be more specific with bc entity, someting like this : $query = $this->em->createQuery('SELECT fp.id, bc.id, bc.name, cp.firstname FROM \FactoKisBundle\Entity\Facto\Process fp INNER JOIN fp.customer bc INNER JOIN bc.person cp'); – Jasson Rojas Dec 12 '17 at 20:43
  • It works, Thank you very much. I should'n mix entities and entities field in the same query. Either SELECT fp.id, bc.id, cp.firstname or SELECT fp, bc, cp – olek07 Dec 12 '17 at 20:51
0
$fields = array('p.id', 'p.name', 'cp.firstname');

$query = $this->getEntityManager()->createQueryBuilder();
$query
    ->select($fields)
    ->from('xxxBundle:xxx', 'p')
    ->innerJoin('p.customer', 'bc')
    ->innerJoin('bc.person', 'cp')

$results = $query->getQuery()->getResult();

more details ....

  1. Doctrine won't let me select specific fields

  2. How can I select specific Columns with createQueryBuilder in ORM Symfony2?

habibun
  • 1,552
  • 2
  • 14
  • 29
  • 1
    if you don't specify the alias you get `Too few arguments to function Doctrine\ORM\EntityRepository::createQueryBuilder(), 0 passed` – cybernet2u Nov 02 '19 at 13:26