CODEWITHSUNDEEP
sqlsymfonydoctrine-ormdoctrinedql
G. Świdnicki
G. Świdnicki

Reputation: 1

Doctrine: results with keys as association entity value

I have a two entities in my project:

class Effect {

   /**
    * @ORM\Id
    * @ORM\GeneratedValue
    * @ORM\Column(name="id", type="integer")
    */
   private $id;

   /**
    * @ORM\Column(name="name", type="string", unique=true)
    */
   private $name;

   /**
    * @ORM\ManyToOne(targetEntity="App\Entity\EffectGroup", inversedBy="effects")
    * @ORM\JoinColumn(name="group_id", referencedColumnName="id")
    */
   private $effectGroup;

}

class EffectGroup {

  /**
   * @ORM\Id
   * @ORM\GeneratedValue
   * @ORM\Column(name="id", type="integer")
   */
   private $id;

   /**
    * @ORM\Column(name="name", type="string", unique=true)
    */
   private $name;

   /**
    * @ORM\OneToMany(targetEntity="App\Entity\Effect", mappedBy="effectGroup")
    */
   private $effects;

}

I need to query database to get the following result:

array [
    'effectGroupName 1' => array [
        object (App\Entity\Effect) {
            id: 1,
            name: 'effectName 1'
            ...
        },
        object (App\Entity\Effect) {
            id: 2,
            name: 'effectName 2'
            ...
        },
    ]
    'effectGroupName 2' => array [
        object (App\Entity\Effect) {
            id: 3,
            name: 'effectName 3'
            ...
        },
        object (App\Entity\Effect) {
            id: 4,
            name: 'effectName 4'
            ...
        },
    ]
]

Where effectGroupName 1 and effectGroupName 2 is name from EffectGroup entity. I need a query so that I can filter the results after the Effect id. What should ORM / DQL query look like? Is it possible to achieve such a result at all?

Upvotes: 0

Views: 453

Answers (1)

Jakumi
Jakumi

Reputation: 8374

it'll not be a canonical query, since your result isn't. you will have some amount of post-processing (since doctrine by default returns one set of rows).

the repository function should look something like this (in EffectRepository)

public function getGrouped(): array 
{
    $result = $this->createQueryBuilder('e')
            ->innerJoin('e.effectGroup', 'g')
            ->select('e as effect') 
            ->addSelect('g.name as groupname')
            ->getQuery()
            ->getResult();
    $return = [];
    foreach ($result as $row) {
        $return[$row['groupname']][] = $row['effect'];
    }
    return $return;
}

Upvotes: 1

Related Questions