Forum | Appery.io

I could do with a little help on a DB query. I want to return rows WHERE: One field of the row matches a local variable AND another is NOT NULL.

The names:

• Database field is: "CaptureEntry_By" (string) (should not be NULL)

• Local variable is "VarSessionID" which must match database field "Session_ID"

  So far the best I have managed is this:
  var SessionID = localStorage.getItem("VarSessionID");
  return '{"$and": [{"Session_ID": "'+SessionID+'"},{"SessionEntry_By": {"$exists":false}}]}';

  But it fails (doesnt return anything)

  The other question I have is, do I have to input these Where statements at the Service level or can I do it in the data source mapping? At present I'm doing it with duplicate services but its messy. It would be much nicer to do it in the mapping somehow.

Hi Oliver,

Use the following code please:prereturn '{"$and": [{"Session_ID": "'+SessionID+'"}, {"SessionEntry_By":{"$regex":".", "$options":"i"}}]}';/pre

Great thank you.
Should that be +VarSessionID+ ? Or the name of the variable will be wrong?

Also could you show me how the query should look if I want the field to be Null vs not Null (so two seperate queries)

Hi Oliver,

This example is for Session_ID field value = SessionID variable value and not empty SessionEntry_By.

You could also check whether a field value is empty, using codeexists/code: http://docs.mongodb.org/manual/refere...

That is, parameter 'where' code{"SessionEntry_By": { $exists: false}}/code
returns all records where SessionEntry_By is empty.

I placed the code:

return '{"$and": [{"Session_ID": "'+VarSessionID+'"}, {"SessionEntry_By":{"$regex":".", "$options":"i"}}]}';

Into the where property on the service but there was no result when I invoked the service against a list.

Should I be mapping a field into the request? What am I doing wrong?

Katya, thats what I put in my original question!

var SessionID = localStorage.getItem("VarSessionID");
return '{"$and": [{"Session_ID": "'+SessionID+'"},{"SessionEntry_By": {"$exists":false}}]}';

The query should bring back all rows WHERE
Local variable matches collection field A
AND
collection field B is NOT EMPTY.

So I'm trying this with no success:

return '{"$and": [{"Session_ID": "'+VarSessionID+'"},{"SessionEntry_By": {"$exists":false}}]}';

Hi Oliver,

Please use following code:

pre

var whereObject = {"$and": [ {"Session_ID": VarSessionID }, {"SessionEntry_By": {"$regex": ".", "$options": "im"} } ] };

return JSON.stringify(whereObject);

/pre

If you still have a problem please:

1 Make screen shot on browser debuger network tab where we can see this reqгest with "where" parameter.

2 Make screen shots of your DB collection(with data).

Regards.