Using query

Backed by Morphia project, PlayMorphia provides powerful query interface to make the "information at your finger tips".

30 seconds tutorial

Suppose you have a model class defined as:

@Entity public class User extends Models {
    public String firstName;
    public String lastName;
    public String country;
    public String department;
    public int score;
}

A typical pattern of using PlayMorphia query interface:

List<User> users = User.q()
    .filter("country", "China")
    .filter("department", "IT").asList();

You could also achieve the same result with:

List<User> users = User.find("country,department", "China", "IT").asList();

Fields can also be separated by space:

List<User> users = User.find("country department", "China", "IT").asList();

or with the Play JPA style:

List<User> users = User.find("byCountryAndDepartment", "China", "IT").asList();

Build MorphiaQuery object

The simplest way to get an MorphiaQuery object out from a Model object is call the static q() method upon the model class. Note you must call the method on your model class not the inherited play.modules.morphia.Model:

MorphiaQuery q = User.q();

Specify query criteria

Once you have the MorphiaQuery instance you could filter the return result set using the filter interface. For example, to find all users with score between 80 and 90, you use the “>” and “<” operators:

List<User> users = user.q().filter("score > ", 80).filter("score < 90").asList();

Note the filter() call on a MorphiaQuery instance adds certain constraints to the current query instance and return "this" object. In other words, filter() is a mutable method and not thread-safe.

Here is a list of operators you can used in PlayMorphia query:

For more operator information please refer to to MongoDB Advanced Queries

¹ Using operator eq has the same effect as no operator:

List<User> users = User.q().filter("department eq", "IT").asList();

is exactly the same as

List<User> users = User.q().filter("department", "IT").asList();

² When you have the "in" or "nin" operators in the filter constraint, you can supply the following types of parameters:

1. array
2. Any object with class implements java.lang.Iterable
3. a single object. In this case in is the same as =, nin is the same as !=

³ To filter record by identifying whether a field exists you can issue the following statement:

List<User> users = User.q().filter("attributes.email exists", true).asList();

This is extremely useful when you want to filter a model which contains an embedded hashmap (dynamic attributes).

Search with regular expression

Application developer could implement regular expression based text search by using java.util.regex.Pattern class. The following query returns all users with firstName contains “john” or “John”

List<User> johns = User.q().filter("firstName", 
    Pattern.compile("john", Pattern.CASE_INSENSITIVE)).asList();

OR query

You can use the filter() interface and find() interface to apply contraints on mutiple fields using “AND” relationship. In order to implement a “OR” based query you need to use MorphiaQuery.or() and com.google.code.morphia.query.Criteria interface. The following query search for user who’s first name contains “john” or last name contains “john”:

MorphiaQuery q = User.q();
q.or(q.criteria("firstName").containsIgnoreCase("john"), 
    q.criteria("lastName").containsIgnoreCase("john"));
List<User> johns = q.asList();

Here q.criteria(<fieldname>).contains(<keyword>) is one of Morphia’s fluent interface. You should not pass Pattern object into the contains interface. Instead, pass String type object which will be automatically converted to Pattern by the underline implementation.

Query on embedded objects

To query on the embedded object you need to use the dot notation. Suppose you have the following models:

@Embedded public class Address {
    public String number;
    public String street;
    public String city;
    public String country;
}
// User model contains the address object
@Entity public class User {
    public String firstName;
    public String lastName;
    @Embedded Address address;
}

Now the following query find out all users lived in “FuXingJie, DuJiangYan, China”:

List<User> users = Users.find("address.street, address.city, address.country",
    "FuXingJie", "DuJiangYan", "China").asList();

Query on referenced objects

PlayMorphia does not support query on referenced objects direct as MongoDB does not support join query. However you could do it in 2 steps:

1. Find out the referenced object
2. Find out the objects who references that object

Suppose you have an Author model class defined:

@Entity public class Author extends Model {
    public String fullName;
    public String email;
}

And then you define a BlobEntry class which references the Author model:

@Entity public class BlogEntry extends Model {
    public String title;
    public Date publishDate;
    public String body;
    @Reference public Author author;
}

The following code find out all blog entries written by [email protected]:

Key<Author> green = Author.find("email", "[email protected]").getKey();
List<BlogEntry> entries = BlogEntry.find("author", green).asList();

And the following code find out all blog entries written by [email protected] and [email protected]:

String[] emails = {"[email protected]", "[email protected]"};
List<Key<Author>> greens = Author.find("email in", emails).asKeyList();
List<BlogEntry> entries = BlogEntry.q().filter("author in", greens).asList();

If you are using the manually reference approach to define reference model:

@Entity public class BlobEntry {
    public String title;
    public Date publishDate;
    public String body;
    public String authorId;
    public Author getAuthor() {
        return Author.findById(authorId);
    }
}

Then you need to query on authorId:

Object id = Author.find("email", "[email protected]).getKey().getId();
List<BlobEntry) entries = BlobEntry.find("authorId", id).asList();

Caching and avoid N+1

Query through reference relationship is not simple and straightforward as it is in relational database world. MongoDB’s solution is do a little bit of denormalization. Let’s say if you often query BlobEntry with author’s email, you could cache the email field in the BlobEntry model:

@Entity public class BlobEntry {
    public String title;
    public Date publishDate;
    public String body;
    public String authorId;
    public Author getAuthor() {
        return Author.findById(authorId);
    }
    // cache
    public String authorEmail;
}

now the query become easy:

List<BlobEntry> entries = BlobEntry.find("authorEmail", "[email protected]").asList();

People might argue that doing it cause redudant data. Just think how much you pay for the redundant data and how much you gain from simplified query. To update cache field is no doubt expensive. Again you need to make trade off between expensive update and fast query. In our case author’s email is seldom updated while queries happen hundreds of times per day. So cache email in the BlobEntry model is not a bad idea.

See MongoDB Data Modeling and Rails to get more information about this topic.

Fetch entities from MorphiaQuery

As shown above examples you can retrieve the query result in a list by invoking asList() method on the MorphiaQuery object:

List<User> = User.q().filter(...).asList();

Unlike JPA, you cannot use fetch to return a list of objects from MorphiaQuery object. The MorphiaQuery.fetch method returns an java.lang.Iterable type object. MorphiaQuery.asList() is the equivalence of JPAQuery.fetch.

Limit the number of records returned

The above query returns all instance filtered by the query. To limit the number of models returned, you can invoke the limit method on the query object:

List<User> = User.q().filter(...).limit(10).asList();

Skip the first N number of records

You can also instruct query to skip first N records:

List<User> = User.q().filter(...).offset(10).asList();

Combining limit and offset call you get an nice pagination support from MorphiaQuery

Sort records in the returned list

It’s easy to sort records in the returned list in a specific order:

List<User> users = User.q().filter(...).order('-_created').asList(); // sort by create timestamp in descending order
List<User> users = User.q().filter(...).order('_modified').asList(); // sort by modified timestamp in ascending order

Get only one record

You can also fetch one model instance from the query:

User user = User.q().get();

or

User user = User.get();

Alias of queries methods

PlayMorphia provides aliases for query support to cater to different coding style:

Attaining MorphiaQuery from your model class:

MorphaiQuery q = User.q();
q = User.createQuery();
q = User.all();
q = User.find();

Filtering query on your model class:

List<User> users = User.q().filter("country", "China").filter("department", "IT").asList();
users = User.q().findBy("country,department", "China", "IT").asList();
users = User.q().findBy("byCountryAndDepartment", "China", "IT").asList();
users = User.find("byCountryAndDepartment", "China", "IT").asList();
users = User.find("country,department", "China", "IT").asList();

See also

1. Morphia Query interface
2. MongoDB Querying
3. MongoDB Advanced Query
4. How do I perform SQL join equivalent in MongoDB
5. Understand morphia model
6. Atomic update operations
7. Aggregation