Doctrine: Sub-query where main entity id is in array of sub-query

Question

I would like to search for people who are not allocated to a room. I made the following query:

public function findByWithoutRoom()
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb2 = $this->getEntityManager()->createQueryBuilder();

    $qb
        ->select('p')
        ->from('MyPeopleBundle:Person', 'p')

        ->where(
            $qb->expr()->exists(
                $qb2->select('r')
                    ->from('MyAccommodationBundle:Room', 'r')
                    ->andWhere($qb2->expr()->like('r.currentPeople', ':person'))
                    ->setParameter('person', '%i:'.$person_id.';%')

                    ->getDQL()
            )
        )

    $result = $qb->getQuery()->execute();
    return $result;
}

How can I have p.id instead of person_id? Note:The currentPeople property is of type "array" (not "simple_array")

UPDATE:

I also tried the following:

public function finByWithoutRoom()
{
    $qb = $this->getEntityManager()->createQueryBuilder();
    $qb
        ->select('p')
        ->from('MyPeopleBundle:Person', 'p')
        ->leftJoin('MyAccommodationBundleV2:Room', 'r')
        ->andWhere($qb->expr()->like('r.currentPeople', '%i:p.id%'));

    $result = $qb->getQuery()->execute();
    return $result;
}

however this gave me the following error:

[Syntax Error] line 0, col 114: Error: Expected StateFieldPathExpression | string | InputParameter | FunctionsReturningStrings | AggregateExpression, got '%'

score 2 · Answer 1 · answered Sep 16 '16 at 08:08

2

You can use directly the alias of the main query, as example:

$qb
    ->select('p')
    ->from('MyPeopleBundle:Person', 'p')
    ->where(
        $qb->expr()->isNotNull(
            $qb2->select('r')
                ->from('MyAccommodationBundle:Room', 'r')
                ->andWhere($qb->expr()->like('r.currentPeople', 'p.id'))

                ->getDQL()
        )
    )

    ->setParameter('from', $from)
    ->setParameter('to', $to);

I suggest to use an not exists instead of is not null (I think is the same result however). As Example:

    $qb->andWhere($qb->expr()->not($qb->expr()->exists($qb2->getDQL())));

Hope this help

answered Sep 16 '16 at 08:08

Matteo

37,680
11
100
115

1

thanks! I am currently checking this. Am wondering if there is a person with id 100 and one person with id 1000, it may return as "not null" while this may not be so. p.id may also occur elsewhere in the array. will get back to this shortly. – xfscrypt Sep 16 '16 at 08:12
it only works when i do ->setParameter(':myid', '%i:556%') where 556 is a hardcoded p.id. is there a way i can have the wildcards added in the format you have specified? ($qb->expr()->like('r.currentPeople', 'p.id')) – xfscrypt Sep 16 '16 at 08:26
we could use SQL concat as described [here](http://stackoverflow.com/a/4420559/2270041) but depends of the version of doctrine you use if have this [pr](https://github.com/doctrine/doctrine2/pull/1397). – Matteo Sep 16 '16 at 08:56

Doctrine: Sub-query where main entity id is in array of sub-query

1 Answers1