This post is a continuation of the previous posts:

• Intersection of the range of values
• MongoDB – Intersection of the range of multiple row values

Suppose we have a mongodb collection with 6 columns in object field:

• investmentParameters.capitalAppreciationFrom
• investmentParameters.capitalAppreciationTo
• investmentParameters.rentalYieldFrom
• investmentParameters.rentalYieldTo
• investmentParameters.occupancyRateFrom
• investmentParameters.occupancyRateTo

Now I would like to select rows where range *From / *To intersect with another ranges.

For example:

[
  {
    _id: 1,
    "investmentParameters": {
      "capitalAppreciationFrom": 100,
      "capitalAppreciationTo": 200,
      "rentalYieldFrom": 150,
      "rentalYieldTo": 155,
      "occupancyRateFrom": 170,
      "occupancyRateTo": 180
    }
  },
  {
    _id: 2,
    "investmentParameters": {
      "capitalAppreciationFrom": 150,
      "capitalAppreciationTo": 300,
      "rentalYieldFrom": 170,
      "rentalYieldTo": 200,
      "occupancyRateFrom": 170,
      "occupancyRateTo": 180
    }
  },
  {
    _id: 3,
    "investmentParameters": {
      "capitalAppreciationFrom": 210,
      "capitalAppreciationTo": 230,
      "rentalYieldFrom": 100,
      "rentalYieldTo": 110,
      "occupancyRateFrom": 500,
      "occupancyRateTo": 505
    }
  },
  {
    _id: 4,
    "investmentParameters": {
      "capitalAppreciationFrom": 300,
      "capitalAppreciationTo": 350,
      "rentalYieldFrom": 400,
      "rentalYieldTo": 450,
      "occupancyRateFrom": 560,
      "occupancyRateTo": 650
    }
  },
  {
    _id: 5,
    "investmentParameters": {
      "capitalAppreciationFrom": 400,
      "capitalAppreciationTo": 401,
      "rentalYieldFrom": 500,
      "rentalYieldTo": 503,
      "occupancyRateFrom": 700,
      "occupancyRateTo": 711
    }
  }
]

Let’s start with simple query with one range.

To build query we have this part of code:

...

foreach(filter.props, (key, val) => {
    ...
    
    if(empty(val.$gte) && !empty(val.$lte))
        val.$gte = 0;
    
    if((!empty(val.$gte) || val.$gte == 0) && !empty(val.$lte))
    {
        let sKey = key.replace('investmentParameters.', '');
                            
        oFilterRange[sKey + 'FilterFrom'] = val.$gte;
        oFilterRange[sKey + 'FilterTo'] = val.$lte;

        let oRange = {
            $not: {
                $or: [
                {
                    $gt: [
                        `$$${sKey}FilterFrom`,
                        `$${key}To`
                    ]
                },
                {
                    $gt: [
                        `$${key}From`,
                        `$$${sKey}FilterTo`
                    ]
                }]
            }
        };

        arRange.push(oRange);
    }

    ...

    if(!empty(arRange))
    {
        arFilter.$expr = {
            $let: {
                vars: oFilterRange,
                in: {
                    $and: arRange
                }
            }
        };
    }

    ...
}

So for one range we have this filter:

{
    $expr: {
        $let: {
            vars: {
                occupancyRateFilterFrom: 100,
                occupancyRateFilterTo: 1500
            },
            in: {
                $and: [
                {
                    $not: {
                        $or: [
                        {
                            $gt: [
                                $$occupancyRateFilterFrom,
                                $investmentParameters.occupancyRateTo
                            ]
                        },
                        {
                            $gt: [
                                $investmentParameters.occupancyRateFrom,
                                $$occupancyRateFilterTo
                            ]
                        }]
                    }
                }
            }
        }
    }
}

We check this filter there and it works! But when I use this filter for my DB it’s not works ((( Even if I try to use this query – it works!!! But when I use this filter for my DB it’s not works (((

What’s wrong? How can I fix it?