Forum | Appery.io

I am trying to search for database records from the previous year using this code:

codevar getYear = (new Date()).getFullYear();
var yr = getYear-1;
var sYear = yr.toString();
//alert(sYear);
console.log("date = " + sYear);

var whereObject ={

      "$or": [ 
  {"Date":{"$regex":sYear*,"$options":"i"}}, 
      {"Date":{"$regex":sYear$,"$options":"i"}} 

]
};
return JSON.stringify(whereObject);/code

I am searching a column called "Date" but it is a string type. Some dates are formatted as yyyy-mm-dd and some are mm/dd/yyyy. Therefore, I need to search
the beginning and the end.

Unfortunately, this code returns everything in the database whether it matches or not.

However, I found that I get the right results when I hard code the year in like this:

code{"Date":{"$regex":2016*,"$options":"i"}},
{"Date":{"$regex":2016$,"$options":"i"}}/code

So basically, I have some small syntax issue when I try to use a variable instead of an actual value. Any ideas on how to make this work?

Ellen,

A few observations ... first in returning a where object ... you can return the object and you don’t have to stringify it ... it’s simplier and easier to read and the Appery engine will consume and send it just fine.

2nd ... prior to returning it ... ( the where object ) you should console Log it by Saying console.log ( whereObject ). You can then expand the object by pressing the in the console Log .

3rd ... your syntax sYear* looks suspect ... after your Regex statement and likely blows up inside your mapping ( if that’s where you are doing it ) causing the where clause to return nothing and thus all of your records are coming back.

4th ... for reason 3 ... all where clauses should be computed in a JavaScript asset so you can debug them ... in your where mapping :

return fMakewhere()

and then in a separate JavaScript

function fMakewhere() {

Your code

return YourwhereObject
}

5th .. the suspect Code is sWhere* ... which is undefined ...proper syntax would likely be sWhere + ‘*’ , “options” etc

But you can debug that ... if you stop execution with the debugger in your new JavaScript and Fix the syntax on the fly .

Best,
Bruce

Hello Ellen,

Please use plain requests without the regular expressions, using the date format (and operators $lt, $gt, etc.): https://docs.appery.io/reference#data...

Thank you, Bruce. I found that the issue was with what you said in your 5th statement. I needed to add +"*" instead of the way I was doing it.

Per Ellen's original post - the data is 'string' and the operations referred to above won't work with her string data - because it's stored inconsistently.

anytime