How to query by a specific field/s in a list of embedded documents ?

[partyzan]

Taking a simple structure of:

class Transaction(Document):

    transaction_id = StringField(required=True, unique=True)
    title = StringField(required=True)
    details = StringField(required=True)

class Device(Document):

    __collection__ = "devices"

    device_id = StringField(required=True, unique=True)
    device_name = StringField()
    transactions = ListField(EmbeddedDocumentField(Transaction))

I am trying to understand how can I query the devices collection based on the fields of the embedded transaction.

In mongo shell the simple find does the trick:

db.devices.find({"transactions.transaction_id":"c5947d56-2a2f-45b3-8518-a65a78cd43e7"})

I have tried the following:

tid ="..."
Device.objects.filter(transactions__transaction_id=tid).find_all()

How can I perform such a query through motorengine ? How can I perform a more complicated query to match on multiple fields ($elemMatch style) ?

[heynemann]

Device.objects.filter(transactions__transaction_id=tid).find_all()

Should work... ;/ If you pass a single element we'll try to find the element in the collection, if you pass a list we'll match only if the entire list matches.

[partyzan]

Unfortunately it doesn't :/ I am using the following to test:

    @gen_test()
    def test_embedded_lookup(self):

        class Embedded(Document):
            eid = StringField()

        class Base(Document):
            bid = StringField()
            embedded_items = ListField(EmbeddedDocumentField(Embedded))

        base = yield Base.objects.create(bid="b1", embedded_items=[Embedded("e1")])
        result = yield Base.objects.filter(embedded_items__eid="e1").find_all()
        self.assertIsNotNone(result)
        self.assertEqual(len(result), 1)
        self.assertEqual(result[0].bid, base.bid)
        self.assertEqual(len(result[0].embedded_items), 1)
        self.assertEqual(result[0].embedded_items[0].eid, "e1")

This kind of query (not using Q) fails with the following error:

ValueError: Invalid filter 'embedded_items__eid': Invalid operator (if this is a sub-property, then it must be used in embedded document fields).

If using the same query in Q and passing it to filter it just returns an empty list.

[partyzan]

This syntax works for a single embedded document query, but not for list of embedded documents.

    @gen_test
    def test_embedded_lookup(self):

        class Embedded(Document):
            eid = StringField()

        class Base(Document):
            bid = StringField()
            embedded = EmbeddedDocumentField(Embedded)

        base = yield Base.objects.create(bid="b1", embedded=Embedded(eid="e1"))
        self.assertIsNotNone(base)
        self.assertEqual(base.bid, "b1")
        self.assertIsNotNone(base.embedded)
        self.assertEqual(base.embedded.eid, "e1")
        result = yield Base.objects.filter(embedded__eid="e1").find_all()
        self.assertIsNotNone(result)
        self.assertEqual(len(result), 1)
        self.assertEqual(result[0].bid, base.bid)
        self.assertIsNotNone(result[0].embedded)
        self.assertEqual(result[0].embedded.eid, base.embedded.eid)

[partyzan]

I have verified that the same syntax does work for mongoengine:

class Embedded(EmbeddedDocument):
   eid = StringField()

class Base(Document):
    embedded = ListField(EmbeddedDocumentField(Embedded))
    bid = StringField()

base = Base(bid="b1",embedded=[Embedded(eid="e1")])
base.save()
result = Base.objects(embedded__eid="e1")

The Base object is found and retrieved.

Can I work around this issue in some way ? Can I pass a raw query to motorengine ?

[heynemann]

I'm fixing it, but first I'm introducing a way of doing raw queries, so we can work around other issues that may arise.

I'm doing it right now, will have a version by tonight.

[partyzan]

Awesome! Thanks.

[heynemann]

Released on version 0.8.7. Can you please confirm if this has fixed it? Anyways, I updated the docs with how to do raw queries. Sorry for all the inconvenience this has caused you.

[heynemann]

(forgot to link the docs)

http://motorengine.readthedocs.org/en/latest/getting-and-querying.html#querying-with-raw-queries

[partyzan]

Thanks! It's working now.