We have two collections: "houses" and "events". Each document in the houses collection contains an "events" field with an array of id's that refer to events (a "one-to-many" relationship). The array can be empty.
"House" schema:
const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const HouseSchema = new Schema({
name: String,
district: String,
locality: String,
date: {
type: Date,
default: Date.now
},
events: [{
type: mongoose.Schema.Types.ObjectId,
ref: 'Event'
}]
});
module.exports = mongoose.model('House', HouseSchema);
"Event" schema:
const mongoose = require('mongoose');
const Schema = mongoose.Schema;
const EventSchema = new Schema({
event: String,
details: String,
date: {
type: Date,
default: Date.now
},
house: {
type: mongoose.Schema.Types.ObjectId,
ref: 'House'
}
});
module.exports = mongoose.model('Event', EventSchema);
I need to query both "houses" and "events" and find those "houses" with referred "events" that meet all the criteria. I will give several options for queries.
Query with parameters only from "houses". Let's say there are two docs in the "houses" collection that satisfy the condition {"locality": "Facebook"}. The result should be like this:
[
{
"_id": "601bae8e26ed00591d571dbe",
"name": "Facebook testing department",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbf",
"event": "Testing",
"details": "Testing software for production",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
{
"_id": "601bae8e26ed00591d571dbc",
"name": "Facebook office",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbd",
"event": "Conference",
"details": "Developers conference about 12345",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
];
Query with parameters only for "events". Let's say there are two records in the "events" collection that satisfy the condition {"event": "Conference"}. The result is:
[
{
"_id": "601bae8e26ed00591d571dbc",
"name": "Facebook office",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbd",
"event": "Conference",
"details": "Developers conference about 12345",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
{
"_id": "601bae8e26ed00591d571dba",
"name": "Oxford",
"district": "London",
"locality": "Oxford",
"events": [
{
"_id": "601bae8e26ed00591d571dbb",
"event": "Conference",
"details": "About something",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
];
Query with parameters for both "houses" and "events". If the criteria of our query is {"locality": "Facebook", "event": "Conference"}, then the result should be like this:
[
{
"_id": "601bae8e26ed00591d571dbc",
"name": "Facebook office",
"district": "Los-Angeles",
"locality": "Facebook",
"events": [
{
"_id": "601bae8e26ed00591d571dbd",
"event": "Conference",
"details": "Developers conference about 12345",
"date": "2020-07-31T21:00:00.000Z"
}
]
},
];
I have faced similar situations where it is necessary to make queries more complex than usual. But here we need to make a query - depending on the parameters - for one or two collections, and as a result, we should get "houses" with nested "events" that correspond the query parameters. I don't know MongoDB aggregation that well, would appreciate any help.
let locality = req.body.locality;
let event = req.body.event;
HousesModel to your reql model name)let p = HousesModel.aggregate();
localityif (locality && locality.trim()) p.match({ locality: locality });
$lookup with pipeline,events array into letp.lookup({
from: "events", // replace your actual collection name if this is wrong
let: { events: "$events" },
pipeline: [
{
$match: {
$and: [
{ $expr: { $in: ["$_id", "$$events"] } },
(event && event.trim() ? { event: event } : {})
]
}
}
],
as: "events"
});
p.match({ events: { $ne: [] } });
let result = await p.exec();
Combine above code in sequence and test.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments