I have data saved in db as follow:
collection name: movies:
[
{id:1, name:"abch", start:"12:00 pm", end:"03:00 pm"},
{id:2, name:"Annabelle", start:"08:30 am", end:"10:00 am"},
{id:3, name:"Spider Man homecoming", start:"11:30 am", end:"03:00 pm"},
{id:4, name:"Grudge", start:"10:00 pm", end"00:00 am"}
]
I use filter on the movie's start time and end time, like this -- starting:"12:00 pm" ending:"00:00 am"
req.query = { starting:"12:00 pm", ending:"00:00 am" }
I want all the list of movies where the start
and end
timing is between req.query = { starting:"12:00 pm", ending:"00:00 am" }
Output data should be:
[
{id:1, name:"abch", start:"12:00 pm", end:"03:00 pm"},
{id:3, name:"Spider Man homecoming", start:"11:30 am", end:"03:00 pm"},
{id:4, name:"Grudge", start:"10:00 pm", end"00:00 am"}
]
The mongodb query
I used is:
movies.aggregate([
$or: [
{"start":req.query.starting},{"end":req.query.ending}
]
])
But this does not work. I dont know how to do this and dont know where I am going wrong.
I dont want to use $dateFromSting
or new Date()
or ISODate()
Want to get output with the above and want it to get from mongodb queries. Need Help Please!!!
CodePudding user response:
I would not recommand such a data structure, but one option is:
db.collection.aggregate([
{$set: {
startHour: {$add: [
{$mod: [{$toInt: {$substr: ["$start", 0, 2]}}, 12]},
{$divide: [{$toInt: {$substr: ["$start", 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: ["$start", 6, 2]}, "am"]}, 0, 12]}
]},
startingHour: {$add: [
{$mod: [{$toInt: {$substr: [req.query.starting, 0, 2]}}, 12]},
{$divide: [{$toInt: {$substr: [req.query.starting, 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: [req.query.starting, 6, 2]}, "am"]}, 0, 12]}
]},
endingHour: {$add: [
{$toInt: {$substr: [req.query.ending, 0, 2]}},
{$divide: [{$toInt: {$substr: [req.query.ending, 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: [req.query.ending, 6, 2]}, "am"]}, 0, 12]}
]},
endHour: {$add: [
{$toInt: {$substr: ["$end", 0, 2]}},
{$divide: [{$toInt: {$substr: ["$end", 3, 2]}}, 60]},
{$cond: [{$eq: [{$substr: ["$end", 6, 2]}, "am"]}, 0, 12]}
]}
},
{$set: {
endHour: {$cond: [{$eq: ["$endHour", 0]}, 24, "$endHour"]},
endingHour: {$cond: [{$eq: ["$endingHour", 0]}, 24, "$endingHour"]}
}
},
{$match: {
$expr: {
$and: [{$gte: ["$startHour", "$startingHour"]},
{$lte: ["$endHour", "$endingHour"]}]
}
}
},
{$unset: ["stasrtHour", "endHour", "startingHour", "endingHour"]}
])
See how it works on the playground example
CodePudding user response:
Your condition is not 100% clear. Based on my comment and also according to @nimrod_serok recommendation a solution would be this one:
db.collection.insertMany([
{ id: 1, name: "abch", start: { h: 12, m: 0 }, end: { h: 15, m: 0 } },
{ id: 2, name: "Annabelle", start: { h: 8, m: 30 }, end: { h: 10, m: 0 } },
{ id: 3, name: "Spider Man homecoming", start: { h: 11, m: 30 }, end: { h: 15, m: 00 } },
{ id: 4, name: "Grudge", start: { h: 22, m: 0 }, end: { h: 24, m: 00 } }
])
starting = { h: 12, m: 0 }
ending = { h: 24, m: 0 }
db.collection.aggregate([
{
$set: {
startTime: { $dateFromParts: { year: 1900, hour: "$start.h", minute: "$start.m" } },
endTime: { $dateFromParts: { year: 1900, hour: "$end.h", minute: "$end.m" } }
}
},
{
$match: {
$expr: {
$and: [
{ $gte: ["$startTime", { $dateFromParts: { year: 1900, hour: starting.h, minute: starting.m } }] },
{ $lte: ["$startTime", { $dateFromParts: { year: 1900, hour: ending.h, minute: ending.m } }] },
{ $gte: ["$endTime", { $dateFromParts: { year: 1900, hour: starting.h, minute: starting.m } }] },
{ $lte: ["$endTime", { $dateFromParts: { year: 1900, hour: ending.h, minute: ending.m } }] }
]
}
}
}
])
Which is much smaller compared to @nimrod_serok proposal. Some of these conditions are redundant, i.e. you can even reduce them.