Getting data from joined query builder-CodePudding

Hello I'm currently working on menu randomizer web app. Every dish in menu has ingredients, and each ingredient has: name, unit and ammount. Dishes and Ingredients are two different entities that are in OneToMany relation. I want to query each Dish and its Ingredients so here's my custom query:

DishRepository.php

   /**
    * @return Dish[]
    */
    
    public function findMealsByDish($meal)
    {
        return $this->createQueryBuilder('d')
            ->leftJoin('d.ingredients', 'ingredients')
            ->addSelect('ingredients')
            ->where('d.type = :meal')
            ->setParameter('meal', $meal)
            ->andWhere('d.owner = :owner')
            ->setParameter('owner', $this->security->getUser())
            ->orderBy('d.name', 'ASC')
            ->getQuery()
            ->getResult()
        ;
    }

When I dd() my query I get result like this:

I've been looking for but can't find how can I access those Inredients data like: name, etc.

CodePudding user response：

It's an ArrayCollection (OneToMany) so you need to browse the ingredients array collection and then you will have access to properties such as name, etc.

For that you can dump the first one like this :

 dump(yourDishArray[0]->getIngredients()[0]->getName()); //returns "butter"

If you need to iterate (dump all elements name's one by one for e.g) you can do something like that :

 for ($i = 0; $i < count(yourArray[0]->getIngredients()); $i  )  { 
   dump(yourDishArray[0]->getIngredients()->[$i]->getName());
}

NB : You can also use a foreach instead

CodePudding user response：

If I understand correctly, there is actually a lot easier way to do what you are trying to achieve, without any custom queries (using Doctrine and Entities properly).

From what I can see, you have a ManyToOne (or ManyToMany) relationship between meals and dishes (through dish.type). If you don't already have it, you should set the mappedBy side of the relationship on Meal. (https://symfonycasts.com/screencast/symfony3-doctrine-relations/one-to-many).

Then to get all the dishes of a particular meal type, you only need to do:

$dishes = $meal->getDishes();

Now that you have all the dishes you need, you can simply create a foreach loop to go through each dish and get the ingredients (thanks to the OneToMany relationship).

foreach ($dishes as $dish){
     $ingredients = $dish->getIngredients();
     foreach( $ingredients as $ingredient) {
          //get name as $ingredient->getName();
     }
}

Please note that this has performance issues if you have a LOT of dishes as the database will be queried each time in the loop (read up on n 1 problem) to get the ingredients but unless you have really large number of dishes, this should not be a concern.