DQL Queries not returning what I would like

Question

I use two DQL queries. On my website I have users and Formations. I have defined a ManyToMany relation on User like this:

/**
     * Many User have Many Phonenumbers.
     * @ManyToMany(targetEntity="Formation")
     * @JoinTable(name="users_favorites",
     *      joinColumns={@JoinColumn(name="user_id", referencedColumnName="id")},
     *      inverseJoinColumns={@JoinColumn(name="formation_id", referencedColumnName="id", unique=true)}
     *      )
     */
    private $favorites;

Users can add Formations to their favorite.

The first query, I would like to get all the Formations but also, the users that added this formation to their favorite.

    public function findAllWithFormation(){
        return $this->createQueryBuilder('user')
            ->join('user.favorites', 'formation')
            ->addSelect('formation')
            ->addSelect('user')
            ->getQuery()
            ->getOneOrNullResult()
            ;
    }

This return nothing, not a single formation.

EDIT: The result of my query is this:

    FormationController.php on line 22:
App\Entity\User {#768 ▼
  -id: 1
  -email: "mail@mail.fr"
  -roles: array:2 [▶]
  -password: ""
  -favorites: Doctrine\ORM\PersistentCollection {#765 ▼
    -snapshot: array:1 [ …1]
    -owner: App\Entity\User {#768}
    -association: array:19 [ …19]
    -em: Doctrine\ORM\EntityManager {#563 …11}
    -backRefFieldName: null
    -typeClass: Doctrine\ORM\Mapping\ClassMetadata {#703 …}
    -isDirty: false
    #collection: Doctrine\Common\Collections\ArrayCollection {#766 ▼
      -elements: array:1 [▼
        0 => App\Entity\Formation {#763 ▼
          -id: 1
          -name: "PHP "
          -url: "http://ulrt"
        }
      ]
    }
    #initialized: true
  }
  -pseudo: null
}

As you can see, I receive only the formation that the user has added in favorites, while I would like to get all the formation but also have the information of which user has added this formation to their favorite. In User.php I have the field favorites in ManyToMany and this creates a table users_favorites, and this is what I want. But I don't have any link between the formation and the users_favorites on my Formation Entity. How can I add a link between those? Do I need to add a field? If so, how do I define it so it doesn't create another table like the one it created with favorites on User Entity?

Answer 1

So I assume you're doing your dql query in the UserRepository, you should focus the query on the FormationRepository instead (assuming proper many-to-many definitions with User.favorites has a matching Formation.users: ...):

/**
  * @ORM\ManyToMany(targetEntity=User:class, mappedBy="favorites")
  */
private $users;

you might need to add inversedBy="users" on your User.favorites many-to-many annotation

$qb = $this->createQueryBuilder('formation');
return <$qb->leftJoin('formation.users', 'user')
    ->addSelect('user')
    ->getQuery()->getResult();