Doctrine2 Query Builder - Associative Data not hydrating correctly

Question

I've got a somewhat complex query I'm writing on an X-Cart project (utilizes doctrine as the ORM). I have to query/pull records from several tables and ensure I'm not getting back irrelevant/empty results in the associated data. Here's what I'm running into:

I have several inner joins that should be only pulling records that have associated data. I've had the query spit out the raw SQL that it generates, and it works 100% when run directly against the MySQL database.

I believe the problem is occurring when hydrating the base models' associated data.

public function getAlbums() {
    // Query Parameters
    $params = $this->params;

    // Base Query
    // TO DO: Associated data is not being hydrated properly by the joins,
    $query = \XLite\Core\Database::getRepo('\XLite\Module\CodeSmith\Albums\Model\Album')
        ->createQueryBuilder('album')
        ->linkInner('album.songs', 'song')
        ->linkInner('song.song_tracks', 'song_track')
        ->linkInner('song_track.album_track', 'track');

    // Init conditions array
    $conditions = [];

    // If school_type is present, check against song's school type
    if(isset($params['school_type'])){
        $conditions[] = $query->expr()->eq('song.school_type', $params['school_type']);
    }

    // If track type is present, check against album_track's track type
    if(isset($params['track_type'])){
        $conditions[] = $query->expr()->eq('track.track_type', "'{$params['track_type']}'");
    }

    // If album name is present, generate OR expression to check against first letter of album name
    // i.e. (first_letter = A OR first_letter = B OR first_letter = C)
    if(isset($params['album_name'])){
        $orX = $query->expr()->orX();
        $name_conditions = array_map(function($letter) use ($query){
            return $query->expr()->eq($query->expr()->substring('album.name', 1, 1), "'$letter'");
        }, explode(',', $params['album_name']));
        $orX->addMultiple($name_conditions);
        $conditions[] = $orX;
    }

    if(isset($params['category'])){
        // TO DO
    }

    if(isset($params['text'])){
        // TO DO
    }


    // Aggregate conditions array with AND
    if(count($conditions)){
        $conditions = call_user_func_array(array($query->expr(), 'andx'), $conditions);
        $query->where($conditions);
    }


    // Fetch Results
    $albums = $query->getQuery()->getResult();


    return $albums;
}

The associated data is simply coming back with ALL related data. I don't want ALL related entries, only ones that fit the search criteria. Is this an issue with Doctrine or is there something I'm not doing right?

Answer 1

Ended up figuring it out. I needed to utilize a "fetch" join to ensure the associated data hydrated correctly. Doctrine's docs only show examples of it via createQuery and not via the query builder.

This was my old code:

$query = \XLite\Core\Database::getRepo('\XLite\Module\CodeSmith\Albums\Model\Album')
    ->createQueryBuilder('album')
    ->linkInner('album.songs', 'song')
    ->linkInner('song.song_tracks', 'song_track')
    ->linkInner('song_track.album_track', 'track');

Here is how to do it via createQueryBuilder:

$query = \XLite\Core\Database::getEM()->createQueryBuilder()
    ->select('album, song, song_track, track')
    ->from('\XLite\Module\CodeSmith\Albums\Model\Album', 'album')
    ->innerJoin('album.songs', 'song')
    ->innerJoin('song.song_tracks', 'song_track')
    ->innerJoin('song_track.album_track', 'track');

The select statement is what is ensuring the fetch.