query help: created_at != updated_at

[Jon Hancock]

My docs have created_at and updated_at timestamps. I need a query
that can give me docs where these two attribs are not equal. I could
also do this as updated_at > created_at but testing for not equal is
just as good for my needs.

Any ideas? thanks, Jon

[Eliot Horowitz]

There isn't a way to do that right now without $where.
With $where its easy "this.updated_at != this.created_at"

[Kyle Banker]

You could use a where clause:

db.things.find({$where: "this.created_at != this.updated_at" });

This is not the most efficient solution.  It's be better to have your application code send a flag {updated: true} so that you could index and query on that instead.

On Fri, Nov 13, 2009 at 2:24 PM, Jon Hancock <shell...@gmail.com> wrote:

[Jon Hancock]

since this is an admin function, I'll take the performance hit.
I'm not getting the results I need with your example.
Here's how I coded this in ruby:

collection.find({'$where' => 'this.created_at != this.updated_at'},
{:limit => 50}).sort(:updated_at, :descending)

Can someone help me translate the native mongo syntax into ruby
syntax?
thanks, Jon

[Kyle Banker]

The sort clause isn't quite right.

collection.find({'$where' => 'this.created_at != this.updated_at'},

{:limit => 50}).sort(:updated_at, Mongo::DESCENDING)

If you run the query without the sort clause and iterate over the cursor, what get's returned?

[Eliot Horowitz]

That looks correct at a quick glance...
You're not getting any results?

[Jon Hancock]

The sort was working before I added the where. The mongo ruby driver
source tells me :descending is a valid option in addition to
Mongo::DESCENDING,
Either way, I've removed the sort completely and the where query does
not work. I assumed it was due to my ruby syntax being wrong. But
maybe this type of query is simply broke?
thanks, Jon

[Kyle Banker]

Jon,

You'll have to include a call to getTime for this to work:

collection.find({'$where' => 'this.created_at.getTime() != this.updated_at.getTime()'},
{:limit => 50}).sort(:updated_at, Mongo::DESCENDING)

[Jon Hancock]

great, that works!!! seriously funky query though. I thought time
values were stored as just a number. Why do you have to call getTime
() just to check for != ?
thanks, Jon

On Nov 13, 3:14 pm, Kyle Banker <k...@10gen.com> wrote:
> Jon,
>
> You'll have to include a call to getTime for this to work:
>
> collection.find({'$where' => 'this.created_at.getTime() !=
> this.updated_at.getTime()'},
> {:limit => 50}).sort(:updated_at, Mongo::DESCENDING)
>

> On Fri, Nov 13, 2009 at 3:03 PM, Eliot Horowitz <eliothorow...@gmail.com>wrote:
>
>
>
>
>
> > That looks correct at a quick glance...
> > You're not getting any results?
>

> > On Fri, Nov 13, 2009 at 2:42 PM, Jon Hancock <shellsha...@gmail.com>

[Kyle Banker]

Because what's being compared is a javascript date object; getTime gets us the comparable part.