mORMot Open Source

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Most simple way to count rows by criteria

I would like to execute simple query in form:

SELECT COUNT(*) FROM Table WHERE <some-criteria>

assuming that the Table is included into the model and the criteria will be something according to the conventions, like:

  T := TSQLTable.Create(Rest, 'Status=?',[requiredStatus]);

but I want to receive just the count of records satisfying the criteria.

Thanks,

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: Most simple way to count rows by criteria

Found workable solution (requires TSQLRestClient):

with Rest.ListFmt([TSQLTable], 'COUNT(*)', 'Status=%', [requiredStatus]), []) do 
begin
  Result := GetAsInteger(1, 0);
  Free;
end;

Anything simpler?

danielkuettner

Member

From: Germany

Registered: 2014-08-06

Posts: 357

Re: Most simple way to count rows by criteria

Perhaps another way:

t:= TSQLTable.CreateAndFillPrepare(RestDB, 'Datum_von > ?', [DateToSql(Now-30)], 'count(*) as ID');
t.FillOne;
t.ID<-- this holds the value of RecordCount

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: Most simple way to count rows by criteria

Why not just use OneFieldValue?

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: Most simple way to count rows by criteria

@danielkuettner

Thanks, that leads to:

Rest.OneFieldValue(TSQLTable, 'COUNT(*)', 'Status=?', [], [requiredStatus], Count);

And no need to destroy anything. Nice!

In fact, there is a special handling in TSQLRest.MultiFieldValue:

if (n=1) and IdemPChar(pointer(FieldName[0]),'COUNT(*)') then ...

(found with the debugger )

danielkuettner

Member

From: Germany

Registered: 2014-08-06

Posts: 357

Re: Most simple way to count rows by criteria

I've forgotten OneFieldValue...

BTW, if you want "nothing to destroy" you can use a SmartPointer-Interface:

var
  t: ISmartPointer<TSQLtablename>;
  
begin
  t:= TSmartPointer<TSQLtablename>.Create(TSQLtablename.CreateAndFillPrepare(RestDB, 'Datum_von > ?', [DateToSql(Now-30)], 'count(*) as ID'));
  t.FillOne;
  //the recordcount is now in t.ID

Although OneFieldValue is much easier to declare it returns a RawUTF8. You have to convert it with UTF8ToInteger.

alpinistbg

Member

Registered: 2014-11-12

Posts: 124

Re: Most simple way to count rows by criteria

There is an overload that returns Int64.
Besides, I don't use Delphi for daily work, just Delphi7 to check whether some mORMot bugs are FPC specific or general.
ISmartPointer<T> is not an option.