InvalidQueryError with query like: (a='x' | b='x') & (a='y' | b='y')

[stalnacke]

I'm trying to create a query in mongoengine like this mongo query:

db.person.find( {
    { mother: { $ref: "person" , $id: ObjectId('4de504a5cd755c5ac9000000') } },
    $or: [
        { name: /bob/i },
        { email: /bob/i }
    ],
    $or: [
        { name: /dylan/i },
        { email: /dylan/i }
    ]
});

In mongoengine I'm trying to do this by:

persons = Person.objects.filter(mother = bobMother)
persons.filter(Q(name__icontains = "bob") | Q(email__icontains = "bob"))
persons.filter(Q(name__icontains = "dylan") | Q(email__icontains = "dylan"))

This raises an "InvalidQueryError Conflicting values for name" since the query is transformed into something like:

$or: [
        { name: /bob/i, mother: { $ref: "person", $id: ObjectId('4de504a5cd755c5ac9000000') }, name: /dylan/i },
        { name: /bob/i, mother: { $ref: "person", $id: ObjectId('4de504a5cd755c5ac9000000') }, email : /dylan/i },
        { email: /bob/i, mother : { $ref: "person",$id : ObjectId('4de504a5cd755c5ac9000000') }, name: /dylan/i },
        { email: /bob/i, mother : { $ref: "person",$id : ObjectId('4de504a5cd755c5ac9000000') }, email : /dylan/i }
]

Probably this is because the validator tries to avoid using the same key twice in a dict, like in the first and fourth line in the $or-query. Is there another way to write this query?

I'm using: mongoengine: 0.4 pymongo: 1.11 Django: 1.3 python: 2.6 Mac OSX

[rozza]

Unfortunately there is no way to programatically convert that query into a valid mongo query.

You can only do one regex match on a field - so to achieve your aim you'd have to do:

    persons = Person.objects.filter(mother=mother)
    persons.filter(Q(name=re.compile("bob|dylan", re.I)) | Q(email=re.compile("bob|dylan", re.I)))

Closing as its a can't fix.

[totalorder]

How can there be "no way to pragmatically convert that query"? This looks more like a bug. I couldn't avoid to throw together a test. The code below can run the code in stalnacke's second code paragraph. (but doesn't support the "contains" and "$ref") It creates a valid mongo-query solving stalnacke's problem using mongoengine syntax, why isn't this possible in mongoengine?

from django.utils import simplejson
import re
class Q():
    """
    Bogus query class that only handles 'and' and 'or' querys
    """
    def __init__(self, **kwargs):
        self.q = {}
        self.duplicate_key_counter = 0
        if kwargs:
            self.filter(**kwargs)

    def filter(self, *args, **kwargs):
        """
        Add an 'and' query accepting either a Q-object as only argument or
        kwargs specifying the filter. Basicly adding an item to the self.q dictionary
        """
        if len(args) == 1:
            kwargs = args[0].q
        for key, value in kwargs.items():
            if self.q.has_key(key): # If the key already exists, eg. second '$or' added, add __duplicate_key__<number>
                                    # to they key name to avoid duplicate dictionary keys and icrement <number>
                self.q["%s__duplicate_key__%s" % (key, self.duplicate_key_counter)] = value
                self.duplicate_key_counter += 1
            else:
                self.q[key] = value

    def __or__(self, other):
        """
        Create an 'or'-query
        """
        q = Q()
        ors = [self.q, other.q]
        q.filter(**{"$or" : ors})
        return q

    def to_mongo(self):
        """
        Return the mongo representation of the query. Turning into simpljson and removing "__duplicate_key_<number>"
        texts and citation marks from keys (warning ugly regex)
        """
        reg = re.compile('__duplicate_key__\d+')
        json = simplejson.dumps(self.q)
        text = reg.sub('', json)

        reg = re.compile('{"')
        text = reg.sub('{', text)

        reg = re.compile('":')
        text = reg.sub(':', text)

        reg = re.compile(', "')
        text = reg.sub(', ', text)
        return text

def create_or_query():
    """
    Create an 'or'-query for mongo, using the mongoengine syntax
    The finished query will be like below. This is valid mongo syntax
    {$or: [{name: "bob"},
           {email: "bob"}],
     $or: [{name: "dylan"},
           {email: "dylan"}],
     mother: "bobMother"
    }
    """

    base_query = Q()
    base_query.filter(mother = "bobMother")

    or_bob = Q()
    or_bob.filter(Q(name = "bob") | Q(email = "bob"))

    or_dylan = Q()
    or_dylan.filter(Q(name = "dylan") | Q(email = "dylan"))

    base_query.filter(or_bob)
    base_query.filter(or_dylan)
    return base_query.to_mongo()```

[rozza]

I'll happily be proven wrong and accept any patches :)

[rozza]

Syntax wise in the ORM arent these equivalent:

  persons = persons.filter(Q(name__icontains = "bob") | Q(email__icontains = "bob")) 
  persons = persons.filter(Q(name__icontains = "dylan") | Q(email__icontains = "dylan"))

With:

  persons = persons.filter(
          Q(name__icontains = "bob") | Q(email__icontains = "bob") | 
          Q(name__icontains = "dylan") | Q(email__icontains = "dylan")
  )

So we'd have to come up with a the correct syntax to define multiple $or's - probably just using Q()

  persons = persons.filter(
          Q(Q(name__icontains = "bob") | Q(email__icontains = "bob")) | 
          Q(Q(name__icontains = "dylan") | Q(email__icontains = "dylan"))
  )

[rozza]

requires: https://jira.mongodb.org/browse/SERVER-3192

[yamaneko1212]

I think this example looks more natural than above, like (A | B) & (C | D).

persons = persons.filter(
    (Q(name__icontains = "bob") | Q(email__icontains = "bob")) &
    (Q(name__icontains = "dylan") | Q(email__icontains = "dylan"))
)

And this seems not to need nesting-$or.

[andreif]

I get exception TypeError: __init__() takes exactly 1 argument (2 given) trying to run Q(Q(name__icontains = "bob") | Q(email__icontains = "bob")) using mongoengine 0.6.10

[andreif]

As a workaround, I do several requests and then check their intersection. Could you suggest a better way?

ids = []
for word in words:
    q = Q(name__icontains=word) | Q(email__icontains=word)
    ids.append(set(Person.objects.filter(q).values_list('id')))
persons = Person.objects.filter(id__in=set.intersection(*ids))

[yamaneko1212]

Hi

1st question Q(...) | Q(...) returns an instance of Q. The things you do doesn't make sense.

2nd question I don't have any good idea. You should ask this on the mailing list. It is good way to solve problems. http://groups.google.com/group/mongoengine-users