Server Code - DB Query - Group by + Count

Tue Oct 21, 2014 8:24 pm

Hi,
I have the following set of tables and relation:
ITEM - table that contains items
SUB_ITEM - table that contains sub items with pointer to its related item
BASKET - table that contains basket info, In stoke column and additional pointer column to sub item (that has in it pointer to item).
*DB is shared (iTestDB).
I want to run a query using appery server code that will return to me list of records group by item id (not sub item, to use the pointer in it) that has "Shop" value inside inStoke column with a count per each.
i.e the expectation to get item id and the amount of times its appears:
5446bb72e4b03d005b61b6fd, 3
5446bc17e4b03d005b61b703, 1

Can this be done using query?

Wed Oct 22, 2014 6:07 am

Hello Yaniv,

Could you please specify your issue with details? What have you done, what you can't do?

Wed Oct 22, 2014 6:26 am

"I want to run a query using Appery server code that will return to me list of records group by item id (not sub item, to use the pointer in it) that has "Shop" value inside inStoke column with a count per each." - without the need to know the item._id (like a query that summarize all info in the table based on given criteria as specified above)

Since I didn't find a way to solve it in a single query - I'm doing it by taking all item_.id from the ITEM table (1st query) and based on result query one by one to get the info from the STOCK table (2nd query).

The way that I'm doing it today is wrong from several perspectives!
since that I wanted to know how this can be done using single query.

Thu Oct 23, 2014 6:14 pm

Any news?

Thu Oct 23, 2014 6:22 pm

Hello Yaniv,

Unfortunately not yet. We are working on it at the moment, hope we'll get back to you with the update soon.

Tue Oct 28, 2014 4:28 pm

Hello Yaniv,

Sorry for delay. Please make a query with where = '{"InStock":"Shop"}'
and with include = ItemRef, ItemRef.ItemId
You will get in response something like
precode[
{
"id":"5446bc42e4b03d005b61b704",
"Name":"TargetBuyerA",
"createdAt":"2014-10-21 20:04:18.775",
"updatedAt":"2014-10-21 20:05:32.851",
"InStock":"Shop",
"ItemRef":{
"id":"5446bb80e4b03d005b61b6fe",
"Name":"Dril",
"createdAt":"2014-10-21 20:01:04.458",
"updatedAt":"2014-10-21 20:01:13.126",
"ItemId":{
"id":"5446bb72e4b03d005b61b6fd",
"Name":"Electric",
"createdAt":"2014-10-21 20:00:50.467",
"updatedAt":"2014-10-21 20:00:50.467"
}
}
},
{
"id":"5446bc5ae4b03d005b61b705",
"InStock":"Shop",
"createdAt":"2014-10-21 20:04:42.815",
"updatedAt":"2014-10-21 20:22:12.343",
"ItemRef":{
"id":"5446bb9ae4b03d005b61b700",
"ItemId":{
"id":"5446bb72e4b03d005b61b6fd",
"Name":"Electric",
"createdAt":"2014-10-21 20:00:50.467",
"updatedAt":"2014-10-21 20:00:50.467"
},
"createdAt":"2014-10-21 20:01:30.027",
"updatedAt":"2014-10-21 20:02:15.039",
"Name":"Lawn Mower"
},
"Name":"TargetBuyerB"
},
{
"id":"5446bca8e4b03d005b61b707",
"ItemRef":{
"id":"5446bb9de4b03d005b61b701",
"ItemId":{
"id":"5446bc17e4b03d005b61b703",
"Name":"Manual",
"createdAt":"2014-10-21 20:03:35.863",
"updatedAt":"2014-10-21 20:03:35.863"
},
"createdAt":"2014-10-21 20:01:33.834",
"updatedAt":"2014-10-21 20:03:55.322",
"Name":"Screwdriver"
},
"createdAt":"2014-10-21 20:06:00.676",
"_updatedAt":"2014-10-21 20:06:11.643",
"Name":"TargetBuyerD",
"InStock":"Shop"
}
]/code/pre
As a result you get the whole info for one request. Running in a loop you may form data in the desired form.

Tue Oct 28, 2014 7:30 pm

Thank you for your answer.
My expectation from the query is to group by and count the response based on the itemId.
In that case we will get 2 records as a response:
1 will contains 2 items (for itemId: 5446bb72e4b03d005b61b6fd there are 2 items connected to it).
The other only 1 (for itemId: 5446bc17e4b03d005b61b703 there is 1 item only connected to it).

Can it be done using single query using Appery?
,

Tue Oct 28, 2014 8:15 pm

Yaniv,

You can't count the number of records in a single query at the same time. You may slightly reduce traffic due to the fact that you will get not all the data but only needed one. Please add request parameter proj with a value
{"ItemRef": {"ItemId": 1}}
Then in response you will get something like
precode[
{
"id":"5446bc42e4b03d005b61b704",
"ItemRef":{
"id":"5446bb80e4b03d005b61b6fe",
"ItemId":{
"id":"5446bb72e4b03d005b61b6fd",
"Name":"Electric",
"createdAt":"2014-10-21 20:00:50.467",
"updatedAt":"2014-10-21 20:00:50.467"
}
}
},
{
"id":"5446bc5ae4b03d005b61b705",
"ItemRef":{
"id":"5446bb9ae4b03d005b61b700",
"ItemId":{
"id":"5446bb72e4b03d005b61b6fd",
"Name":"Electric",
"createdAt":"2014-10-21 20:00:50.467",
"updatedAt":"2014-10-21 20:00:50.467"
}
}
},
{
"id":"5446bca8e4b03d005b61b707",
"ItemRef":{
"id":"5446bb9de4b03d005b61b701",
"ItemId":{
"id":"5446bc17e4b03d005b61b703",
"Name":"Manual",
"createdAt":"2014-10-21 20:03:35.863",
"updatedAt":"2014-10-21 20:03:35.863"
}
}
}
]/code/pre
Running in a loop through the resulting array, you will count the number of records for each item.
The server code is
precodevar DB_id = "xxxxxxxxxxxxxxxxx&quot
var collectionName = "BASKET&quot
try {
var result = {}, query, i, id;
var params = {}; //Define parameters object
params.criteria = {"InStock": "Shop"};
params.proj = '{"ItemRef": {"ItemId": 1}}';
params.include = "ItemRef, ItemRef.ItemId&quot
query = Collection.query(DB_id, collectionName, params);
for (i = 0; i < query.length; i++) {
id = query["ItemRef"]["ItemId"]["id"];
if (result[id]) {
result[id]++
} else {
result[id] = 1;
}
}
response.success(result); //Pass the result object to the response.
} catch (e) {
response.success("message: " + e.message + "\ncode: " + e.code); //If something goes wrong error message will appear
}/code/pre

Wed Oct 29, 2014 8:48 pm

Thanks

Mon Aug 24, 2015 11:04 am

Hello Yaniv,

I try to retreive a field of a pointer in the Server Code's query, but nothing

That what I have
var CollectionNameResult = Collection.query (dbId, CollectionName, params, token);

My CollectionNameResult.lenght still equal to 0

I tried all of the following "params.crietria" instruction:

"CollectionName" have this pointer among his columns: "PointerToOtherCollection"

Which one or other one is right?

1) params.criteria = {'CollectionName.PointerToOtherCollection.ColumnToFilterOn': FilteringValue };
2) params.criteria = {'PointerToOtherCollection.ColumnToFilterOn': FilteringValue };

3) params.criteria = {'CollectionNameResult.PointerIdToOtherCollection.ColumnToFilterOn': FilteringValue };

4) params.criteria = {'{"PointerIdToOtherCollection": {"ColumnToFilterOn"}}': FilteringValue };

5) Other way?

I'm hopeless.