I am trying to create a custom Query annotation in a MongoRepository. Everything is fine except the date comparison. I need to find all items that were created on a specific day, so I give 2 date objects with the good date and the times fixed to 00:00 and 23:59 to gte and lte operators. This is the request sent according to the debug trace:
2022-08-27 01:52:25.817 DEBUG 440959 --- [or-http-epoll-4] o.s.data.mongodb.core.MongoTemplate : find using query: { "$and" : [{ "$or" : [{ "$where" : "5 == null"}, { "rating" : 5}]}, { "$or" : [{ "$where" : "null == null"}, { "productId" : null}]}, { "$or" : [{ "$where" : "APPROVEDD == null"}, { "moderationStatus" : "APPROVEDD"}]}, { "$or" : [{ "$where" : "true == false"}, { "clientResponses" : { "$exists" : true, "$not" : { "$size" : 0}}}]}, { "$or" : [{ "$where" : "2022-01-29T23:00:00Z == null || 2022-01-30T22:59:59Z == null"}, { "submissionTime" : { "$gte" : { "$date" : "2022-01-29T23:00:00Z"}, "$lte" : { "$date" : "2022-01-30T22:59:59Z"}}}]}]} fields: Document{{}} for class: class com.company.productreviews.cdpresponseportalapi.model.Review in collection: reviews
Dates seem to be in the good format, but mongo gives me an error (only when I use the date filter of my request):
Query failed with error code 139 and error message 'SyntaxError: identifier starts immediately after numeric literal' on server mongodb-databaserevqa-shard-00-02.xxocp.mongodb.net:27017; nested exception is com.mongodb.MongoQueryException: Query failed with error code 139 and error message 'SyntaxError: identifier starts immediately after numeric literal' on server mongodb-databaserevqa-shard-00-02.xxocp.mongodb.net:27017
My repo:
public interface ReviewRepository extends MongoRepository<Review, String> {
@Query("{ $and : ["
"{ $or : [ { $where: '?0 == null' }, { 'rating': ?0 } ] },"
"{ $or : [ { $where: '?1 == null' }, { 'productId': ?1 } ] },"
"{ $or : [ { $where: '?2 == null' }, { 'moderationStatus': ?2 } ] },"
"{ $or : [ { $where: '?3 == false' }, { 'clientResponses': { $exists: true, $not: { $size: 0 } } } ] },"
"{ $or : [ { $where: '?4 == null || ?5 == null' }, { 'submissionTime': { $gte: ?4, $lte: ?5 } } ] },"
"]}")
List<Review> findAll(Integer rating, Integer productId, String moderationStatus, boolean withAnswersOnly, Date submissionDateStartRange, Date submissionDateEndRange);
Review findOneByReviewId(int reviewId);
}
CodePudding user response:
It seems to be a syntax error when you date value are not null. Wrapping your statement in quote should do the trick. So in your last @Query statement do the following:
"{ $or : [ { $where: '\"?4\" == \"null\" || \"?5\" == \"null\"' }, { 'submissionTime': { $gte: ?4, $lte: ?5 } } ] },"