0

Through what to make requests of this kind in MongoDB (python, motor)?

There is a Collection of Documents. It is necessary to display them in one list in the following order:

  1. Urgent first.
  2. Further unfulfilled.
  3. Further leftovers sorted by date from recent to oldest.

I made working solution like this, but I'm looking how to optimize it to single query instead of triple and add pagination on MongoBD side ? :

now = datetime.utcnow()
reactivated = db.vtodo.find({'next_time': {'$lt': now},
                                     "project": project}).sort([("is_project", -1)])
uncompleted = db.vtodo.find({"project": project,
                                     'completed': None}).sort([("is_project", -1)])
completed_new_old = db.vtodo.find(
            {"project": project, 'completed': {'$ne': None},
             '$or': [{'next_time': {'$gt': now}}, {'next_time': None}]}).sort(
            [("is_project", -1), ("completed", -1), ])
reactivated = await reactivated.to_list(length=await db.vtodo.count_documents({}))
uncompleted = await uncompleted.to_list(length=await db.vtodo.count_documents({}))
completed_new_old = await completed_new_old.to_list(
            length=await db.vtodo.count_documents({}))
vtodos = reactivated + uncompleted + completed_new_old
page = 0
size = 12
return vtodos[page * size: page * size + size]

Documents examples:

[{'_id': ObjectId('6096988f8d23679184d619a3'), 'title': 'T5',
 'creator': ObjectId('6077f7207610f32a8d5e7e0c'),
 'completed': datetime.datetime(2021, 6, 5, 8, 30, 16, 631000),
 'next_time': datetime.datetime(2021, 6, 6, 8, 30, 16), 
'project': ObjectId('60aba9b03a686ce60be89538')}]

[{'_id': ObjectId('60abd6a487bdf6655a393388'), 'title': 'T6',
 'creator': ObjectId('6077f7207610f32a8d5e7e0c'), 'is_project': True, 
 'project': ObjectId('60aba9b03a686ce60be89538')}, 
{'_id': ObjectId('60be85006a5355235313575e'), 'title': 'T1', 
 'creator': ObjectId('6077f7207610f32a8d5e7e0c'), 
 'project': ObjectId('60aba9b03a686ce60be89538'), 
 'next_time': datetime.datetime(2021, 6, 8, 23, 44, 26)}, 
{'_id': ObjectId('60be85016a5355235313575f'), 'title': 'T2', 
 'creator': ObjectId('6077f7207610f32a8d5e7e0c'), 
 'project': ObjectId('60aba9b03a686ce60be89538')}]

[{'_id': ObjectId('60be85056a53552353135761'), 'title': 'T4',
 'creator': ObjectId('6077f7207610f32a8d5e7e0c'),
 'project': ObjectId('60aba9b03a686ce60be89538'),
 'completed': datetime.datetime(2021, 6, 7, 20, 44, 10, 882000), 'next_time': None},
 {'_id': ObjectId('60be85036a53552353135760'), 'title': 'T3',
 'creator': ObjectId('6077f7207610f32a8d5e7e0c'),
 'project': ObjectId('60aba9b03a686ce60be89538'),
 'completed': datetime.datetime(2021, 6, 7, 20, 44, 3, 168000), 'next_time': None}]

Expected result:

T5,
T6,
T1,
T2,
T4,
T3

Update: Tried with $or, but results is unordered at all:

db.vtodo.find({'$or': [
            {"creator": oid, 'next_time': {'$lt': now}, "project": None},
            {"creator": oid, "project": None, 'completed': None},
            {"creator": oid, "project": None}, ]}).sort(
                [("is_project", -1)])

I tried aggregation also, but seems it doesn't help

Se Gu
  • 33
  • 5
  • 1
    I did not work with MongoDB, but it looks like. db.vtodo.find() returns you dictionary and then you try to sort it. – Artiom Kozyrev Jun 05 '21 at 12:45
  • 1
    i am not sure about `motor-asyncio` tag. you can not put sort condition in mongodb sort() method, you have to use aggregation query, please post some example document. – turivishal Jun 05 '21 at 13:16
  • 1
    can you check this query [playground](https://mongoplayground.net/p/Y7My5fH0H-n) – turivishal Jun 05 '21 at 14:17
  • there is match stage do you want to select document for one creator right? if not then remove $match stage. – turivishal Jun 05 '21 at 15:11
  • @turivishal thank you! I updated question. Now I added working code. Just looking how to optimize it in single query instead of multiple – Se Gu Jun 07 '21 at 21:08
  • 1
    use [aggregate()](https://docs.mongodb.com/manual/reference/method/db.collection.aggregate/) method with [$facet](https://docs.mongodb.com/manual/reference/operator/aggregation/facet/) stage. – turivishal Jun 08 '21 at 04:44
  • Thank you @turivishal I created [mongoplayground](https://mongoplayground.net/p/l4XNTEbTqCS) but didn't understand how correctly make it with $facet – Se Gu Jun 08 '21 at 06:43
  • 1
    see this [playground](https://mongoplayground.net/p/2E9i4bOtksD) – turivishal Jun 08 '21 at 07:26
  • @turivishal I fixed it "None" to null [mongoplayground](https://mongoplayground.net/p/Z8yq-F4KhW_). But order isn't correct (4,3,5,6,1,2 - seems inside groups it's correct, but third group become first) and need some way to drop names of stages. Also I tried [here playground](https://mongoplayground.net/p/ZNZkR8LXnW_) to clean names of stages, but seems facet need it and without facet it's impossible to work like with 3 arrays – Se Gu Jun 08 '21 at 08:40

0 Answers0