Error grouping with TMS Aurelius

Hi, I have an error when query with grouping.

The code querying and grouping is this (snipped):

  var logs := TList<TGQLELog>.Create;
  try
    var model := CHANNEL_TEXT[CH_TRACE];
    var Manager := TObjectManager.Create(DBConnectionCache(model).Connection, TMappingExplorer.Get(model));
    try
      var events := Manager.Find<TEL_Events>
                      .Select(TProjections.ProjectionList
                        .Add(Linq['SessionId'].Group.As_('SessionId'))
                        .Add(Linq['Actor'])
                        .Add(Linq['Start'])
                        .Add(Linq['Stop'])
                        .Add(Linq['Elapsed']))
                      .Where(Linq.Eq('ChannelId', CH_TRACE) and
                             Linq.GreaterOrEqual('Start', dateFrom_) and Linq.LessOrEqual('Start', dateTo_) and
                             Linq.Eq('ClientId', 0))
                      .OrderBy('Start', True).OrderBy('Stop', False)
                      .List;
      for var E in events do
        logs.Add(TGQLELog.Create(E));
    finally
      Manager.Free;
    end;
    Result := logs.ToArray;
  finally
    logs.Free;
  end;

and the error is:

{
    "data": {
        "errors": [
            {
                "message": "Field is not included in GROUP BY list. Table name = 'A', Field name = 'ID', FieldNo = 0, Found field name = 'ID' - Native error: 10342",
                "locations": [
                    {
                        "line": 2,
                        "column": 5
                    }
                ],
                "extensions": {
                    "code": "EABSException"
                }
            }
        ]
    }
}

all fields exists in table, and ID field too but is not required for this query.

Whats is wrong ?

Thanks.

Esteban

Hi @Ricardi_Gustavo, welcome to our Support Center!

When you are building an aggregation query, all fields must be either a grouping now (Group) or an aggregate one (like Sum, Count, etc.). Exactly like an SQL statement.
So your fields Actor, Start, Stop and Elapsed should also be grouped - if that's what you want to do.

Thank you for your fastest response, the error persists, I have modified the code.

  var logs := TList<TGQLELog>.Create;
  try
    var model := CHANNEL_TEXT[CH_TRACE];
    var Manager := TObjectManager.Create(DBConnectionCache(model).Connection, TMappingExplorer.Get(model));
    try
      var events := Manager.Find<TEL_Events>
                      .Select(TProjections.ProjectionList
                        .Add(Linq['SessionId'].Group)
                        .Add(Linq['Actor'].Group)
                        .Add(Linq['Start'].Group)
                        .Add(Linq['Stop'].Group))
                      .Where(Linq.Eq('ChannelId', CH_TRACE) and
                             Linq.GreaterOrEqual('Start', dateFrom_) and Linq.LessOrEqual('Start', dateTo_) and
                             Linq.Eq('ClientId', 0))
                      .OrderBy('Start', True).OrderBy('Stop', False)
                      .List;
      for var E in events do
        logs.Add(TGQLELog.Create(E));
    finally
      Manager.Free;
    end;
    Result := logs.ToArray;
  finally
    logs.Free;
  end;

I know how grouping works in SQL but in ORM I losing the perspective. What is wrong now ?

Thanks.

Esteban

Another point: when using aggregated projections, you should use ListValues, not List. The return is not a list of TEL_Events objects, but a list of TCriteriaResult objects.

Reference:
https://doc.tmssoftware.com/biz/aurelius/guide/queries.html#results-with-projections

Thanks, works grouping all fields and using listvalues.

1 Like

This topic was automatically closed 60 minutes after the last reply. New replies are no longer allowed.