Using two types of query in the Where statement.

[Oliver Fisk]

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.

[Kateryna Grynko]

Hi Oliver,

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

[Oliver Fisk]

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

[Oliver Fisk]

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)

[Kateryna Grynko]

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.

[Oliver Fisk]

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?

[Oliver Fisk]

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.

[Oliver Fisk]

So I'm trying this with no success:

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

[Yurii Orishchuk]

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.