Problem with specific entity relationship in Symfony 6.3

We are gradually rewriting a very old application into the newer Symfony framework, while still running the old application in parallel until more of it is rewritten.

I'm trying to recreate the old database in Symfony, and so far, most of the entities are OK.

However, I am facing an issue with one particular relationship.

I have an entity called DIET and another one called ModelGallery.

The relationship looks something like this:

ModelGallery.item_id = diet.id AND ModelGallery.model = 'DIET'

I'm trying to figure out how to resolve this at the entity level - performing a join on two columns. How can I achieve this in Symfony? Any help or guidance is much appreciated.

I must adjust symfony code to existing database.

I create custom function in repository

 public function getDietWithModelGalleries(int $id)
   {
      $qb = $this->createQueryBuilder('d');

      $query = $qb->select('d', 'mg')
          ->leftJoin('App\Entity\ModelGallery', 'mg', Join::WITH, 'd.id = mg.itemId AND mg.model = :model')
          ->setParameter('model', 'DIET')
          ->where('d.id = :id')
          ->setParameter('id', $id)
          ->getQuery();

      return $query->getResult();
   }

But I want to make it on Entity-Level

To define this relationship at the entity level in Symfony, you can use a custom repository method and doctrine extensions.

First, define a many-to-one relationship from ModelGallery to Diet:

// ModelGallery entity

/** 
 * @ORM\ManyToOne(targetEntity="Diet")
 * @ORM\JoinColumns({
 *   @ORM\JoinColumn(name="item_id", referencedColumnName="id"),
 *   @ORM\JoinColumn(name="model", referencedColumnName="model") 
 * })
*/
private $diet;

This will create the join condition on item_id and model.

Next, create a repository class for ModelGallery and add a custom method to retrieve galleries for a diet:

// ModelGalleryRepository

public function findByDiet(Diet $diet) 
{
  return $this->createQueryBuilder('mg')
    ->andWhere('mg.diet = :diet')
    ->setParameter('diet', $diet)
    ->getQuery()
    ->getResult();
}

Then in your controller or service, you can do:

$galleries = $galleryRepository->findByDiet($diet);

This will handle the join behind the scenes. Make sure to also add the diet property to ModelGallery for two-way association.