[FIXED] Doctrine many to many query builder

Issue

I have a Contacts entity and a Users entity with many-to-many relationship, in order to setup favorite contacts.

I try to build a query to find if a contact is a favorite of a user. Doctrine returns the following error:

[Semantical Error] line 0, col 81 near 'favoris = :user': Error: Invalid PathExpression. StateFieldPathExpression or SingleValuedAssociationField expected.

Here is my query :

    public function isFavori(users $user, contacts $contacts) {

    $em = $this->getEntityManager();
    $qb = $em->createQueryBuilder('c');
    $query = $qb
        ->select('1')
        ->from('contactsBundle:contacts', 'c')
        ->join('contactsBundle:users', 'f', 'WITH', 'c.favoris = :user')
        ->where('c = :contact')
        ->setParameter('user', $user)
        ->setParameter('contact', $contacts)
    ;
    $querystring = $qb->getQuery();
    return $qb->getQuery()->getResult();

}

Here is the join annotation from the Contact entity:

/**
 * @ORM\ManyToMany(targetEntity="Curuba\contactsBundle\Entity\users", inversedBy="contactsfavoris")
 *
 */
private $favoris;

Solution

I ended up in creating my own class. This is also required to get more flexibility to manipulate the query when using this bundle : APYDataGridBundle.

So here is what I have:

contacts entity class, with:

/**
 * @ORM\OneToMany(targetEntity="Curuba\contactsBundle\Entity\contactsfavoris", mappedBy="contact", orphanRemoval=true, cascade={"remove", "persist"})
 * @ORM\JoinColumn(nullable=true)
 */
private $usersFavoris;

users entity class, with:

/**
 * @ORM\OneToMany(targetEntity="Curuba\contactsBundle\Entity\contactsfavoris", mappedBy="user")
 *
 */
private $contactsfavoris;

contactsfavoris entity class, with:

/**
 * @ORM\ManyToOne(targetEntity="contacts", inversedBy="usersFavoris")
 */
private $contact;

/**
 * @ORM\ManyToOne(targetEntity="users", inversedBy="contactsfavoris")
 */
private $user;

The query:

public function isFavori(users $user, contacts $contacts) {

    $em = $this->getEntityManager();
    $qb = $em->createQueryBuilder('c');
    $userid = $user->getId();
    $contactId = $contacts->getId();
    $query = $qb
        ->select('1')
        ->from('contactsBundle:contactsfavoris','cf')
        ->where('cf.user = :user')
        ->andWhere('cf.contact = :contact')
        ->setParameter('user', $userid)
        ->setParameter('contact', $contacts)
    ;

    try {
        $result = $qb->getQuery()->getSingleScalarResult();
    } catch (Exception $e) {
        $result = 0;
    }
    if ($result == 1 ) {
        $favori = true;
    } else {
        $favori = false;
    }

    return $favori;

}

Answered By - curuba