0

I have a problem when I want to query and compare nested documents and their fields within that document. Overall I have a database with restaurants. Each of the restaurants have a document called "openingHours" that contains a document for each day of the week with 3 fields

1 The day of the week

2 Opening time

3 Closing time

In my query the goal is to compare the opening time and closing time, for each of the documents (days).

The problem right now is that when I run my query I get a result based on all the documents inside "openingHours" and it doesn't compare each of the documents on its own. Reason behind the "$expr" is that the time the restaurants close can extend to 01:00 so we need to check for that as well.

Here is my query:

$or: [
      {
        $and: [
          { 
            "$expr" : {
                "$gt" : [
                    "$openingHours.open", 
                    "$openingHours.close"
                ]
            }
          },
          {
            "openingHours.close": {$gte: openingHours}
          }
        ]
      },
      {
        $and: [
          { 
            "$expr" : {
                "$gt" : [
                    "$openingHours.open", 
                    "$openingHours.close"
                ]
            }
          },
          {
            "openingHours.close": {$lte: openingHours}
          },
          {
            "openingHours.open": {$lte: openingHours}
          }
        ]
      },
      {
        $and: [
          { 
            "$expr" : {
                "$lt" : [
                    "$openingHours.open", 
                    "$openingHours.close"
                ]
            }
          },
          {
            "openingHours.open": {$lte: openingHours}
          },
          {
            "openingHours.close": {$gte: openingHours}
          }
        ]
      },
    ]
  })

Screenshot from Robo3t to help show what I mean with the documents. Also you can see in the image that the problem occurs when it doesn't make the query on each of the nested documents itself, since the time can differ from day to day.

Thanks in advance!

Community
  • 1
  • 1
Emil Falk
  • 3
  • 2
  • think you're not on the right way. Please provide a complete document in json format to help. – matthPen Jul 06 '18 at 10:40
  • Hi matth, here you go https://drive.google.com/open?id=1EeyZKXtHEGIHqdDIbLiBTUzM_E_VRoMa – Emil Falk Jul 06 '18 at 11:30
  • ty. But i don't really understand what you're expecting as final result : you just want to know if openingHours.open is sup/inf than openingHours.close? what is variable openingHours in your query ({$gte: openingHours})? – matthPen Jul 06 '18 at 11:45
  • The result should be a list of restaurants that have open - the variable openingHours will contain a number like 2300 that the user has passed in a form on the site. Then based on that number we need to check if the restaurant is open at that time using the open/closed numbers for each day. The problem is that right now it calculate all the days together so if i search for monday 08:00 and the restaurant is closed monday but has open tuesday on 08:00 it will still show since it looks through all the days. Hope it make sense, and thanks for your time. – Emil Falk Jul 06 '18 at 12:03

1 Answers1

1

First, !!it's a very bad idea to manage time and hours like this!! What happend if your restaurant is closed at 23:00, and i pass 2280 as param??? To deal with time without date information, it's better to base on seconds passed since 00:00:00.

storedTime = hours * 3600 + minutes * 60 + seconds

you have to manage your openingHours.close value with +86400 if necessary (close after midnight->+86400) before storing it. This way, you won't have to carry about openingHours.close greater or lesser than openingHours.open in your query.

{
"_id" : "hAZyWRwqzM5KM6TZz",
"name" : "Restaurant Spontan",
"openingHours" : [ 
    {
        "day" : 1,
        "open" : 72000,   // 20:00
        "close" : 79200   //22:00
    }, 
    {
        "day" : 2,
        "open" : 54000,    //   15:00
        "close" : 90000     // <= 01:00 = 24:00 (86400) + 01:00 (3600)
    }, 

   ...
]
}

Now you can easily query openingHours with element matching both day and current hour/minute between open and close times.

matthPen
  • 4,253
  • 1
  • 16
  • 16
  • Hi MatthPen, sorry for getting back so late. Thanks for the response though. I got it to work with your suggestion. Just FYI - in the system, you can't choose 2280 - since the data is coming from a drop-down where I can set the values myself. So before it was just 2200, 2300 and such. But I've changed it now to use seconds as you wrote. – Emil Falk Jul 18 '18 at 13:26