How to check if multiple records exist on database (laravel doctrine)

Question

I want to confirm whether a collection of users exist on the database. So, say I have an array:

$data = [
    [
        'firstName' => 'Alastair',
        'lastName' => 'Appleby'
    ],
    [
        'firstName' => 'Celine',
        'lastName' => 'Wilkinson'
    ]
];

I want to return those two users, if they exist. I can't seem to figure out an efficient way of doing this effectively. I tried running a native query, but that returned nothing and I think it's because of the way I've bound the parameters. My attempt:

    $where = [];
    $params = [];

    foreach($data as $n => $u) {
        $where[] = '(first_name = ? AND last_name = ?)';
        $params[$n + $n + 1] = $u['firstName'];
        $params[$n + $n + 2] = $u['lastName'];
    }

    $where = implode(' OR ', $where);

    $query = "SELECT * FROM users WHERE $where";

    $q = $this->em->createNativeQuery($query, new ResultSetMapping());
    $q->setParameters($params);

    return $q->getArrayResult();

Answer 1

You need to use the query builder expression methods, like so;

$qb = $this->em->createQueryBuilder();

$where = [];
$params = [];

$qb->select('u')
    ->from(User::class, 'u');

foreach($data as $n => $u) {
    $where[] = $qb->expr()->andX(
        $qb->expr()->eq('u.firstName', ":firstName$n"),
        $qb->expr()->eq('u.lastName', ":lastName$n")
    );

    $params["firstName$n"] = $u['firstName'];
    $params["lastName$n"] = $u['lastName'];
    }

$qb->where($qb->expr()->orX(
    $where
))->setParameters($params);

return $qb->getQuery()->getArrayResult();