0

First of all I am sorry if this is a basic question, I am new to queries in MongoDB. Well, what I need is to find the latest registers for a worker in my WorkerLocationContext document and the latest register for each sensor in my HeatMeasureContext document, then join it by their location and then apply some filters. Here are my Schemas:

HeatMeasureContext:

const heatMeasureContextSchema  = new mongoose.Schema({
    sensor: { type: Schema.Types.ObjectId, ref: 'MeasureSensor', required: true },
    humid: { type: Schema.Types.Number, required: true },
    globe: { type: Schema.Types.Number, required: true },
    mercury: { type: Schema.Types.Number, required: true },
    internal: { type: Schema.Types.Number, required: true },
    external: { type: Schema.Types.Number, required: true }
}, { timestamps: true })

MeasureSensor:

const measureSensorSchema = new mongoose.Schema({
    name: { type: String, required: true },
    description: { type: String, required: false },
    type: { type: String, required: false, uppercase: true,
        enumValues: ['MEASURE'], default: 'MEASURE' },
    location: { type: Schema.Types.ObjectId, ref: 'Location' },
    sensorType: { type: String, required: false, uppercase: true,
        enumValues: ['WORKER_ATTACHED', 'ENVIRONMENT'], default: 'ENVIRONMENT' },
    measurerType: { type: String, required: false, uppercase: true,
        enumValues: ['HEAT', 'RUID'] },
    placementType: { type: String, required: false, uppercase: true,
        enumValues: ['INTERNAL', 'EXTERNAL'], default: 'INTERNAL' }
})

WorkerLocationContext:

const workerLocationContextSchema  = new mongoose.Schema({
    sensor: { type: Schema.Types.ObjectId, ref: 'LocationSensor', required: true },
    worker: { type: Schema.Types.ObjectId, ref: 'Worker', required: true }
}, { timestamps: true })

Location

const locationSchema = new mongoose.Schema({
    name: { type: String, required: true },
    description: { type: String, required: false },
    type: { type: String, required: false, uppercase: true,
    enumValues: ['REST', 'ROOM', 'COURTYARD'], default: 'ROOM' }
})

Worker

const workerSchema = new mongoose.Schema({
    name: { type: String, required: true },
    workGroup: { type: Schema.Types.ObjectId, ref: 'WorkGroup', required: false }
})

I have built my query like this:

WorkerLocationContext.aggregate([ 
    {
        "$lookup": {
            "from": "HeatMeasureContext",
            "localField": "sensor.location._id",
            "foreignField": "sensor.location._id",
            "as": "HMContext"
        }
    },
    {
        "$match": {
            "$and": [
                { "$or": [
                    { "$and": [ 
                        { 
                            "HMContext.sensor.placementType": { "$eq": "INTERNAL" }}, 
                            {"HMContext.internal": { "$gte": limit} 
                        },
                        { 
                            "HMContext.sensor.placementType": { "$eq": "EXTERNAL" }}, 
                            {"HMContext.external": { "$gte": limit} 
                        },
                    ]},
                ]},
                { "WorkerLocationContext.worker.location.type": { "$ne": "REST" } }
            ]
        }
    },
    {
        "$group": {
            "_id": "null",
            "workers": {
              "$count": {}
            },
            "hmDatetime": {
                "$max": "$HMContext.createdAt"
            },
            "wlDatetime": {
                "$max": "$WorkerLocationContext.createdAt"
            }
        }
    }
]);

Basically, my goal with it is to count how many workers fit in that condition according to their current location, thus the latest registers in the context tables. I have tried some simulations in the mongoplayground, but nothing succeeded. Is it possible to be done in MongoDB? Can you help me?

Thanks in advance!

5
  • Why are you trying to use MongoDB, which is a document store, for what seems to be a perfectly relational schema?
    – mustaccio
    Nov 2, 2021 at 18:50
  • Hi @mustaccio, it is because I am using a MEAN Stack for my application, just because of it... you mean that it is not possible with mongo?
    – liegolas
    Nov 2, 2021 at 19:39
  • I don't know Mongo well enough to tell you what's possible, it just doesn't look like the right tool for the job.
    – mustaccio
    Nov 2, 2021 at 20:03
  • @mustaccio thanks for the input, unfortunately it would give me a lot of work to change the database now :(
    – liegolas
    Nov 2, 2021 at 20:51
  • Might be a lot of work to do this (and every task like this) when you could just write a 10 line query in SQL and be done with it.
    – user212533
    Nov 5, 2021 at 16:42

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.