Groupby based on result query / SubQuery

(0) Share

Report

Posted on by NoobGamer

Hi ,

I want to Query the Items whose modelGroup are stocked products . I can able to query those items with the following query .But after getting the resultant items I need to group the records based on ItemGroup So that I can get/Retrive the distinct Item Group values . I am unable to groupby the resultant Query . whether can I able to group by the resultant query ?

Query                          query = new query();
QueryBuildDataSource           invtbl,grp,mdl,mdlstk;
InventTable invTable;
InventItemGroupItem invGroup;
InventModelGroupItem invModel;
QueryBuildRange         queryBuildRange;

InventModelGroup stk;

invtbl = query.addDataSource(tableNum(InventTable));
grp = invtbl.addDataSource(tableNum(InventItemGroupItem));
grp.relations(false);
grp.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemGroupItem, ItemId));
grp.fetchMode(QueryFetchMode::One2One);


mdl = invtbl.addDataSource(tableNum(InventModelGroupItem));
mdl.relations(false);
mdl.addLink(fieldNum(InventTable, ItemId), fieldNum(InventModelGroupItem, ItemId));
mdl.fetchMode(QueryFetchMode::One2One);

mdlstk = mdl.addDataSource(tableNum(InventModelGroup));
mdlstk.relations(false);
mdlstk.addLink(fieldNum(InventModelGroupItem, ModelGroupId), fieldNum(InventModelGroup, ModelGroupId));


mdlstk.fetchMode(QueryFetchMode::One2One);
mdlstk.addRange(fieldNum(InventModelGroup,StockedProduct)).value(queryvalue(noyes::Yes));
// query.groupByField();
// query.groupByField(fieldNum(InventItemGroupItem, ItemGroupId),grp);
//QueryGroupbyfield = grp.addGroupByField(fieldNum(InventItemGroupItem, ItemGroupId));

//  invtbl.addOrderByField(fieldnum(InventTable, ItemId));
//grp.addGroupByField(fieldNum(InventItemGroupItem,ItemGroupId));
//grp.addSortField(fieldnum(InventItemGroupItem, ItemGroupId));
//grp.addOrderByField(fieldnum(InventItemGroupItem, ItemGroupId));
//grp.orderMode(OrderMode::GroupBy);

QueryRun qr = new QueryRun(query);

while(qr.next())
{
    invTable = qr.get(tableNum(InventTable));
    invGroup = qr.get(tableNum(InventItemGroupItem));
    invModel = qr.get(tableNum(InventModelGroupItem));

    stk = qr.get(tableNum(InventModelGroup));

    Info(strFmt("%1 ,%2 ,%3 ,%4 ,%5",invTable.ItemId,invGroup.ItemGroupId,invModel.ModelGroupId,stk.StockedProduct,stk.ModelGroupId));
}

I have try groupby Methods but it didnt work as Expected . Am I missing anything ? Can I add subquery for the following query to groupby ?

I have the same question (0)

All responses (12)

Answers (1)

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
Can you tell us more about your problem, please? " I am unable to groupby" and "it didnt work as Expected" can mean many things.

By the way, let me adjust your code a bit. For example, using the existing relation to join InventModelGroupItem and InventTable is not only easier than using addLink(), but you also forgot one field.

Query query = new query(); QueryBuildDataSource invtbl = query.addDataSource(tableNum(InventTable)); QueryBuildDataSource mdl = invtbl.addDataSource(tableNum(InventModelGroupItem)); mdl.relations(true); mdl.fetchMode(QueryFetchMode::One2One); QueryBuildDataSource mdlstk = mdl.addDataSource(tableNum(InventModelGroup)); mdlstk.relations(true); mdlstk.fetchMode(QueryFetchMode::One2One); mdlstk.addRange(fieldNum(InventModelGroup, StockedProduct)).value(queryValue(NoYes::Yes)); QueryRun qr = new QueryRun(query); while (qr.next()) { InventTable invTable = qr.get(tableNum(InventTable)); InventItemGroupItem invGroup = qr.get(tableNum(InventItemGroupItem)); InventModelGroupItem invModel = qr.get(tableNum(InventModelGroupItem)); InventModelGroup stk = qr.get(tableNum(InventModelGroup)); info(strFmt("%1 ,%2 ,%3 ,%4 ,%5", invTable.ItemId, invGroup.ItemGroupId, invModel.ModelGroupId, stk.StockedProduct, stk.ModelGroupId)); }

Was this reply helpful? Yes No
GirishS 27,827 Moderator on at

Like (0)

Report

Hi Noob,

What output you got after adding group by for Item Group field?

Thanks,

Girish S.

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report

That would mean that only ItemGroupId has a value. Only fields that are either grouped by or aggregated have a value defined, therefore we would need to decide what to do with all the other fields.

I wonder whether groupping by ItemGroupId makes sense at all if the goal is getting "items whose modelGroup are stocked products". If we don't group by item ID, we need to aggregate item ID, such as just getting the highest one in the given group. This seems to go against the requirement. And if we group by item ID, I don't see a reason for grouping by ItemGroupId.

Was this reply helpful? Yes No
NoobGamer 2 on at

Like (0)

Report

Hi Girish and Martin,

After Running the above Query which I have shared , I am getting the following output since I have added the range to the stocked products field , I can able to see whether the Item is stocked or not based on this result ,

But I want the ItemGroup(Kitchen) to be Grouped/Distinct When I try to add the groupby the ItemGroupid field I am getting other results where the ranges doesnt work and Item field model group field appers blank in the result . so I have commented those lines.

And I need only the ItemgroupId field after grouping because I need to use this query in the lookup

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
If all you want to get is a list of model group with StockedProduct = Yes, that are used by some items (and not items as such, as originally claimed), outputting any other field isn't useful and you don't need InventTable data source at all. Let me suggest this code:

Query query = new Query(); QueryBuildDataSource groupItemDs = query.addDataSource(tableNum(InventModelGroupItem)); groupItemDs.addGroupByField(fieldNum(InventModelGroupItem, ItemGroupId)); QueryBuildDataSource modelGroupDs = groupItemDs.addDataSource(tableNum(InventModelGroup)); modelGroupDs.relations(true); modelGroupDs.joinMode(JoinMode::ExistsJoin); modelGroupDs.addRange(fieldNum(InventModelGroup, StockedProduct)).value(queryValue(NoYes::Yes)); QueryRun qr = new QueryRun(query); while (qr.next()) { InventModelGroupItem modelGroupItem = qr.get(tableNum(InventModelGroupItem)); info(modelGroupItem.ItemGroupId); }

Isn't it what you want?

Was this reply helpful? Yes No
NoobGamer 2 on at

Like (0)

Report
Hi Martin,

ItemGroupId field doesnt belong to the table InventModelGroupItem ., ItemGroupId field belongs to InventItemGroupItem Table.

And yes . InventTable is not required in this case . But for understanding purpose I have added InventTable since in the Item Master Form has both ItemGroupId and ItemModelId.

And these two fields are from InventItemGroupItem(ItemGroupId) and InventModelGroupItem(ModelGroupId) . And the details about the stocked products are from InventModelGroup table .

So I have used the following Code

Query query = new query(); QueryBuildDataSource invtbl,grp,mdl,mdlstk; InventTable invTable; InventItemGroupItem invGroup; InventModelGroupItem invModel; QueryBuildRange queryBuildRange; InventModelGroup stk; invtbl = query.addDataSource(tableNum(InventTable)); grp = invtbl.addDataSource(tableNum(InventItemGroupItem)); grp.relations(false); grp.addLink(fieldNum(InventTable, ItemId), fieldNum(InventItemGroupItem, ItemId)); grp.fetchMode(QueryFetchMode::One2One); mdl = invtbl.addDataSource(tableNum(InventModelGroupItem)); mdl.relations(false); mdl.addLink(fieldNum(InventTable, ItemId), fieldNum(InventModelGroupItem, ItemId)); mdl.fetchMode(QueryFetchMode::One2One); mdlstk = mdl.addDataSource(tableNum(InventModelGroup)); mdlstk.relations(false); mdlstk.addLink(fieldNum(InventModelGroupItem, ModelGroupId), fieldNum(InventModelGroup, ModelGroupId)); mdlstk.fetchMode(QueryFetchMode::One2One); mdlstk.addRange(fieldNum(InventModelGroup,StockedProduct)).value(queryvalue(noyes::Yes)); QueryRun qr = new QueryRun(query); while(qr.next()) { invTable = qr.get(tableNum(InventTable)); invGroup = qr.get(tableNum(InventItemGroupItem)); invModel = qr.get(tableNum(InventModelGroupItem)); stk = qr.get(tableNum(InventModelGroup)); Info(strFmt("%1 ,%2 ,%3 ,%4 ,%5",invTable.ItemId,invGroup.ItemGroupId,invModel.ModelGroupId,stk.StockedProduct,stk.ModelGroupId)); }

And the result of this code is

Further I need to do some modification in the code so that I can get the distinct values for ItemGroupId which is (Kitchen) . But I am unable to groupby based on on the resultant query .Whether can I group this reults based on ItemGroupId ?

Was this reply helpful? Yes No
Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
Sorry about the table name. Changing it sounds easy. Does it look correct now?

Query query = new Query(); QueryBuildDataSource groupItemDs = query.addDataSource(tableNum(InventItemGroupItem)); groupItemDs.addGroupByField(fieldNum(InventItemGroupItem, ItemGroupId)); QueryBuildDataSource modelGroupDs = groupItemDs.addDataSource(tableNum(InventModelGroup)); modelGroupDs.relations(true); modelGroupDs.joinMode(JoinMode::ExistsJoin); modelGroupDs.addRange(fieldNum(InventModelGroup, StockedProduct)).value(queryValue(NoYes::Yes)); QueryRun qr = new QueryRun(query); while (qr.next()) { InventItemGroupItem groupItem = qr.get(tableNum(InventItemGroupItem)); info(groupItem.ItemGroupId); }

Yes, you can group by ItemGroupId. But I can't give you a solution for "I am unable to groupby based on on the resultant query", because I don't know what you mean by not being able. I suggest you forget your code for a moment and look at mine instead.

Was this reply helpful? Yes No
NoobGamer 2 on at

Like (0)

Report

Hi Martin ,

how do you relate InventItemGroupItem with InventModelGroup ? As there is no relation between these two tables

SELECT * FROM InventItemGroupItem(InventItemGroupItem_1) GROUP BY InventItemGroupItem.ItemGroupId EXISTS JOIN * FROM InventModelGroup(InventModelGroup_1) WHERE ((StockedProduct = 1))

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,801 Most Valuable Professional on at

Like (0)

Report
Sorry, I was only guessing - I'm not at work at the moment. And I was confused by those tables with similar names. Now your query makes more sense to me.

It seems that you want something like this:

select ItemGroupId InventItemGroupItem group by ItemGroupId join InventTable join InventModelGroupItem join InventItemGroupItem join InventModelGroup where StockedProduct = Yes

And this will be real code:

Query query = new Query(); QueryBuildDataSource groupItemDs = query.addDataSource(tableNum(InventItemGroupItem)); groupItemDs.addGroupByField(fieldNum(InventItemGroupItem, ItemGroupId)); QueryBuildDataSource inventTableDs = groupItemDs.addDataSource(tableNum(InventTable)); inventTableDs.relations(true); inventTableDs.joinMode(JoinMode::ExistsJoin); QueryBuildDataSource modelGroupItemDs = inventTableDs.addDataSource(tableNum(InventModelGroupItem)); modelGroupItemDs.relations(true); modelGroupItemDs.joinMode(JoinMode::ExistsJoin); QueryBuildDataSource modelGroupDs = modelGroupItemDs.addDataSource(tableNum(InventModelGroup)); modelGroupDs.relations(true); modelGroupDs.joinMode(JoinMode::ExistsJoin); modelGroupDs.addRange(fieldNum(InventModelGroup, StockedProduct)).value(queryValue(NoYes::Yes)); QueryRun qr = new QueryRun(query); while (qr.next()) { InventItemGroupItem groupItem = qr.get(tableNum(InventItemGroupItem)); info(groupItem.ItemGroupId); }

Does it look correct now?

And can you know finally explain your problem? You still didn't tell us what you mean by "not being able to group".

Was this reply helpful? Yes No
NoobGamer 2 on at

Like (0)

Report

"not being able to group" It means I want distinct values of itemGroupId

In this distinct value of ItemgroupId

Was this reply helpful? Yes No