#1 2015-10-28 09:42:49

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,

Offline

#2 2015-10-29 12:49:36

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?

Offline

#3 2015-10-30 10:04:56

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 303

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

Offline

#4 2015-10-30 12:22:37

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,167
Website

Re: Most simple way to count rows by criteria

Why not just use OneFieldValue?

Offline

#5 2015-10-30 12:34:45

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 smile )

Offline

#6 2015-10-30 17:16:11

danielkuettner
Member
From: Germany
Registered: 2014-08-06
Posts: 303

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.

Offline

#7 2015-10-31 00:44:03

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.

Offline

Board footer

Powered by FluxBB