I want implement a "bump" feature for topics. Once a topic is bumped, it will have a new "bump_date" field. I want to sort it so that when there is a "bump_date" field, it will be sorted as if it was the "created" field. Here's an example of my db.topics:
{
"text" : "test 1",
"created" : "Sun Nov 20 2011 02:03:28 GMT-0800 (PST)"
},
{
"text" : "test 2",
"created" : "Sun Nov 18 2011 02:03:28 GMT-0800 (PST)"
},
{
"text" : "test 3",
"created" : "Sun Nov 17 2011 02:03:28 GMT-0800 (PST)",
"bump_date: : "Sun Nov 19 2011 02:03:28 GMT-0800 (PST)"
}
I want the sort to return in the order of "test 1", "test 3", "test 2"
MongoDB can perform sort operations on a single-field index in ascending or descending order. In compound indexes, the sort order determines whether the index can be sorted.
If MongoDB cannot obtain the sort order via an index scan, then MongoDB uses a top-k sort algorithm. This algorithm buffers the first k results (or last, depending on the sort order) seen so far by the underlying index or collection access.
The default internal sort order (or natural order) is an undefined implementation detail.
If the sort keys correspond to the index keys or an index prefix, MongoDB can use the index to sort the query results. A prefix of a compound index is a subset that consists of one or more keys at the start of the index key pattern. The following query and sort operations use the index prefixes to sort the results.
Sorting in MongoDB is done like so:
db.collection.find({ ... spec ... }).sort({ key: 1 })
where 1
is ascending and -1
is descending.
In your specific example: db.topics.find().sort({ bump_date: 1 })
, although it might be better to call it something like "updated_at".
You'll also definitely want to put an index on your "bump_date" field.
As Brian Hicks suggested, creating an additional updated_at field is the way to go. This way, when a document is created you can have created_at and updated_at initially be the same.
{
"created_at": xxx,
"updated_at": xxx
}
If you then "bump" the updated_at field by setting it to the current time when there is a a bump event you can sort on the updated_at field to achieve the ordering you desire.
Also:
db.collection.find( { $query: {}, $orderby: { column : -1 } } )
where 1 is ascending and -1 is descending.
Currently it is not possible in mongodb to do a sort based on user defined criteria over multiple columns.eg. here the function would have been to return bump_date
if it is set,else return created
Either you will have to use a server-side or client-side code as mentioned here :
Mongo complex sorting?
or if you want to stay with basic quering and sorting, you shall :
create a key bump_date
equivalent to created
whenever a new record is created. This will not be a data overhead, as you can expect every topic of yours to be bumped once in a while in future,hence bump_date
field will eventually be added. So add it from the start itself.
Whenever the article is bumped,update the field bump_date
.
Your example documents will look like this with this change :
{
"text" : "test 1",
"created" : "Sun Nov 20 2011 02:03:28 GMT-0800 (PST)",
"bump_date" : "Sun Nov 20 2011 02:03:28 GMT-0800 (PST)"
},
{
"text" : "test 2",
"created" : "Sun Nov 18 2011 02:03:28 GMT-0800 (PST)",
"bump_date" : "Sun Nov 18 2011 02:03:28 GMT-0800 (PST)"
},
{
"text" : "test 3",
"created" : "Sun Nov 17 2011 02:03:28 GMT-0800 (PST)",
"bump_date: : "Sun Nov 19 2011 02:03:28 GMT-0800 (PST)"
}
You shall ensureIndex
on bump_date field. Now you can query the required data easily.
db.topics.find().sort({ bump_date: 1 })
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With