SYS ExpressionQueryBuilder

From Axaptapedia

downnload

[edit] What is it?

SYS_ExpressionQueryBuilder is a way to easily code queries with dynamic structure. It is based on Expression Builder pattern by Martin Fowler

[edit] The problem

For example, you have the following code (select number of negative inventory transactions in year 2006 grouped by ItemID, in some specific inventory location):

InventDim inventDim;
    InventLocationID inventLocationID;
;
 	while select count(recID) from inventTrans group by ItemID
        where inventTrans.DatePhysical >= 01012006
              &&
              inventTrans.DatePhysical <= 31122006
              &&
              inventTrans.Qty < 0
        exists join inventDim
            where inventDim.inventDimId == inventTrans.inventDimId
                  &&
                  inventDim.InventLocationId == inventLocationID
    {
        info(strFmt('%1: %2', inventTrans.ItemId, InventTrans.RecId));
    }

Now somebody wants to use the same query, but invent location should be specified by standard filter. There is no way except using QueryBuildDataSources:

Query q=new Query();
    QueryBuildDataSource dsInventTrans = q.addDataSource(tableNum(InventTrans));
    QueryBuildDataSource dsInventDim = dsInventTrans.addDataSource(tableNum(InventDim));
;
    dsInventDim.joinMode(JoinMode::ExistsJoin);
    dsInventDim.fetchMode(QueryFetchMode::One2One);
    dsInventDim.addLink(fieldNum(InventTrans, InventDimID),
                        fieldNum(InventDim, InventDimID));
 
    dsInventTrans.addSelectionField(fieldNum(InventTrans, RecID), SelectionField::Count);
    dsInventTrans.orderMode(OrderMode::GroupBy);
    dsInventTrans.addSortField(fieldNum(InventTrans, ItemID));
    dsInventTrans.addRange(fieldNum(InventTrans, DatePhysical)).value(queryRange(01012006, 31122006));
    dsInventTrans.addRange(fieldNum(InventTrans, Qty)).value('<0');
 
    dsInventDim.addRange(fieldNum(InventDim, InventLocationID)).value(condition);
 
  	while(qr.next())
    {
        inventTrans = qr.get(tableNum(InventTrans));
        info(strFmt('%1: %2', inventTrans.ItemId, InventTrans.RecId));
    }

The code is very fat and contains lots of duplications

[edit] The Solution

SYS_ExpressionQueryBuilder provides sligntly more elegant way:

InventTrans inventTrans;
    QueryRun qr = SYS_ExpressionQueryBuilder::construct()
        .dataSource(tableNum(InventTrans))
            .count(fieldNum(InventTrans, RecID))
            .groupBy(fieldNum(InventTrans, ItemID))
            .between(fieldNum(InventTrans, DatePhysical), 01012006, 31122006)
            .matches(fieldNum(InventTrans, Qty), '<0')
 
        .exists(tableNum(InventDim))
            .link(fieldNum(InventTrans, InventDimID),
                  fieldNum(InventDim, InventDimID))
            .matches(fieldNum(InventDim, InventLocationID), condition)
    .run();
;
 	while(qr.next())
    {
        inventTrans = qr.get(tableNum(InventTrans));
        info(strFmt('%1: %2', inventTrans.ItemId, InventTrans.RecId));
    }

...to be continued...