How to project more than one value with doctrine mongodb query builder selectElemMatch

Question

I have the following kind of data in my mongo database. The property "values" consists of an array of attributes. "values" is a property of a product, which also has some other properties like "normalizedData". But the structure of "values" is what gives me a headache.

"values" : [
    {
        "_id" : ObjectId("5a09d88c83218b814a8df57d"),
        "attribute" : NumberLong("118"),
        "entity" : DBRef("pim_catalog_product", ObjectId("59148ee283218bb8548b45a8"), "akeneo_pim"),
        "locale" : "de_AT",
        "varchar" : "LED PAR-56 TCL 9x3W Short sw"
    },
    {
        "_id" : ObjectId("5a09d88c83218b814a8df57a"),
        "attribute" : NumberLong("118"),
        "entity" : DBRef("pim_catalog_product", ObjectId("59148ee283218bb8548b45a8"), "akeneo_pim"),
        "locale" : "de_DE",
        "varchar" : "LED PAR-56 TCL 9x3W Short sw"
    },
    {
        "_id" : ObjectId("5a09d88c83218b814a8df57c"),
        "attribute" : NumberLong("184"),
        "entity" : DBRef("pim_catalog_product", ObjectId("59148ee283218bb8548b45a8"), "akeneo_pim"),
        "locale" : "de_AT",
        "boolean" : false
    },
    {
        "_id" : ObjectId("5a09d88c83218b814a8df585"),
        "attribute" : NumberLong("118"),
        "entity" : DBRef("pim_catalog_product", ObjectId("59148ee283218bb8548b45a8"), "akeneo_pim"),
        "locale" : "fr_FR",
        "varchar" : "LED PAR-56 TCL 9x3W Short sw"
    },
    {
        "_id" : ObjectId("5a09d88c83218b814a8df584"),
        "attribute" : NumberLong("121"),
        "entity" : DBRef("pim_catalog_product", ObjectId("59148ee283218bb8548b45a8"), "akeneo_pim"),
        "locale" : "fr_FR",
        "varchar" : "Eurolite LED PAR-56 TCL 9x3W Short sw"
    },
    {
        "_id" : ObjectId("5a09d88c83218b814a8df574"),
        "attribute" : NumberLong("207"),
        "entity" : DBRef("pim_catalog_product", ObjectId("59148ee283218bb8548b45a8"), "akeneo_pim"),
        "varchar" : "51913611"
    },
]

A couple of things to notice about this extract from the dataset:

attributes with their ID ("attribute") can appear multiple times, like 118 for example.
attributes do not always have the same subset of properties (see 207 and 121 for example).
if an attribute is present multiple times (like 118) it should differ in the "locale" property at least.

Now I need the doctrine mongoDB query builder to project the following result:

I want only those attributes to be present in the result that contain one of the IDs specified by the query (e.g. array(118, 184)).
If the attribute exists multiple times, I want to see it multiple times.
If the attribute exists multiple times, I want to limit the number by an array of locales given.

So an example query would be: return all attributes inside "values" that have eigther 118 or 184 as the "attribute" property, and (if specified) limit the results to those attributes, where the locale is either "de_DE" or "it_IT".

Here is what I have tried so far:

$qb = $productRepository->createQueryBuilder();
$query = $qb
    ->hydrate(false)
    ->select(array('normalizedData.sku'))
    ->selectElemMatch(
        'values',
        $qb->expr()->field('attribute')->in(array(117, 110))->addAnd(
            $qb->expr()->field('locale')->in(array('it_IT', 'de_DE'))
        ))
    ->field('_id')->in($entityIds)
    ->field('values')->elemMatch($qb->expr()->field('attribute')->in(array(117, 110)))
    ->limit($limit)
    ->skip($offset);

This query always returns only one attribute (no matter how many times it is present within the "values" array) per product. What am I doing wrong here?

EDIT: My MongoDB version is 2.4.9 and doctrine-mongo-odm is below 1.2. Currently I cannot update either.

score 1 · Answer 1 · answered Jan 19 '18 at 11:28

1

You can try below aggregation query in 3.4 mongo version. $elemMatch by design returns first matching element.

You will need $filter to return multiple matches.

$match to limit the documents were values has atleast one value where it contains both attribute in [118,184] and locale in ["de_DE","it_IT"] followed by $filter to limit to matching documents in a $project stage. You can add $limit and $skip stage at the end of aggregation pipeliine same as what you did with regular query.

db.col.aggregate([
  {"$match":{
    "values":{
      "$elemMatch":{
        "attribute":{"$in":[118,184]},
        "locale":{"$in":["de_DE","it_IT"]}
      }
    }
  }},
  {"$project":{
    "values":{
      "$filter":{
        "input":"$values",
        "as":"item",
        "cond":{
          "$and":[
            {"$in":["$$item.attribute",[118,184]]},
            {"$in":["$$item.locale",["de_DE","it_IT"]]}
          ]
        }
      }
    }
  }}
])

You can use AggregationBuilder to write the query in doctrine.

answered Jan 19 '18 at 11:28

s7vr

73,656
11
106
127

Hi thank you, that looks like just what I need. Is there a way to do this without the aggregation builder? I am afraid I have to use doctrine mondo odm below 1.2 :( – Micha Jan 19 '18 at 12:47
Np. I don't think so unless you do it on client side. I'm not familiar with mongo odm to give you any suggestions. Can you run direct aggregation query using mongo php driver ? – s7vr Jan 19 '18 at 12:55
The Mongo DB version I am stuck with is 2.4.9. I am screwed, right? – Micha Jan 19 '18 at 13:03
No, you should be okay. I can update the answer for lower versions but you need to find a way to run aggregation query with or without odm. Let me know. – s7vr Jan 19 '18 at 13:05

How to project more than one value with doctrine mongodb query builder selectElemMatch

1 Answers1