How to find by referenced document in Doctrine ODM with MongoDB?

Question

I have one document in my "params" collection like this:

{
  "_id": ObjectId("4d124cef3ffcf6f410000037"),
  "code": "color",
  "productTypes": [
    {
      "$ref": "productTypes",
      "$id": ObjectId("4d120a2d2b8d8d3010000000"),
      "$db": "test"
    }
  ]
}

the referenced document is this:

{
  "_id": ObjectId("4d120a2d2b8d8d3010000000"),
  "code": "car"
}

I'm using DoctrineODM to fetch the "param" documents which referenced "productType" is "car". I'm using this code:

$query = $dm->createQuery('Cms\Model\Param');
$query->field('productTypes.code')->equals('car');
$result = $query->execute();
var_dump($result);

but the result is an empty array. How can i do this?

Caraway

What database are you connecting to? It's not 'test' or 'admin' right? – Lylalyle

no. it's not test or admin. i changed the name. – Caraway

Can we see your document classes? – Illinois

Answer 1

If you using ReferenceMany or ReferenceOne you can't query by any reference document field, except reference document id.

If you need query on code from referenced collection you should use EmbedMany instead of ReferenceMany.

In this case your document will be:

{
  "_id": ObjectId("4d124cef3ffcf6f410000037"),
  "code": "color",
  "productTypes": [
     {
       "_id": ObjectId("4d120a2d2b8d8d3010000000"),
       "code": "car"
     }
  ]
}

And following query will work:

$query = $dm->createQuery('Cms\Model\Param');
$query->field('productTypes.code')->equals('car');
$result = $query->execute();
var_dump($result);

Also if your ProductType code is unique you can use it instead of MongoId, in this case you can query on $id:

{
  "_id": ObjectId("4d124cef3ffcf6f410000037"),
  "code": "color",
  "productTypes": [
    {
      "$ref": "productTypes",
      "$id": 'car',
      "$db": "test"
    }
  ]
}

Referenced document:

{
  "_id": 'car'
}

Query:

$query->field('productTypes.$id')->equals('car');

Britishism

This doesn't address the question. You can't use Embed* for everything- Reference* is there for a reason. The real problem as I see it is that you have to peek into actual DB on your own to realize that references are mapped as $varname.id. So you can query: ...->findBy(array('referenceField.id' => managedDoc->getId()) Still this is definitely not a good solution because one shouldn't care or even know about internal way Doctrine does Reference* associations... – Helainehelali

Actually, I take that back - partially. DocumentPersister->prepareQueryValue() in Doctrine\ODM\MongoDB\Persisters in DoctrineODM seems to do the magic! It converts 'referenceVar.idField' in the find* queries so that regardless of the underlying implementation of Reference* it will work. I.e. it seems that findOneBy('referencedDoc.id' -> ...some_id...) is the intended usage... I am continually amazed at how much work has gone into building Doctrine ODM for Mongo. It's so huge and so well thought out! – Helainehelali

@JakubP. How about adding these comments as an answer. – Flamen

Just a heads up to anyone wondering in the future... you have to create the index for productTypes.$id within Mongo or else DoctrineODM throws an exception saying there is no index (despite this working in Mongo CLI) – Bertha

Answer 2

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

$productType = $dm->getRepository('Cms\Model\ProductTypes')->findOneByCode('car');

$queryBuilder = $dm->getRepository('Cms\Model\Param')->createQueryBuilder()
                   ->field('productTypes')->references($productType);

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

PS: use includesReferenceTo() a @MongoDB\ReferenceMany

Captive

Answer 3

->field('productTypes.code')->equals(new \MongoRegex('/.*car.*/i'))

Internode