add group by and fields selection in query

QueryBuildDataSource provides addSelectionField and addGroupByField to add fields and group on a query.

Further, what the aggregate function that you want to apply on a field you can provide it in a addSelectionField method that supports base enum SelectionField with some basic statistical operations. A sample code is given below.


Query q = new Query();
QueryBuildDataSource queryBuildDataSource;
QueryRun queryRun;
LedgerJournalTrans ledgerJournalTrans;

queryBuildDataSource = q.addDataSource(tablenum(LedgerJournalTrans));

queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans, JournalNum));
queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans,AmountCurDebit), SelectionField::Sum);
queryBuildDataSource.addSelectionField(fieldnum(LedgerJournalTrans, AmountCurCredit), SelectionField::Sum);
queryBuildDataSource.addGroupByField(fieldnum(LedgerJournalTrans, JournalNum));

queryRun = new QueryRun(q);

while(queryRun.next())
{
ledgerJournalTrans = queryRun.get(tablenum(LedgerJournalTrans));

info(strfmt('Journal Num = %1 Debit = %2 Credit = %3', ledgerJournalTrans.JournalNum, ledgerJournalTrans.AmountCurDebit, ledgerJournalTrans.AmountCurCredit));

}

An equivalent SQL query is given below.

select Journalnum, sum(AmountCurDebit), SUM(AmountCurCredit)
from LEDGERJOURNALTRANS
group by JOURNALNUM

Further you can filter the records using the addRange method in a QueryBuildDataSource class.

e.g.

queryBuildDataSource.addRange(fieldnum(LedgerJournalTrans, JournalNum)).value('000033_444')

the above statement filters the journal number '000033_444'.