goodybag/mongo-sql — pull request #3 by jrf0110 (2013-05-05 18:48:03+00:00)
Title: Add Custom Syntax

The MongoSql API isn't really prepared for arbitrary SQL. You can pretty much get by with putting your custom logic as long string of a query value, but that defeats the purpose of building up a document with value. It also avoids using parameterized queries.

Take for example querying users where some date is greater than the date 5 hours ago.

```sql
select * from users where last_visited > ( now() - interval '5' hour );
```

Currently in MongoSql, you'd have to put that value directly in some string:

```javascript
db.users.find({
'last_visited':
{ $gt: { "now() - interval '5' hour" } }
});
```

The query would still get parameterized, but it would parameterize the whole right-hand side of the ```$gt``` statement.

To get around the parameterization problem, we could add a ```$custom``` operator as follows:

```javascript
db.users.find({
'meta=>last_visited':
{ $gt: { $custom: ["now() - interval $1 hour", 5] } }
});
```

That's pretty good for one-offs. But if you're performing that custom all the time, it would make sense to build your own custom syntax:

```javascript
db.registerOperator('$hours_ago', function(column, value, values, collection){
return "now() - interval " + value + " hour";
});

// Usage
db.users.find({
'last_visited':
{ $gt: { $hours_ago: 5 } }
});
```