Hibernate MongoDB native query: sort unsupported, query modifiers deprecated


#1

Hi,

I hope this is the correct place to ask this question - if not please point me to somewhere else.

I’m using Java’s hibernate-ogm-mongodb 5.3.0.Final. I use HQL for most of my MongoDB queries and also use some native queries.

In chapter 10.7.2. (Native MongoDB queries) of Hibernate OGM Reference Guide the following warning is written:
“No cursor operations such as sort() are supported. Instead use the corresponding MongoDB query modifiers such as $orderby within the criteria parameter.”

But MongoDB reference specifies that query modifiers are “Deprecated in the mongo Shell since v3.2”.

Following is a query example:

db.AggregationPlaylist.find(
{
	{"$and": 
		[
		    {"aggregationDate": {$gte: ISODate("2018-05-30T00:00:00.000Z")}}, 
		    {"aggregationDate": {$lt: ISODate("2018-06-30T00:00:00.000Z")}},
	            {"aggregationType": 0}
		]
	},
	{"$sort": 
		{"$aggregationDate" : -1}
	}
)

How can I sort data by writing a native query in Java?

Kind regards,
Lidija


#2

Hi, an example of a query with order is here: https://github.com/hibernate/hibernate-ogm/blob/master/mongodb/src/test/java/org/hibernate/ogm/datastore/mongodb/test/query/nativequery/MongoDBEntityManagerNativeQueryTest.java#L205

In your case it will look like:

db.AggregationPlaylist.find( {
"$query" : 
	{"$and": 
		[
		    {"aggregationDate": {$gte: ISODate("2018-05-30T00:00:00.000Z")}}, 
		    {"aggregationDate": {$lt: ISODate("2018-06-30T00:00:00.000Z")}},
		    {"aggregationType": 0}
		]
	},
"$orderby":  { "$aggregationDate" : -1 }
})

But you could also write a JPQL query in this case:

String query = "FROM AggregationPlaylist a WHERE a.aggregationDate >= :start AND a.aggregationDate < :end AND a.aggregationType = 0 ORDER BY aggregationType DESC"

List<?> result = session
				.createQuery( query )
				.setParameter( "start", startDate )
				.setParameter( "end", endDate )
				.list();

I wrote this queries without testing them, let me know if you have some problems with them.

Thanks,
Davide


#3

Hi Davide,

thank you for your response. I appologize for my late answer.

I was able to test this today and I am very happy to say that running native query this way works perfectly.

My query was obviously flawed and I couldn’t test it in mongo shell since query modifiers are deprecated.

Thank you very much for your help.

Kind regards,
Lidija