Two conditions in where clause

This forum contains topics that were moved from Get Satisfaction
Hawk
Posts: 0
Joined: Mon Aug 04, 2014 11:23 am

Two conditions in where clause

Post by Hawk »

I mapped as below and added the following JS:

return '{"$and":[{"UserId": "' + value + '"},{"MPD_month": "' + value + '"}]}';

where UserId is a column name but MPD_month is a LSV name. Is this a correct way of setting where clause for both? MPD_month has a value of 'SEP 2014' which conform one of the records in the DB, but when I invoke the service, it gets the value "undefined"!

What I am trying to ahcieve is return values by userId and month. The userId is working fine but when I added the month condition it's not working anymore!

Thank you for your help

Image

Kateryna Grynko
Posts: 0
Joined: Thu Nov 15, 2012 9:13 am

Two conditions in where clause

Post by Kateryna Grynko »

Hi Hawk,

You cannot map 2 fields to 'where' parameter. If you have localStorage variables userId and MPD_month, you could map one value, for example, MPD_month, and read userId from localStorage.

So instead of:prereturn '{"$and":[{"UserId": "' + value + '"},{"MPD_month": "' + value + '"}]}';/preThere will be:prereturn '{"$and":[{"UserId": "' + Apperyio.storage.userId.get() + '"},{"MPD_month": "' + value + '"}]}';/pre

Hawk
Posts: 0
Joined: Mon Aug 04, 2014 11:23 am

Two conditions in where clause

Post by Hawk »

Hi Kateryna,

I used the following:

codereturn '{"$and":[{"UserId": "' + value + '"},{"Month_Year": "' + Apperyio.storage.Month_Year.get() + '"}]}';/code

where I mapped only UserId to Where condition and added the above JS to where. The results I get are all zeros.

To clairfy more, where condition is on map request. On success, I run the following JS to calculate the totals and display them on corresponding label:

codefunction numberWithCommas(x) {
var parts = x.toString().split(".");
parts[0] = parts[0].replace(/\B(?=(\d{3})+(?!\d))/g, ",");
return parts.join(".");
}

var vis = 0; var int = 0; var net = 0; var bon = 0;var petm = 0; var pcf = 0;
for (var i=0; i<data&#46;length; i++) {
vis+=+data&#46;MPD_Visibility;
int+=+data&#46;MPD_Interact;
net+=+data&#46;MPD_Sales;
bon+=+data&#46;MPD_Bonus;
petm+=+data&#46;MPD_Points_Month;
pcf+=+data&#46;MPD_Points_Carried_Forward;
}
vis = numberWithCommas(vis);
int = numberWithCommas(int);
net = numberWithCommas(net);
bon = numberWithCommas(bon);
petm = numberWithCommas(petm);
pcf = numberWithCommas(pcf);
Appery("mobilelabel_124_130")&#46;text(vis);
Appery("mobilelabel_152")&#46;text(int);
Appery("mobilelabel_154")&#46;text(net);
Appery("mobilelabel_156")&#46;text(bon);
Appery("mobilelabel_162")&#46;text(petm);
Appery("mobilelabel_161")&#46;text(pcf);/code

This without adding a second condition to where (mean when I use only UserId: return '{"UserId": "' + value + '"}';), everthing is working fine. However, it will return the totals regardless the month. So all what I need to add, is to show the result based on the month chosen. The month value is passed via radio button to the LSV : Month_Year. And there is a column in the DB table called MPD_Month.

I hope it's clear. Many thanks for your help

Hawk
Posts: 0
Joined: Mon Aug 04, 2014 11:23 am

Two conditions in where clause

Post by Hawk »

Sorry, I found the error, where JS should be like this:
return '{"$and":[{"UserId": "' + value + '"},{"MPD_Month": "' + Apperyio.storage.Month_Year.get() + '"}]}'

Many thanks

Yurii Orishchuk
Posts: 0
Joined: Fri Feb 14, 2014 8:20 am

Two conditions in where clause

Post by Yurii Orishchuk »

Hi Hawk,

Please show us request from net tab where we can see this "where " parameter and show us your DB with items which is you want to get.

Regards.

Hawk
Posts: 0
Joined: Mon Aug 04, 2014 11:23 am

Two conditions in where clause

Post by Hawk »

Hi Yurri,

Kateryna's solution worked for me. However, I treid to use the same method in order to present data using list service whereby UserId matches and MSG_status does not match. In other words, I wanted to present all messages that belong to a particular user but do not have the status "Deleted" (which is stored in LSV: Msg_delete_status). However, when I used with ($not), no messages appear at all (although no message has the status (Deleted). I tried the following:

prereturn '{"$and":[{"UserId": "' + value + '"},{"MSG_status": {"$not": {"' + Apperyio&#46;storage&#46;Msg_delete_status&#46;get() + '"}}}]}';/pre

and:

prereturn '{"$and":[{"UserId": "' + value + '"},{"MSG_status": {"$notInQuery": {"' + Apperyio&#46;storage&#46;Msg_delete_status&#46;get() + '"}}}]}';/pre

But when I used this:

prereturn '{"UserId": "' + value + '"}';/pre

It presents all messages belong to the particular user. So my guessing is that they way I use (not) is troubled.

I deeply appreciate your help.

Yurii Orishchuk
Posts: 0
Joined: Fri Feb 14, 2014 8:20 am

Two conditions in where clause

Post by Yurii Orishchuk »

Hi Hawk,

"$not" used in other condition for example when you need "not lower" value, or "not regexp".

In your case when you need "not value" please use "$ne".

pre

var whereObject = {"$and":[{"UserId": value}, {"MSG_status": {"$ne": Apperyio&#46;storage&#46;Msg_delete_status&#46;get() }}]};

return JSON&#46;stringify(whereObject);

/pre

More details here:
http://devcenter.appery.io/documentat...

Regards.

Post Reply