doctrine composite key and one to many

Question

I use Symfony 2.8. I have two table and in both the primary key is composed by 3 columns:

id, tipo_corso, comune  
02, it, devi  
01, en, capi  
09, es, file

Obviously the two table have other different columns. I can't change the primary key by use only one or two columns. For one record in StranieriCRS table there are many record in EsoneroLingua table (OneToMany):

First entity:

class StranieriCRS
{

    /**
     * @ORM\Column(type="string")
     * @ORM\Id
     */
    private $id;

    /**
     * @ORM\Column(type="string")
     * @ORM\Id
     */
    private $tipo_corso;

    /**
     * @ORM\Column(type="string")
     * @ORM\Id
     */
    private $comune;

    public function __construct($id, $tipo_corso, $comune)
    {
        $this->id = $id;
        $this->tipo_corso = $tipo_corso;
        $this->comune = $comune;
        $this->esonerolingua = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /**
     * @ORM\OneToMany(targetEntity="EsoneroLingua", mappedBy="stranieriCRS", fetch="EAGER")
     */
    private $esonerolingua;

    /**
     * Get esonerolingua
     *
     * @return \Doctrine\Common\Collections\Collection
     */
    public function getEsonerolingua()
    {
        return $this->esonerolingua;
    }

Second entity:

class EsoneroLingua
{

    /**
     * @ORM\Column(type="string")
     * @ORM\Id
     */
    private $id;

    /**
     * @ORM\Column(type="string")
     * @ORM\Id
     */
    private $tipo_corso;

    /**
     * @ORM\Column(type="string")
     * @ORM\Id
     */
    private $comune;

    public function __construct($id, $tipo_corso, $comune)
    {
        $this->id = $id;
        $this->tipo_corso = $tipo_corso;
        $this->comune = $comune;
    }

    /**
     * @ORM\ManyToOne(targetEntity="StranieriCRS", inversedBy="esonerolingua")
     * @ORM\JoinColumns(
     *      @ORM\JoinColumn(name="id", referencedColumnName="id"),
     *      @ORM\JoinColumn(name="tipo_corso", referencedColumnName="tipo_corso"),
     *      @ORM\JoinColumn(name="comune", referencedColumnName="comune"),
     * )
     */
    private $stranieriCRS;

The problem occur when I want get the StranieriCRS object because he give me as result only one result...seems like a OneToOne relation. My Controller:

$sql = $entityManager->createQuery("
    SELECT c
    FROM AppBundle:EsoneroLingua c
    WHERE c.id = '1546871' and c.tipo_corso = 'C' and c.comune = '7868'
");

$test = $sql->getResult();

In $test I was expect N record of EsoneroLingua with the same record StranieriCRS but I get only one EsoneroLingua with the correct StranieriCRS object. Seems work like OneToOne relation...why? Plus if I made dump($sql->getSql()); I obtain the raw sql...I try to use it directly in my db and he give me the right result. Is it a Doctrine bug?

Please try removing the `JoinColumns` annotation. This is not directly related to your problem, but this annotation is useless in most cases and only clutters your code. — lxg, Aug 21 '18 at 08:12
@lxg I already delete JoinColumns from StranieriCRS as suggest by LP154 but nothing change :( — lausent, Aug 21 '18 at 08:19
It’s good that nothing changed. You just removed unnecessary code, and nothing went worse. Congratulations! :) By the way, you can remove ALL `JoinColumn`s, everywhere. They’re almost never needed. — lxg, Aug 21 '18 at 08:35
I don’t think so, because this is a very common pattern. You wouldn’t be the first to face it. ;) Try clearing or deleting your cache and try again. If you’re using an external caching service (e.g. Memcache) clear or restart that one, too. — lxg, Aug 21 '18 at 08:39
Is there other columns in `EsoneroLingua` that are primary keys (and declared as id for Doctrine) ? With your code, it seems like you can't have 2 `EsoneroLingua` entities with the same `(id, tipo_corso, comune)`. — LP154, Aug 21 '18 at 08:51
There are other columns in EsoneroLingua as in StranieriCRS but they are not declared as id (the annotation @ORM\Id is only on the column $id, $tipo_corso and $comune). In my oracle table I have that two table and the columns name are the same (id, tipo_corso and comune) — lausent, Aug 21 '18 at 08:59
But is there other columns that are primary key in Oracle in `EsoneroLingua` ? Because if not, you can't have multiple `EsoneroLingua` with the same PK, so you can't have a ManyToOne — LP154, Aug 21 '18 at 09:15
Solved, in EsoneroLingua $id, $tipo_corso and $comune was not a primary key...I add a fourth column as @ORM\Id. — lausent, Aug 21 '18 at 09:47

score 1 · Answer 1 · answered Aug 21 '18 at 07:27

To make a bidirectionnal One-To-Many, specify the JoinColumns only in the Many-To-One side.

So, in StranieriCRS, remove the following lines :

 * @ORM\JoinColumns(
 *      @ORM\JoinColumn(name="id", referencedColumnName="id"),
 *      @ORM\JoinColumn(name="tipo_corso", referencedColumnName="tipo_corso"),
 *      @ORM\JoinColumn(name="comune", referencedColumnName="comune"),
 * )

And just let Doctrine guess the columns with the inversedBy and mappedBy attributes.

For more information on the mappings, see this page.

doctrine composite key and one to many

1 Answers1