DEV Community

loading...

Jelly Fin: Should I use a query or scan?

sublimegeek profile image Jonathan Irvin ・1 min read

Hey Dev.to,

I have an endpoint where I'm trying to get a subset of transactions by date using between. When I run this, I get no results. Odd thing is, I get no errors either.

I know, I know...why not post this on StackOverflow. I don't feel like being judged and you guys are much more supportive. :)

// get transactions for {month} and {year}
api.get('/transaction/tab/{year}/{month}', (request) => {
  const year = request.pathParams.year
  const month = request.pathParams.month
  const params = {
    TableName: request.env.tableName,
    KeyConditionExpression: '#date between :val1 and :val2',
    ExpressionAttributeNames: {
      '#date': 'date'
    },
    ExpressionAttributeValues: {
      ':d': 'date',
      ':val1': year +'-'+month+'-01',
      ':val2': year +'-'+month+'-'+getDaysInMonth(month, year)
    }
  }

  console.log(params)

  // post-process dynamo result before returning
  dynamoDb.query(params, (err, data) => {
    console.log(data)
    if (err) {
      console.error('Unable to query. Error:', JSON.stringify(err, null, 2))
      return 'Unable to query. Error: '+ JSON.stringify(err, null, 2)
    } else {
      console.log('Query succeeded.')
      data.Items.forEach((item) => {
        console.log(' -', item.year + ': ' + item.title)
      })
      return data.Items
    }
  })
})

Here's my data model:

{
    id: String
    date: String
    ...
}

Discussion

pic
Editor guide
Collapse
kip13 profile image
kip

I think the problem is here:

ExpressionAttributeValues: {
  ':d': 'date',
  ':val1': year +'-'+month+'-01',
  ':val2': year +'-'+month+'-'+getDaysInMonth(month, year)
}

If you see the docs you can figure out how you need write the correct values for ExpressionAttributeValues, something like that maybe:

ExpressionAttributeValues: {
  ':val1': {
    'S': year +'-' + month + '-01'
  },
  ':val2': {
    'S': year + '-' + month + getDaysInMonth(month, year)
  }
}

':d': 'date' isn't necessary...

Collapse
sublimegeek profile image
Jonathan Irvin Author

@kip13 thank you! I'm going to try that out now. Date is a restricted word in AWS. Restricted Words. Lame? Right?

Collapse
sublimegeek profile image
Jonathan Irvin Author

Tried that and we're still not getting results. I'm storing dates as strings and "2018-01-01" as an example. Should I just start storing dates as timestamps?

Collapse
kip13 profile image
kip

Isn't neccesary, you can use Strings

Can you try with FilterExpression ?

Collapse
kip13 profile image
kip

If date isn't an index or sort key you need to use FilterExpression see

Collapse
kip13 profile image
kip

@sublimegeek it works now ?

Thread Thread
sublimegeek profile image
Jonathan Irvin Author

Not quite yet. I just made the source public, if you want to check it out.

jonathan-irvin / jelly-fin

A simple way to manage your finances with forecasting. We should automate our money, not make it automate us.

Jelly Fin

Finances are hard. It's one of the first adulting things everyone has to wrestle with. So, let's make it easy and automate it. Over the course of several years, my wife and I have tracked our finances using a forecasting method and had done it all within a spreadsheet. The time came where I wanted to take this concept and make it mobile using serverless architecture and clean design.

The idea is to take what we know: the transactions that are predictable like mortgage and insurance and just keep track of them against the account balance. Then take transactions that aren't predictable and try to predict them using seasonality analytics. Does your electric bill go up in the summer? Let's predict what it would be and try and account for it.


Collapse
sublimegeek profile image
Jonathan Irvin Author

Wait. I see what you mean.

Collapse
sublimegeek profile image
Jonathan Irvin Author

@kip13 I think for my head I may use timestamps for the date. The math just works better that way.