Doctrine QueryBuilder select from one-to-many relationship

Question

I have 2 entities with a one-to-many relationship. I need to select the name from both entity 1 and entity 2

$qb
    ->select(['f.name1', 'c.name2'])
    ->from('BundleOne:EntityOne', 'c')
    ->innerJoin('c.EntityTwo', 'f');

return $qb->getQuery()->getArrayResult();

With the above query, I get the following results:

1 => array:2 [ 
    "name1" => "xyz" 
    "name2" => "n1" 
] 
2 => array:2 [ 
    "name1" => "xyz" 
    "name2" => "n2" 
] 
3 => array:2 [ 
    "name1" => "abc" 
    "name2" => "n3" 
] 
4 => array:2 [ 
    "name1" => "abc" 
    "name2" => "n4" 
]

As you can notice, since this is a one-to-many relationship, a name1 can have several name2 associated with it and instead of the above, I want to return the result as follows:

"xyz" => array:2 ["n1", "n2"]
"abc" => array:2 ["n3", "n4"]

that is the name1 as the key of the array that contains all name2

Is that possible?

try to use something like this https://stackoverflow.com/a/17783366/7155723 — Odin Thunder, Jun 12 '18 at 06:51
try to remove the select option and do it like `...$qb->addSelect('f')\\or f.name` this would select by default the reference entity, which is used in `from()`, and add the related entity as property in the correct path. — Juan I. Morales Pestana, Jun 12 '18 at 12:16

score 0 · Answer 1 · answered Jun 15 '18 at 11:43

You could do it like this (you didn't provide any real-world example of entities, so I created two on my own, just for the sake of simplicity):

#Category 1-N Product
#AppBundle/Repository/CategoryRepository.php
public function getProducts()
{
    $qb = $this
        ->createQueryBuilder('c')
        ->select('c, p')
        ->leftJoin('c.products', 'p')
    ;

    return $qb->getQuery()->getArrayResult();
}

Then in the controller:

#AppBundle/Controller/DefaultController.php
public function indexAction()
{
    $categories = $this->getDoctrine()->getRepository('AppBundle:Category')->getProducts();
    $results = [];
    foreach($categories as $category) {
        $results[$category['name']] = $category['products'];
    }

    return $this->render('...', [
        'results' => $results,
    ]);
}

The results will be displayed like this:

array:2 [▼
  "category1" => array:3 [▼
    0 => array:2 [▼
      "id" => 1
      "name" => "product1"
    ]
    1 => array:2 [▼
      "id" => 2
      "name" => "product2"
    ]
    2 => array:2 [▼
      "id" => 3
      "name" => "product3"
    ]
  ]
  "category2" => array:2 [▼
    0 => array:2 [▼
      "id" => 4
      "name" => "product1"
    ]
    1 => array:2 [▼
      "id" => 5
      "name" => "product2"
    ]
  ]
]

Doctrine QueryBuilder select from one-to-many relationship

1 Answers1