Auxílio com consultas onde é necessário group by com sum em campos

BIZ TMS Aurelius
1

wagner,
I would like to make a query which returns a list of objects. exe:
list of products with stock balance, but the repeated products need to be grouped by adding their stock balance.
How can I do this query? should i use projections?
I couldn't find any example for this.

Ex:

product balance
test1 2
test2 1
teste1 3

result queries:

test1 5
teste2 1

2

Hi Aléssio,

Yes, you should use projections, and it's described in the documentation:

Results := Manager.Find<TEstimate>
  .CreateAlias('Customer', 'c')
  .Select(TProjections.ProjectionList
    .Add(TProjections.Sum('EstimateNo'))
    .Add(TProjections.Group('c.Name'))
    )
  .ListValues;

References:

https://doc.tmssoftware.com/biz/aurelius/guide/queries.html#projections-overview

https://doc.tmssoftware.com/biz/aurelius/guide/queries.html#aggregated-functions

3

yes, but how do I get the object list from TCriteriaResult?

uses {...}, Aurelius.Criteria.Projections, 
  Aurelius.CriteriaBase, Aurelius.Criteria.Linq;

var
  Results: TObjectList<TCriteriaResult>;
begin
  Results := Manager.Find<TTC_Estimate>
    .CreateAlias('Customer', 'c')
    .Select(TProjections.ProjectionList
      .Add(TProjections.Sum('EstimateNo').As_('EstimateSum'))
      .Add(TProjections.Group('c.Name'))
      )
    .Add(Linq['c.Name'].Like('M%'))
    .OrderBy('EstimateSum')
    .ListValues;

  EstimateSum := Results[0].Values['EstimateSum'];
  CustomerName := Results[0].Values[1]; // no alias specified for c.Name
end;

Results[0].Values[1]? i try but not sucess

4

Correct, that's how you get the individual scalar values from each projection. There is no object list. Just a list of TCriteriaResult objects, and each TCriteriaResult has a list of scalar values.