pouchDB and querying by date or date range

Question

I'm currently able to successfully fetch data from my database, create an index, and then get the most recent 7 records using pouchDB in a react.js progressive web app.

My hurdle currently is querying by dates. So with my current code, I'm getting all data, creating an index and then I'm getting only the 7 latest records so that I can map the data. But I'm trying to figure out how to use my moment date for the current day: newestDate: moment(this.currentDate).format("YYYY-MM-DD") and use this to say "get the most recent record from each day over the last 7 days"

So effectively, I have records which have the format of {caloriesBurned:"250", caloriesConsumed:"1450", createdAt:"2020-03-05"} and I want to be able to modify my query to get only one record for each day, starting today and going back for 7 days

How can I properly do this in pouchDB with this code:

fetchData = () => {
    this.setState({
        calorieElements: null,
    });
    this.state.caloriesDB.db.allDocs({
        include_docs: true,
    }).then(result => {
        const rows = result.rows;
        console.log('this is a row');
        console.log(result);
        this.setState({
            calorieElements: rows.map(row => row.doc),
        });
        console.log(this.state.calorieElements);
    }).catch((err) =>{
        console.log(err);
    });
  }

  getMax = () => {

    this.state.caloriesDB.db.createIndex({
      index: {
        fields: ['_id','caloriesBurned', 'createdAt']
      }
    }).then(result => {
      console.log(result);
      this.setMax();

   }).catch((err) =>{
      console.log(err);
    });
  }

  setMax = () => {
    this.state.caloriesDB.db.find({
      selector: {
        $and: [
          {_id: {"$gte": null}},
          {caloriesBurned: {$exists: true}},
          {caloriesConsumed: {$exists: true}},
          {createdAt: {$exists: true}}
        ]
      },
      fields: ['caloriesBurned','caloriesConsumed', 'createdAt'],
      sort: [{'_id':'desc'}],
      limit: 7
    }).then(result => {

      const newDocs = result.docs;
      const docCalories = newDocs.map(x => +x.caloriesConsumed - +x.caloriesBurned);

    }).catch((err) =>{
      console.log(err);
    });
  }

I was hoping for a solution with multiple per day (get the last record of the day in that case) but I'd accept a solution for one per day, as I can update the day's record — Geoff_S, Apr 08 '20 at 14:29

RamblinRose · Accepted Answer · 2020-04-08T15:00:58.670

Here's an example of the surely ways to achieve what you desire. I've kept the code as close to yours as possible. The key is the ordering of the index fields, with createdAt being first to facilitate sort.

let db;
// demo 
initDb().then(() => {
  // get last 7 days on 2020-03-09
  return queryCreatedAt("2020-03-09", 7, 'desc');
}).then(result => {
  // display results.
  console.log(result);
})

// query docs by createdAt 
function queryCreatedAt(date, limit, order) {
  return db.find({
    selector: {
      $and: [{
          createdAt: {
            $lte: date
          }
        },
        {
          caloriesBurned: {
            $exists: true
          }
        },
        {
          caloriesConsumed: {
            $exists: true
          }
        }
      ]
    },
    sort: [{
      createdAt: order
    }],
    limit: limit,
    fields: [
      'createdAt',
      'caloriesBurned',
      'caloriesConsumed'
    ]
  });
}

// init db instance
function initDb() {

  db = new PouchDB('test', {
    adapter: 'memory'
  });

  return db.bulkDocs(getDocsToInstall()).then(() => {
    return db.createIndex({
      index: {
        fields: [
          'createdAt',
          'caloriesBurned',
          'caloriesConsumed'
        ]
      }
    })
  })
}

// get test docs
function getDocsToInstall() {
  return [{
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-02-27"
    }, {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-02-28"
    }, {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-02-29"
    },
    {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-03-01"
    }, {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-03-02"
    },
    {
      "caloriesBurned": "950",
      "caloriesConsumed": "1950",
      "createdAt": "2020-03-03"
    },
    {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-03-04"
    },
    {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-03-05"
    },
    {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": "2020-03-06"
    },
    {
      "caloriesBurned": "450",
      "caloriesConsumed": "1450",
      "createdAt": "2020-03-07"
    },
    {
      "caloriesBurned": "550",
      "caloriesConsumed": "1550",
      "createdAt": "2020-03-08"
    }
  ]
}

<script src="//cdn.jsdelivr.net/npm/pouchdb@7.1.1/dist/pouchdb.min.js"></script>
<script src="https://github.com/pouchdb/pouchdb/releases/download/7.1.1/pouchdb.memory.min.js"></script>
<script src="https://github.com/pouchdb/pouchdb/releases/download/7.1.1/pouchdb.find.js"></script>

For supporting multiple entries on the same date, I would denormalize the indivdual documents presented to day (24 hour) based documents with entries in an array like this. I prefer using UTC over string dates - it's a personal choice.

{
  entries: [
    {
      "caloriesBurned": "350",
      "caloriesConsumed": "1350",
      "createdAt": 1586357069
    },
    {
      "caloriesBurned": "450",
      "caloriesConsumed": "1450",
      "createdAt": 1586357080
    },
    {
      "caloriesBurned": "550",
      "caloriesConsumed": "1550",
      "createdAt": 1586357140
    }
  ]
}

For inspiration as how to index and query items in an collection refer to the SO article Index and query items in an array with mango query for cloudant and couchdb 2.0

pouchDB and querying by date or date range

1 Answers1