Question 42026 in PHP Question posted in
Official manual can be found here.

QueryBuilder with a relationship in Symfony – PHP

I’m coding in Symfony a search bar working with a query builder for an entity named "Structure" which is related to an entity "Partenaire" (OneToMany), it’s working great but the problem is that it shows all the structures and I need to display only the structures related to the Partenaire. If someone can help me to solve this issue, thank you.

PartenaireController.php:

#[Route('/{id}', name: 'app_partenaire_show', methods: ['GET', 'POST'])]
public function show(Partenaire $partenaire, EntityManagerInterface $entityManager, PartenaireRepository $partenaireRepository,Request $request, PartenairePermissionRepository $partenairePermissionRepository, StructureRepository $structureRepository): Response
{
    $getEmail = $this->getUser()->getEmail();
    $partenaireId = $entityManager->getRepository(Partenaire::class)->findOneBy([
        'id' => $request->get('id')
    ]);

    $search2 = $structureRepository->findOneBySomeField2(
        $request->query->get('q')
    );
    
    return $this->render('partenaire/show.html.twig', [
        'partenaire' => $partenaire,
        'permission'=>$partenairePermissionRepository->findBy(
            ['partenaire' => $partenaireId],
        ),
      'structures'=>$search2, // show all the structures

      /*  'structures'=>$structureRepository->findBy( // show the structure linked to the partenaire but doesn't work with the search
            ['partenaire' => $partenaireId],
            [],
        ),*/
        'email'=>$getEmail,
    ]);
}

StructureRepository.php :

public function findOneBySomeField2(string $search2 = null): array
{
    $queryBuilder =  $this->createQueryBuilder('q')
        ->orderBy('q.id' , 'ASC');

    if ($search2) {
        $queryBuilder->andWhere('q.Adresse LIKE :search')
            ->setParameter('search', '%'.$search2.'%');
    }

    return $queryBuilder->getQuery()
        ->getResult()
        ;
}
Tags:

2

Answers


  1. 0

    as it’s a One To Many relation, you should join the right table and add a where statement in your structure query like :

    public function findByPartenaireWithFilter(Partenaire $partenaire, string $search2 = null): array
{
    $queryBuilder =  $this->createQueryBuilder('q')
        ->leftJoin('q.partenaire','p')
        ->where('p.partenaire.id = :partenaireId')
        ->setParameter('partenaireId', $partenaire->getId())
        ->orderBy('q.id' , 'ASC');

    if (null !== $search2) {
        $queryBuilder->andWhere('q.Adresse LIKE :search')
            ->setParameter('search', '%'.$search2.'%');
    }

    return $queryBuilder
        ->getQuery()
        ->getResult();
}
    Login or Signup to reply.
  2. 0

    You need to limit your results to the desired Partenaire by adding a condition. You can achieve this by simply passing another argument to the repository function.

    I’ve removed the commented code in the controller and the loading of the Partenaire entity, as it should be already loaded thanks to the magic of param converters.

    #[Route('/{id}', name: 'app_partenaire_show', methods: ['GET', 'POST'])]
public function show(Partenaire $partenaire, EntityManagerInterface $entityManager, Request $request, PartenairePermissionRepository $partenairePermissionRepository, StructureRepository $structureRepository): Response
{
    $getEmail = $this->getUser()->getEmail();
    // The Partenaire should already be loaded

    $result = $structureRepository->findByPartenaireWithFilter(
         $partenaire,
         $request->query->get('q')
    );
    
    return $this->render('partenaire/show.html.twig', [
        'partenaire' => $partenaire,
        'permission' => $partenairePermissionRepository->findBy(
            ['partenaire' => $partenaireId],
        ),
        'structures' => $search2, // show all the structures
        'email' => $getEmail,
    ]);
}

    As for the repository, it’s pretty straightforward: pass the loaded partenaire and add a where clause.

    public function findByPartenaireWithFilter(Partenaire $partenaire, string $search2 = null): array
{
    $queryBuilder =  $this->createQueryBuilder('q')
        ->where('q.partenaire = :partenaire')
        ->setParameter('partenaire', $partenaire)
        ->orderBy('q.id' , 'ASC');

    if (null !== $search2) {
        $queryBuilder->andWhere('q.Adresse LIKE :search')
            ->setParameter('search', '%'.$search2.'%');
    }

    return $queryBuilder
        ->getQuery()
        ->getResult();
}

    As for the permissions key in the controller, I think what you are trying to do, I’d recommend looking into security voters.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search