Complex AND-OR query in Morphia

Question

I've been trying to combine the and() and or() methods of the Query interface to create a set of conditions where there are 2 lists of criteria, and at least one from each must be satisfied.

I read this discussion and have been trying to use the Query.and() to combine my two $or clauses.

Essentially, I'm trying to say:

Criteria[] arrayA;
Criteria[] arrayB;

// Programatically populate both arrays

Query q = dao.createQuery().and(
    q.or(arrayA),
    q.or(arrayB)
);

I'm using arrays of criteria because I have to loop through several different inputs to generate the particular criteria I need, and this approach works when I'm just using a single $or, but I can't get Morphia to generate the query I expect when I try and include both $or clauses in the $and as I explained above. I find that there's no $and query and the second $or has overwritten the first, just as if I had simply called or() twice.

E.g I expect a query to be generated like this:

{
    "$and": {
    "0": {
        "$or": {
            "0": //Some criteria,
            "1": //Some criteria,
            "2": //Some criteria,
        }
    },
    "1": {
        "$or": {
            "0": //Some other criteria,
            "1": //Some other criteria,
            "2": //Some other criteria,
        }
    }
}

However, I just get a query like this:

{
    "$or": {
        "0": //Some other criteria,
        "1": //Some other criteria,
        "2": //Some other criteria,
    }
}

I can't see much documentation, but looking at the test case, this seems to be the right way to go about this. Can anyone help shed any light on why this isn't working as I expect?

(This question was cross-posted to the Morphia mailing list, as I'm not sure which place would get the best response)

Edit 0:

Update: Revisiting this, I have checked out the Morphia test code and every thing runs fine, I've been unable to reproduce my issue in the test code.

Therefore, I created a new project to try and get a working example of the query I want. However I encountered the same issue, even with a barebones test project.

The project is mavenised, and the POM is:

<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
<modelVersion>4.0.0</modelVersion>
<groupId>test</groupId>
<artifactId>test</artifactId>
<version>0.0.1-SNAPSHOT</version>
<name>Test</name>


<dependencies>
    <dependency>
        <groupId>com.google.code.morphia</groupId>
        <artifactId>morphia</artifactId>
        <version>0.99</version>
    </dependency>
</dependencies>
<dependencyManagement>
    <dependencies>
        <!-- Force the use of the latest java mongoDB driver -->
        <dependency>
            <groupId>org.mongodb</groupId>
            <artifactId>mongo-java-driver</artifactId>
            <version>2.7.3</version>
        </dependency>
    </dependencies>
</dependencyManagement>

</project>

I have a TestEntity class:

import java.util.Map;

import com.google.code.morphia.annotations.Entity;

@Entity
public class TestEntity {
    Map<String, Integer> map;
}

And finally my test class:

import java.net.UnknownHostException;
import java.util.HashMap;
import java.util.Map;

import com.google.code.morphia.Datastore;
import com.google.code.morphia.Morphia;
import com.google.code.morphia.query.Query;
import com.google.code.morphia.query.QueryImpl;
import com.mongodb.Mongo;
import com.mongodb.MongoException;

public class Test {

    static Mongo mongo;
    static Morphia m;
    static Datastore ds;

    static {
        mongo = null;
        try {
            mongo = new Mongo();
        } catch (UnknownHostException e) {
            e.printStackTrace();
        } catch (MongoException e) {
            e.printStackTrace();
        }
        m = new Morphia();
        ds = m.createDatastore(mongo, "test");
    }

    public static void main(String[] args) {
        populate();
        query();
    }

    public static void query() {
        Query<TestEntity> q = ds.createQuery(TestEntity.class);

        q.and(q.or(q.criteria("map.field1").exists()),
                q.or(q.criteria("map.field2").exists()));

        Iterable<TestEntity> i = q.fetch();
        for (TestEntity e : i) {
            System.out.println("Result= " + e.map);
        }

        QueryImpl<TestEntity> qi = (QueryImpl<TestEntity>) q;
        System.out    
                .println("Query= " +         qi.prepareCursor().getQuery().toString());
    }

    public static void populate() {
        TestEntity e = new TestEntity();
        Map<String, Integer> map = new HashMap<String, Integer>();
        map.put("field1", 1);
        map.put("field2", 2);
        e.map = map;

        ds.save(e);
    }
}

For me, the above code doesn't produce the correct $and query, but I can't see why

Solution 1

Despite Morphia 0.99 including the Query.and(Criteria ...) method, it doesn't generate the correct query.

Issue 338, which addresses support for explicit $and clauses in queries is targeted at Morphia 0.99.1, which is currently only available as a SNAPSHOT version via Maven.

However, using 0.99.1-SNAPSHOT resolved the issue for us.

Solution 2

Just guessing (don't have time to test), but should it be:

Query q = dao.createQuery().and(
  q.or(q.criteria(arrayA)),
  q.or(q.criteria(arrayB))
);

Update You're right, Query.criteria was not right--it was what was used in the simple test, so I thought you had missed something. This seems to work for me (breaking it into two statements):

Query q = dao.createQuery();
q.and(
  q.or(arrayA),
  q.or(arrayB)
);

Update 2 More complete test code:

Criteria[] arrayA = {dao.createQuery().criteria("test").equal(1), dao.createQuery().criteria("test").equal(3)};
Criteria[] arrayB = {dao.createQuery().criteria("test").equal(2), dao.createQuery().criteria("test").equal(4)};;
Query q = dao.createQuery();
q.and(
  q.or(arrayA),
  q.or(arrayB)
);
System.out.println(q.toString());

gives:

{ "$and" : [ { "$or" : [ { "test" : 1} , { "test" : 3}]} , { "$or" : [ { "test" : 2} , { "test" : 4}]}]}

Comments

[chrisbunney]

Unfortunately not, as Query.criteria() only takes a string (which is the field name), and both arrays are lists of Criteria objects. There's no alternative methods to use, and since passing the arrays worked for constructing a single or clause I may need to check out the Morphia code and write a test case for this scenario

[chrisbunney]

Thanks for the update, that's interesting, so it works for you like that? My actual query is more complicated than the simply example I gave to illustrate my intention, but I assumed that the principle was sound. Perhaps the problem is elsewhere in the query code and this is just the symptom...

[Eve Freeman]

In fact, your code doesn't compile for me, with the chaining off of the dao.createQuery(), since q hasn't been initialized.

[Eve Freeman]

Btw, I'm running 99.1-snapshot of Morphia.

[chrisbunney]

Ah no, it wouldn't compile, it was just something I typed to illustrate what I was trying to achieve. We're also running the most recent version of Morphia, but have forced Maven to use the most recent Java Driver, rather than the one defined in the Morphia POM. I'll have to take a look to see if that might be a source of issues. Based on your responses, it seems less and less likely that it's a generic issue with Morphia, which is what I was afraid of ;)

[Eve Freeman]

I'm actually using my own copy of the Java driver, which is pretty close to the latest official driver on github (it only adds new classes, doesn't change any of the existing stuff). I don't think a new Java driver would be the problem.

[chrisbunney]

Turns out we were running the latest STABLE release of Morphia, which includes the and() method, but it doesn't produce the correct query. Updating to the latest 99.1-snapshot fixed the issue for us

[chrisbunney]

Hi @RussBateman, the code didn't change, it seems there is an issue in version 0.99 that is resolved in 0.99.1-SNAPSHOT, so we simply changed the version of the library we're using (not ideal, but the best option we had)

[Gelin Luo]

Even with 0.99.1-SNAPSHOT I still have the problem with Query.and(). It is okay as long as the Criteria is simple fields, but when you have a $or criterial included in $and, it will fail

[chrisbunney]

We've not encountered any issues relations to Query.and() since updating the version. Perhaps you've found a very specific case where there is a bug?