Mongoid Moped query not returning the same as the MongoDB shell

Question

Creating a query using Mongoid 3.1.5 and MongoDB 2.4.9 I get different results from the Mongoid/Moped query that I do from the equivalent MongoDB query.

Update added Mongoid query The query in its long for is:

return Video.order_by(release_date: :desc, avg_rating: :desc, title: :asc)
.where( :viewable => true, :release_date.lte => start_at_date, :release_date.gte =>
start_date, :categories.in => genre_filters).any_of({:avg_rating.gt => 1},
{:avg_rating => nil}).skip(skip*POSTERS_PER_ROW).limit(limit*POSTERS_PER_ROW)
.only(:_id, :poster_large_thumb, :title, :similar_as_string, :release_date, :avg_rating)

This query works correctly for everything I've thrown at it except when genre_filters causes only one object to be found afaik.

I get the following console output describing the Moped query:

MOPED: 127.0.0.1:27017 QUERY        database=guide_development collection=videos 
selector={"$query"=>{"viewable"=>true, "release_date"=>{"$lte"=>2014-03-27 00:00:00 UTC,
"$gte"=>1850-01-01 00:00:00 UTC}, "categories"=>{"$in"=>["Netflix"]}, 
"$or"=>[{"avg_rating"=>{"$gt"=>1}}, {"avg_rating"=>nil}]}, "$orderby"=>
{"release_date"=>-1, "avg_rating"=>-1, "title"=>1}} 
flags=[:slave_ok] limit=60 skip=0 batch_size=nil fields={"_id"=>1,
"poster_large_thumb"=>1, "title"=>1, "similar_as_string"=>1, "release_date"=>1,
"avg_rating"=>1} (198.0939ms)

This returns no results--not what I expected.

After some time I came up with the following MongoDB shell query, which should be equivalent:

var start = new Date(2014, 3, 27);
var end - new Date(1850,1,1);

db.videos.find({
    viewable: true, release_date: {
    $lte: start, $gte: end
    }, 
    categories:{
        $in: ["Netflix"]
    }, $or: [ {avg_rating: {$gt: 1}}, {avg_rating: {$exists: false}}],
},{
    _id: 1,
    poster_large_thumb: 1,
    title: 1,
    similar_as_string: 1,
    release_date: 1,
    average_rating: 1
}).sort({release_date: -1, avg_rating: -1, title:1}).skip(0).limit(60).count()

Gives one result in the MongoDB shell, and this is the one object it should find.

Any ideas? Does a result of one object cause a problem in Mongoid?

Answer 1

I think there's a difference between:

avg_rating: {$exists: false}

which means return results where the field doesn't exist

and

{"avg_rating"=>nil}

which limits to results where the field exists but is set to nil.

See the docs here: http://docs.mongodb.org/manual/reference/operator/query/exists/

When is true, $exists matches the documents that contain the field, including documents where the field value is null. If is false, the query returns only the documents that do not contain the field.

Answer 2

Beside the difference between existence and is null, you could have a time zone problem.

Rails and Mongoid will convert your timestamps to UTC for you, you can see this in the Moped log:

"release_date"=>{"$lte"=>2014-03-27 00:00:00 UTC, "$gte"=>1850-01-01 00:00:00 UTC}

Mongoid is using 2014-03-27T00:00:00Z and 1850-01-01T00:00:00Z as your timestamps so you have 00:00:00 (UTC) as the time components. The timestamps inside MongoDB will also be in UTC.

However, the MongoDB shell will use whatever time zone is currently in effect when it converts 2014-03-27 to a timestamp:

> new Date(2014, 3, 27)
ISODate("2014-04-27T07:00:00Z")

Note the 07:00:00Z. Those seven hours come from my PDT timezone (GMT-0700). You should be using the ISODate convenience function in the MongoDB shell thusly:

> ISODate('2014-03-27')
ISODate("2014-03-27T00:00:00Z")
> ISODate('1850-01-01')
ISODate("1850-01-01T00:00:00Z")

to make sure you get the right time zone.

Also, since you're only looking at a count, you can leave out the second argument to find in the MongoDB shell to simplify things. Or use { _id: true } and drop the count() to cut down on the clutter.

Fix the problems that tirdadc and I have pointed out and see if you get the same results in both cases. If you don't, then start building both queries piece by piece until they produce different results and you'll know where the problem lies. Don't forget to include the sorting options from the start and compare the returned documents IDs rather than just the counts.