mORMot Open Source

Martin.Reiner

Guest

Getting SUM of certain values from 1:N table

Hi there,

I have the following data structure (simplified)

type
  TSQLTask = class(TSQLRecord)
  private
    FTaskTitle: RawUTF8;
    FTaskDescription: RawUTF8;
  published
    property TaskTitle: RawUTF8 read FTaskTitle write FTaskTitle;
    property TaskDescription: RawUTF8 read FTaskDescription write FTaskDescription;
  end;

  TSQLVote = class(TSQLRecord)
  private
    FValue: Integer;
    FAuthor: RawUTF8;
  published
    property Value: Integer read FValue write FValue;
    property Author: RawUTF8 read FAuthor write FAuthor;
  end;

  TSQLTaskVoteList = class(TSQLRecordMany)
  private
    FSource: TSQLTask;
    FDest: TSQLVote;
  published
    property Source: TSQLTask read FSource;
    property Dest: TSQLVote read FDest;
  end;

and I'm using this code to get the all tasks (TSQLTask) and for each I would like to fetch the sum of the votes (TSQLVote in relationship table TSQLTaskVoteList).
The following works though but I feel like there will be an easier way to do it (without storing the vote sum as a standalone field in the TSQLTask table).

procedure TMainForm.Button1Click(Sender: TObject);
var
  Task: TSQLTask;
  SQLTable: TSQLTable;
begin
  Task := TSQLTask.CreateAndFillPrepare(FDatabase, '');
  try
    while Task.FillOne do
    begin
      // fill in the TVirtualStringTree with Task data here

      // but I cannot find any function to get the SUM of the
      // votes for the current task, so I've used this code
      SQLTable := FDatabase.ExecuteList([TSQLVote, TSQLTaskVoteList],
        'SELECT SUM(Value) FROM Vote WHERE Vote.ID IN ' +
        '(SELECT ID FROM TaskVoteList WHERE Source = ' + Int32ToUtf8(Task.ID) + ')');
      try
        // and add the count of the votes from the fetched SUM table row
        CurrentTaskVoteSum := SQLTable.GetAsInteger(1, 0);
      finally
        SQLTable.Free;
      end;
    end;

  finally
    Task.Free;
  end;
end;

1. Is there an easier way to get the SUM of the field values from the table through the 1:N relationship table ?
2. Is the TSQLRestClientURI.ExecuteList right way to execute the SQL queries like this ? I'm using local file database.

Thanks a lot

Last edited by Martin.Reiner (2012-01-24 11:19:48)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,667

Website

Re: Getting SUM of certain values from 1:N table

1. Is there an easier way to get the SUM of the field values from the table through the 1:N relationship table ?

From the SQL point of view, I think you may also use:

select SUM(Vote.Value) from Vote, TaskVoteList where Vote.ID=TaskVoteList.ID and TaskVoteList.Source=?

But I'm no expert here - still a bit confused about how you want to use your data.

2. Is the TSQLRestClientURI.ExecuteList right way to execute the SQL queries like this ? I'm using local file database.

Using a TSQLTable is not mandatory.
You can try to use

 TSQLRestClient.ListFmt([TSQLVote, TSQLTaskVoteList],
  'SUM(Vote.Value)','Vote.ID=TaskVoteList.ID and TaskVoteList.Source=?',[],[Task.ID]);

But this is just a wrapper around ExecuteList method.

In all case, you should better use parameters (via ? in the SQL) to use prepared requests, which are much faster than a manual use of Int32ToUtf8(), and less confusing for strings (it will quote it as expected).