Doctrine2 query(Builder) loses relations after using "where" and "in" clause

Question

I have two entities, let's call them "Girl" and "Feature". Entity "Girl" is in undirectional ManyToMany relation with "Feature".

I have a page with simple search form. I want to search for Girls by Features. What I get after form submission on the server side is a list of Features id's, and what I should return is a list of Girls that have selected Features listed with all other Features, that girl have.

Let's look at this: ╔════════════╦═══════════════╦═══════════════╦═══════════════╗ ║ ║ (1) Feature 1 ║ (2) Feature 2 ║ (3) Feature 3 ║ ╠════════════╬═══════════════╬═══════════════╬═══════════════╣ ║ (1) Girl 1 ║ Yes ║ Yes ║ No ║ ║ (2) Girl 2 ║ Yes ║ No ║ Yes ║ ╚════════════╩═══════════════╩═══════════════╩═══════════════╝

Numbers in brackets are entities id.

What I do at first is:

$qbd = $this->getQbd('AppBundle:Girl', 'g')
        ->select('g, gf')
        ->leftJoin('g.features', 'gf');

After getting data (features ids) from URL (becausce form use GET method) and putting them into $features array, I perform something like this:

if (!empty($features)) {
    $qbd->where(
        $qbd->expr()->in('gf.id', ':features')
    )->setParameter('features', $features);
}

Simple as possible.

Everything is fine. Almost.

When I try to perform search for Girls with Features id equals 2 and 3, I get in result both Girls (which is good). Sadly, each one of them have only feature 2 and 3 (for this case; when I'm trying to perform for each of them $girl->getFeatures()), even if both of them should also contains reference to feature 1.

What I'm doing wrong? I've already tried to build others, more complex queries for that case, but with each of them at the end I got the same problem.

Answer 1

You can make use of the "MEMBER OF" DQL construction.

For example you can do like so:

$qb->from('AppBundle:Girl', 'g')
   ->select('g')
   ->andWhere(':feature MEMBER OF g.features')
   ->setParameter('feature', $feauture);

The bad thing it work only with single feature. For example, if u need to get girls having feature A and B u need to do like so:

$qb->from('AppBundle:Girl', 'g')
   ->select('g')
   ->andWhere(':featureA MEMBER OF g.features')
   ->setParameter('featureA', $featureA)
   ->andWhere(':featureB MEMBER OF g.features')
   ->setParameter('featureB', $featureB);

It is the cleanest way to search for some entity in collection that I know. But, if you need to search for some entity (girl) that have many related entities (features) you gonna face some performance issues using this approach. In that case maybe it is better to make use of sub-queries.