MongoDB filter by reference's field (ORM)

Question

Let's say I have the document city and the document country.

city' references to country by country: ObjectId("xxxx")

How can I find cities by countries' field, eg. db.Cities.find({"country.code":"US"}

Is it possible? I think not, but might it be possible using MongoDB ORM for PHP?

I tried

$qb = $this->database->createQueryBuilder(self::NAMESPACE_CITY);
$qb->field('country.code')->equals("US")`

My City entity contains

/** @ODM\ReferenceOne(targetDocument="\Doctrine\Documents\Country", simple=true) */
protected $country;

Document example:

<?php

namespace Doctrine\Documents;

use Doctrine\ODM\MongoDB\Mapping\Annotations as ODM;
use Application\Doctrine\Documents\Document;

/** @ODM\Document(collection="Cities") */
class City extends Document {

    /** @ODM\Id */
    protected $_id;

    /** @ODM\ReferenceOne(targetDocument="\Doctrine\Documents\Country", simple=true) */
    protected $country;

}

This is going to be a bit of an opinionated comment: Document store databases are terrible for any sort of business logic with **relations**. For your interest on this subject here's a [good article](http://www.sarahmei.com/blog/2013/11/11/why-you-should-never-use-mongodb/) (with a terrible title). — Scopey, Jan 29 '15 at 23:13
Hehe, yes, I know that they're terrible for this very well... I hoped it may be solved somehow, at least via ORM... — simPod, Jan 29 '15 at 23:15

score 1 · Accepted Answer · edited May 23 '17 at 12:06

Querying for city documents by country code in a single query would require joining on both collections, which is not supported. You could break this into two queries by first finding the country with the given code, which I assume is a unique string. That query would yield the country's ObjectId, which you could then use for querying the cities collection:

{ "country": ObjectId(...) }

If, for some reason, you wanted to find cities by some non-unique value on country (e.g. continent, substring match on the name), this could still be done in two queries. The first would again find ObjectIds for any countries matching the criteria. The second query would then use the $in operator with an array of ObjectIds:

{ "country": { "$in": [ ObjectId(...), ObjectId(...), ... ]}}

If you're using Doctrine MongoDB ODM, the query builder supports this via the references() and includesReferenceTo() methods in Expr.php (also exposed through Builder.php). references() and includesReferenceTo() can be used to match on a single document reference existing in a ReferenceOne or ReferenceMany relationship, respectively. They also correctly handle simple (i.e. just ObjectId) and complex (i.e. DBRef) references.

That said, the ODM query builder does not have a method for matching against multiple references (note that both of the aforementioned methods take a single document argument). Since you're using simple references, it should be easy to do this with in(), as I mentioned in the second paragraph; however, the query would be more complicated if we were working with DBRefs (we'd need to mix $elemMatch and $in, as discussed in this answer). I've opened issue #1024 on the ODM project to track this feature request.

score 0 · Answer 2 · answered Oct 09 '15 at 13:53

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

$country = $dm->getRepository('Country')->findOneByCode('US');

$queryBuilder = $dm->getRepository('City')->createQueryBuilder()
                   ->field('country')->references($country);

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

PS: use includesReferenceTo() a @MongoDB\ReferenceMany

MongoDB filter by reference's field (ORM)

Document example:

2 Answers2