0

I currently have a setup with multiple requests to the database in a for-loop.

// week is a moment-range object
for (const day of week.by('day')) {
    // get start and end of day
    const startDay = day.startOf('day').toDate();
    const endDay = day.endOf('day').toDate();

    // I would like to reduce this query to one
    const data = await Model.find({
      $or: [
        {
          $and: [{ start: { $gte: startDay } }, { start: { $lt: endDay } }],
        },
        {
          $and: [{ end: { $gte: startDay } }, { end: { $lt: endDay } }],
        },
      ],
    })
      .sort({ start: 'asc' })
      .select('_id someref')
      .populate('someref', 'name');

    console.log(data);
}

This is not very efficient and therefore I would like to reduce it to one query, grouped by the current return of data.

I've tried already to prepare the find parameter in the for-loop but didn't get far. Any hints would be very much appreciated.

trincot
  • 317,000
  • 35
  • 244
  • 286
Luke
  • 185
  • 1
  • 3
  • 16
  • Does `week` represent a list of *consecutive* days? I don't know the `by` method of moment. Do you have a link to the documentation? – trincot Aug 20 '20 at 15:00
  • Super sorry. It is a moment-range object: `moment.range(firstDayOfWeek, lastDayOfWeek);` The `by`function returns a list of days in this case: [link](https://github.com/rotaready/moment-range#by) – Luke Aug 20 '20 at 15:16

1 Answers1

0

Your query seems to find records that either start or end in a given day. As week is a range of consecutive days, this really translates to a query for records that either start in that week, or end in that week.

So you could remove the for loop, and define startDay and endDay as the start/end of the week, as follows:

const startDay = week.start.startOf('day').toDate();
const endDay = week.end.endOf('day').toDate();

The rest of your code can remain as it is. Just remove the line with for and the corresponding ending brace.

One difference though is that you wouldn't get any duplicates any more. In your current code you would get records that both start and end in the week (but not on the same day) twice. That will not happen with this code.

trincot
  • 317,000
  • 35
  • 244
  • 286
  • Thank you. This seems to be heading in the right direction. Is there a way to group the data as I am having it currently "grouped" or should I do this in javascript? – Luke Aug 20 '20 at 15:48
  • I don't really know what you mean with grouping in this context. – trincot Aug 20 '20 at 16:07
  • I have now rewritten the query/logic a bit and base it on the aggregation function of mongodb. Due to this I am able to get the data based on days. Thank you for your help! – Luke Aug 21 '20 at 21:17
  • I have now rewritten the query/logic a bit and base it on the aggregation function of mongodb. Due to this I am able to get the data based on days. Thank you for your help! – Luke Aug 21 '20 at 21:17