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 '%'

Matteo · Accepted Answer · 2016-09-16 08:08:43Z

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, 2016 at 8:08

Matteo

39.8k11 gold badges110 silver badges129 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

xfscrypt Over a year ago

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 Over a year ago

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'))

Matteo Over a year ago

we could use SQL concat as described here but depends of the version of doctrine you use if have this pr.

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related