Why is my Many-to-Many relationship not working in Doctrine with Symfony?

Question

I have a Many-to-Many relationship defined as follows:

The two related tables:

CREATE TABLE `homily` (
  `homilyID` int NOT NULL AUTO_INCREMENT,
  `homily` text NOT NULL,
  PRIMARY KEY (`homilyID`),
) ENGINE=InnoDB

---

CREATE TABLE `liturgy` (
  `liturgyID` int NOT NULL AUTO_INCREMENT,
  `timeFK` tinyint NOT NULL,
  `typeFK` tinyint NOT NULL,
  `week` tinyint NOT NULL,
  `day` tinyint NOT NULL,
  `colorFK` tinyint NOT NULL,
  `name` varchar(200) COLLATE utf8mb3_spanish_ci NOT NULL,
  PRIMARY KEY (`liturgyID`),
  UNIQUE KEY `timeFK` (`timeFK`,`typeFK`,`week`,`day`),
  KEY `colorFK` (`colorFK`)
) ENGINE=InnoDB

And an intermediate table that handles the Many-to-Many relationship:

CREATE TABLE `liturgy_homily_join` (
  `liturgyFK` int NOT NULL,
  `homilyFK` int NOT NULL,
  `theme` varchar(200) COLLATE utf8mb3_spanish_ci NOT NULL DEFAULT '',
  PRIMARY KEY (`liturgyFK`,`homilyFK`),
  KEY `homilyFK` (`homilyFK`),
  CONSTRAINT `liturgy_homily_join_ibfk_1` FOREIGN KEY (`liturgyFK`) REFERENCES `liturgy` (`liturgyID`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `liturgy_homily_join_ibfk_2` FOREIGN KEY (`homilyFK`) REFERENCES `homily` (`homilyID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB

Now I want to query my liturgy table with a criterion and get all the homily rows that are related.

To do this I have tried to express a Unidirectional Many to Many relationship in Liturgy, as the documentation explains here.

The class looks like this:

Liturgy

namespace App\Entity;

use App\Repository\LiturgyRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: LiturgyRepository::class)]
class Liturgy
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(name:"liturgyID")]
    private ?int $id = null;

    #[ORM\Column(type: Types::SMALLINT, name:"timeFK")]
    private ?int $timeFK = null;

    #[ORM\Column(type: Types::SMALLINT)]
    private ?int $week = null;

    #[ORM\Column(type: Types::SMALLINT)]
    private ?int $day = null;

    #[ORM\Column(length: 200)]
    private ?string $name = null;

    #[ORM\Column(type: Types::SMALLINT, name:"typeFK")]
    private ?int $typeFK = null;

    #[JoinTable(name: 'liturgy_homily_join')]
    #[JoinColumn(name: 'liturgyFK', referencedColumnName: 'id')]
    #[InverseJoinColumn(name: 'homilyFK', referencedColumnName: 'id')]
    #[ManyToMany(targetEntity: Homily::class)]

    public ?Collection $homilies=null;

    public function __construct() 
    {
        $this->$homilies = new ArrayCollection();
    }

    public function getHomilies(): ?Collection
    {
        return $this->homilies;
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    // ...

}

I've also tried a Bidirectional Many-to-Many relationship, as the documentation explains here.

In this case the classes look like this:

Liturgy

namespace App\Entity;

use App\Repository\LiturgyRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: LiturgyRepository::class)]
class Liturgy
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(name:"liturgyID")]
    private ?int $id = null;

    #[ORM\Column(type: Types::SMALLINT, name:"timeFK")]
    private ?int $timeFK = null;

    #[ORM\Column(type: Types::SMALLINT)]
    private ?int $week = null;

    #[ORM\Column(type: Types::SMALLINT)]
    private ?int $day = null;

    #[ORM\Column(length: 200)]
    private ?string $name = null;

    #[ORM\Column(type: Types::SMALLINT, name:"typeFK")]
    private ?int $typeFK = null;
/*
    #[JoinTable(name: 'liturgy_homily_join')]
    #[JoinColumn(name: 'liturgyFK', referencedColumnName: 'id')]
    #[InverseJoinColumn(name: 'homilyFK', referencedColumnName: 'id')]
    #[ManyToMany(targetEntity: Homily::class)]
*/

    #[ManyToMany(targetEntity: Homily::class, inversedBy: 'liturgies')]
    #[JoinTable(name: 'liturgy_homily_join')]
    public ?Collection $homilies=null;

    public function __construct()
    {
        $this->$homilies = new ArrayCollection();
    }

    public function getHomilies(): ?Collection
    {
        return $this->homilies;
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getLiturgyID(): ?int
    {
        return $this->id;
    }

    public function setLiturgyID(int $liturgyID): static
    {
        $this->id = $liturgyID;
        return $this;
    }

    public function getTimeFK(): ?int
    {
        return $this->timeFK;
    }

    public function setTimeFK(int $timeFK): static
    {
        $this->timeFK = $timeFK;
        return $this;
    }

    public function getWeek(): ?int
    {
        return $this->week;
    }

    public function setWeek(int $week): static
    {
        $this->week = $week;
        return $this;
    }

    public function getDay(): ?int
    {
        return $this->day;
    }

    public function setDay(int $day): static
    {
        $this->day = $day;
        return $this;
    }

    public function getName(): ?string
    {
        return $this->name;
    }

    public function setName(string $name): static
    {
        $this->name = $name;
        return $this;
    }

    public function getTypeFK(): ?int
    {
        return $this->typeFK;
    }

    public function setTypeFK(int $typeFK): static
    {
        $this->typeFK = $typeFK;
        return $this;
    }
}

---

Homily

namespace App\Entity;

use App\Repository\HomilyRepository;
use Doctrine\DBAL\Types\Types;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: HomilyRepository::class)]
class Homily
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(name:'homilyID')]
    private ?int $id = null;

    #[ORM\Column(type: Types::TEXT)]
    private ?string $homily = null;

    #[ManyToMany(targetEntity: Liturgy::class, mappedBy: 'homilies')]
    private Collection $liturgies;

    public function __construct() {
        $this->liturgies = new ArrayCollection();
    }

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getHomily(): ?string
    {
        return $this->homily;
    }

    public function setHomily(string $homily): static
    {
        $this->homily = $homily;
        return $this;
    }

}

My problem is that in neither case does the code work. Assuming this code in LiturgyRepository:

public function findHomily(string $time,string $week,string $day,string $cycle): ?array
 {
     $timeFK=$this->timeAssoc[$time] ?? null;
     $day=$this->dayAssoc[$day] ?? null;
     $cycle=$this->cycleAssoc[$cycle] ?? null;
     $qb= $this->createQueryBuilder('liturgy')
     ->andWhere('liturgy.id > 0')

         /* quitamos criterios de parámetros por el momento*/
         ->setMaxResults(5)
         ->getQuery()
         ->getResult()
     ;
     dd($qb);
     return $qb;
 }

If I understood correctly, if the relationship works, I should have in the homilies property a list of Homily objects, but I always get null, as you can see in the debug:

  0 => App\Entity\Liturgy {#651 ▼
    -id: 1
    -timeFK: 1
    -week: 1
    -day: 1
    -name: "Domingo I de Adviento (Año A)."
    -typeFK: 1
    +homilies: null
  }
  1 => App\Entity\Liturgy {#649 ▼
    -id: 2
    -timeFK: 1
    -week: 2
    -day: 1
    -name: "Domingo II de Adviento (Año A)."
    -typeFK: 1
    +homilies: null

I clarify that it is not a problem with the data, the tables have matching records in those relationships. For example the query:

select 
    h.homilyID 
from liturgy l 
inner join liturgy_homily_join j 
    on l.liturgyID=j.liturgyFK 
inner join homily h 
    on j.homilyFK=h.homilyID 
where 
    l.liturgyID=1;

It gives me all the rows that meet the relationship:

+----------+
| homilyID |
+----------+
|     1987 |
|     1989 |
|     1990 |
|     ...  |
+----------+

Can anyone tell me what is my mistake in implementing relationships?

Answer 1

There's a few things wrong here !

First I would prefer a bi-directional relationship with as few attributes as possible - the mapping you showed in your second example seems good (you can verify if doctrine is happy with php bin/console doctrine:schema:validate)

Your constructor is wrong and attributes ArrayCollection to the wrong variable

    public function __construct() 
    {
        // Remove the $ in front of homilies !  
        $this->homilies = new ArrayCollection();
    }

Then you should know that doctrine Collections are lazy by default (they are unpopulated until you try to use them). So just "dumping" your liturgy result won't show them.

You can check if you have the necessary results by looping over them

foreach($liturgy->getHomilies() as $homily) {...}

Or by counting

$liturgy->getHomilies()->count()