Doctrine ODM Query builder nested subfield finding

Question

I have the follow problem with nested finding of collection element:

class User
{
    /*
     * @MongoDB\Id(strategy="auto")
     */
    protected $id;
}

class Network
{
    /*
     * @MongoDB\ReferenceOne(targetDocument="User")
     */
    protected $owner;
}

class Connection
{
    /*
     * @MongoDB\ReferenceOne(targetDocument="Network")
     */
    protected $network;
}

How I can find all user own network connections (by user id) in Doctrine ODM Query builder? P.S. Native mongodb query will accept too.

Answer 1

public function findByUserId($userId)
{
    return $this->dm->getRepository(Connection::class)->findBy([
        'network.owner.$id' => new MongoId($userId),
    ]);
}

Answer 2

Unfortunately you can't query by the fields of referenced documents directly, it's better handled by relational databases. In MongoDB, this is of course possible but requires multiple queries: first you find the networks user belong to, then you should find the connections of the networks.

Of course, it's enough to get the list of network identifiers to make the connection query. This is easily done with the help of a query builder:

$networkQb = $this->dm->getRepository(Network::class)->createQueryBuilder();
$networkQb->field('owner.$id')->equals($userId);
$networkQb->select('_id'); // Limit results to the ID of the network only
$networkQb->hydrate(false); // Don't return Network objects but only plain array

$networkResults = $networkQb->getQuery()->toArray();
$networkIdList = array_map(function($result) { return $result['_id']; }, $networkResults); // This converts array(array("_id" => "1234"), array("_id" => "5678")) to array("1234","5678")

// Then we'll make the actual query for the connections, based on the id list of the networks
$connectionQb = $this->dm->getRepository(Connection::class)->createQueryBuilder();
$connectionQb->field('network.$id')->in($networkIdList);

$connections = $connectionQb->getQuery()->toArray();

This kind of query is still relatively fast as far as there are only a few networks per user.

Answer 3

You must use the references() method of Query Builder for a @MongoDB\ReferenceOne like https://doctrine-mongodb-odm.readthedocs.org/en/latest/reference/query-builder-api.html

$user = $dm->getRepository('User')->findOneById($userId);

$queryBuilder = $dm->getRepository('Network')->createQueryBuilder()
                   ->field('owner')->references($user);

$ownNetworkConnections = $queryBuilder->getQuery()->execute();

PS: use includesReferenceTo() a @MongoDB\ReferenceMany