Symfony and doctrine request

Question

I'm trying to get all Tickets with "Destinataire" equal to a "Compte":

$ret = $repository->findByDestinataires($compte->getId());

My problem is that I get an error:

An exception occurred while executing 'SELECT t0.id AS id_1, t0.Titre AS Titre_2, t0.DateCreation AS DateCreation_3, t0.DateButoire AS DateButoire_4, t0.DateFin AS DateFin_5, t0.Priorite AS Priorite_6, t0.Commentaire AS Commentaire_7, t0.Statut AS Statut_8, t0.emeteur_id AS emeteur_id_9, t0.client_id AS client_id_10 FROM ticket t0 WHERE ticket_compte.compte_id = ?' with params [1]:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'ticket_compte.compte_id' in 'where clause'

I have two tables like this:

Compte:

namespace CommonBundle\Entity;

use Doctrine\ORM\Mapping as ORM;
use Symfony\Component\Validator\Constraints as Assert;

/**
 * Compte
 *
 * @ORM\Table(name="compte")
 * @ORM\Entity(repositoryClass="CommonBundle\Repository\CompteRepository")
 */
class Compte
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    public $id;

    /**
     * @var string
     *
     * @ORM\Column(name="Nom", type="string", length=80)
     */
    public $nom;

    /**
     * @var string
     *
     * @ORM\Column(name="Prenom", type="string", length=80)
     */
    public $prenom;

    /**
     * @var string
     *
     * @ORM\Column(name="Fonction", type="string", length=80)
     */
    public $fonction;

    /**
     * @var string
     *
     * @Assert\NotBlank()
     * @ORM\Column(name="Pseudo", type="string", length=80)
     */
    public $pseudo;

    /**
     * @var string
     *
     * @Assert\NotBlank()
     * @ORM\Column(name="MotDePasse", type="string", length=80)
     */
    public $motDePasse;

    /**
    * @ORM\ManyToOne(targetEntity="CommonBundle\Entity\Profil", cascade={"persist"})
    * @ORM\JoinColumn(nullable=false)
    */
    public $profil;

    /**
    * @ORM\ManyToMany(targetEntity="CommonBundle\Entity\Ticket", cascade={"persist"})
    */
    public $tickets;
}

Ticket:

namespace CommonBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
 * Ticket
 *
 * @ORM\Table(name="ticket")
 * @ORM\Entity(repositoryClass="CommonBundle\Repository\TicketRepository")
 */
class Ticket
{
    /**
     * @var int
     *
     * @ORM\Column(name="id", type="integer")
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    public $id;

    /**
     * @var string
     *
     * @ORM\Column(name="Titre", type="string", length=80)
     */
    public $titre;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="DateCreation", type="datetimetz")
     */
    public $dateCreation;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="DateButoire", type="datetimetz")
     */
    public $dateButoire;

    /**
     * @var \DateTime
     *
     * @ORM\Column(name="DateFin", type="datetimetz", nullable=true)
     */
    public $dateFin;

    /**
     * @var int
     *
     * @ORM\Column(name="Priorite", type="integer")
     */
    public $priorite;

    /**
     * @var string
     *
     * @ORM\Column(name="Commentaire", type="text")
     */
    public $commentaire;

    /**
     * @var int
     *
     * @ORM\Column(name="Statut", type="integer")
     */
    public $statut;

    /**
    * @ORM\ManyToOne(targetEntity="CommonBundle\Entity\Compte", cascade={"persist"})
    * @ORM\JoinColumn(nullable=false)
    */
    public $emeteur;

    /**
    * @ORM\ManyToMany(targetEntity="CommonBundle\Entity\Compte", cascade={"persist"})
    * @ORM\JoinColumn(nullable=true)
    */
    public $destinataires;

    /**
    * @ORM\ManyToOne(targetEntity="CommonBundle\Entity\Client")
    * @ORM\JoinColumn(nullable=true)
    */
    public $client;
}

And there links:

compte_ticket //for "emeteure"

->ticket_id compte_id

ticket_compte //for "destinataires"

->ticket_id compte_id

I've tried the same request directly on the server and got the same error. Is it the PHP code that is wrong? Or maybe the entities...

INFO:

I've not put all the get/set from Compte or Ticket on purpose. If it's needed I'll edit.

Answer 1

http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/working-with-objects.html

It seem the that the shortcut method you are using (findByDestinataires) is only designed to get related records from the owning side of a relationship and hence only one 2 many not many 2 many.

You will need to create a custom query for this.

Here is one I knocked up quickly, add the following function to your TicketRepository

public function getTicketsByCompteId($compte_id)
{
    return $this->getEntityManager()
        ->createQuery(
            "SELECT t, d FROM AppBundle:Ticket t
                LEFT JOIN t.destinataires d
                WHERE
                  d.id = :compte_id"
        )
        ->setParameter('compte_id', $compte_id)
        ->getResult();

}

And then call it from your controller:

$ret = $repository->getTicketsByCompteId($compte->getId());

Answer 2

Sounds like the foreign key field is not created. Did you run

php app/console doctrine:schema:update --force --dump-sql

After adding your relationships?