symfony querybuilder for search by relation in collection

Question

I have Entity Application with relation to Applicant

    /**
     * @ORM\ManyToOne(targetEntity=Applicant::class, inversedBy="applications")
     * @ORM\JoinColumn(nullable=false)
     */
    private $applicant;

now I try create QueryBuilder for search application by Applicant name in ApplicantRepository i have

    public function searchByName($searchString)
    {
        return $this->createQueryBuilder('a')
            ->andWhere('a.name LIKE :phrase')->setParameter('phrase', '%'.$searchString.'%')
            ->getQuery()
            ->getResult();
    }

in controller I have

$applicants = $applicantRepository->searchByName($searchString);

Now I want search Application with applicant name in this applicants collection. May I use QueryBuilder fot that?

I am trying something like this

public function getApprovedSearchByApplicants($applicants)
    {
        return $this->createQueryBuilder('a')
            ->andWhere('a.applicant IN (:applicants)')
            ->setParameter('applicants', $applicants)
            ->getQuery()
            ->getResult();
    }

Answer 1

Search by search string:

$entityManager
    ->createQuery('
        SELECT ct
        FROM App\Entity\Application ct
            JOIN ct.applicant nt
        WHERE nt.name LIKE :phrase
    ')
    ->setParameters(['phrase' => "%$searchString%"])
    ->getResult();

Search by applicants:

$entityManager
    ->createQuery('
        SELECT ct
        FROM App\Entity\Application ct
            JOIN ct.applicant nt
        WHERE nt IN (:nts)
    ')
    ->setParameters(['nts' => $applicants])
    ->getResult();

Answer 2

so, looking to your configuration, your Application::$applicant === Applicant::$name, just because Application::$applicant property has Applicant::$id value, by default. You can check the documentation.

So, this way, you need to make smth like this:

/**
 * @ORM\ManyToOne(targetEntity=Applicant::class, inversedBy="applications")
 * @ORM\JoinColumn(name="applicant_name", referencedColumnName="name", nullable=false)
 */
private $applicant;

It should work.

UPDATE after question update and discussions:

So, the problem was in the testing data in the database. Bad question.

Answer 3

I did not test it, but something like the following code should do the trick. It is almost the same solution as goulashsoup proposed, but without typing raw DQL.

/**
 * @param array|Applicant[] $applicants
 *
 * @return array|Application[]
 */
public function findByApplicants(array $applicants): array
{
    $qb = $this->createQueryBuilder('a')

    return $qb->innerJoin('a.applicant', 'at')
        ->where(
            $qb->expr()->in('at.id', ':applicants')
        )
        ->setParameter('applicants', $applicants)
        ->getQuery()
        ->getResult();
}

I don't think you need to name the function wtih "ApprovedSearch" since the method is only aware of a list of Applicant for whom you want the list of Application.