SYS ExpressionQueryBuilder

downnload

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

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):

<xpp> InventDim inventDim;

   InventLocationID inventLocationID;

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

</xpp>

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

<xpp>

   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(01\01\2006, 31\12\2006));
   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));
   }

</xpp>

The code is very fat and contains lots of duplications

The Solution

SYS_ExpressionQueryBuilder provides sligntly more elegant way:

<xpp>

   InventTrans inventTrans;
   QueryRun qr = SYS_ExpressionQueryBuilder::construct()
       .dataSource(tableNum(InventTrans))
           .count(fieldNum(InventTrans, RecID))
           .groupBy(fieldNum(InventTrans, ItemID))
           .between(fieldNum(InventTrans, DatePhysical), 01\01\2006, 31\12\2006)
           .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));
   }

</xpp>

...to be continued...