Using subqueries and aggregate functions in ORM

larand54 · 2019-03-30 12:34:26

I'm new to mORMot and trying to learn.
In this case I have a problem finding the oldest record (birthDate) in table Baby.
I reused the example from the documentation to test ORM.

What I want to do is execute the following SQL-code

BirthDate = (SELECT MIN(BirthDate) FROM Baby)

This is how I solved it:

  Baby.Free;
  Baby := TSQLBaby.CreateAndFillPrepare( aClient, 'BirthDate = (SELECT MIN(BirthDate) FROM Baby)',[]);
  while Baby.FillOne do
  begin
    Memo1.lines.Add(Format('%s - %s ',[Baby.Name, Baby.Address]));
  end;

As you can see I have the "Bounds"-parameter empty[] because I couldn't make it work so I had to put the complete SQL in the "FormatSQLWhere"-parameter.
Is there any other aproach that you recommend to be used?

edwinsn · 2019-03-31 09:14:28

I think your code is logical and OK.

ab · 2019-03-31 17:23:29

Just try to use 'Min(BirthDate)' as the FieldName parameter, and use plain Retrieve for one record.

larand54 · 2019-04-04 21:39:50

I have tried your suggestion but failed, I've probably not understand what you mean so I need more help.

Note that I use Lazarus-2.1 and fpc-3.3.1

I've tried hard to find out how I should use the retrieve-function but totaly failed.

  if aClient.Retrieve('BirthDate = ?',[],['Select min(BirthDate)'],Baby,'') then

and even this will not work,

  if aClient.Retrieve('BirthDate = ?',[],['2019-01-01'],Baby) then

I get access violation in mormot.pas at the last line in the dump below:

function TSQLModel.GetTableIndex(aTable: TSQLRecordClass): integer;
var i: PtrInt;
    Props: TSQLRecordProperties;
    c: PSQLRecordClass;
begin
  if (self<>nil) and (aTable<>nil) then begin
    Props := PPointer(PtrInt(aTable)+vmtAutoTable)^;
    if (Props<>nil) and (Props.fModelMax<fTablesMax) then

Last edited by larand54 (2019-04-04 21:49:50)

larand54 · 2019-04-07 17:09:37

Finally I found why I got the Access violation. That's because I didn't created the Baby object first.
I needed to do like this:

  if aClient.Retrieve(ID,Baby) then
    Memo1.lines.Add(Format('WWWW %s - %s ',[Baby.Name, Baby.Address])+' // '+FormatDateTime('yyyy-mm-dd', Baby.BirthDate));

But I still don't know how I should do to use "Retrieve" to get the post with oldest date.
Of course I can do like this:

  if aClient.Retrieve('BirthDate = (SELECT MIN(BirthDate) FROM Baby)',Baby) then
    Memo1.lines.Add(Format('WWWW %s - %s ',[Baby.Name, Baby.Address])+' // '+FormatDateTime('yyyy-mm-dd', Baby.BirthDate));

But as I understod from ab, this is not the best way to do it... am I wright/wrong?

ab · 2019-04-08 06:41:43

Did you try something like:

if aClient.Retrieve('', Baby, 'min(Birthdate)') then

(as I wrote above https://synopse.info/forum/viewtopic.ph … 308#p29308 )

larand54 · 2019-04-08 12:16:40

Well I did got a hit but the Baby object was empty in return so all strings was empty and the date was '1899-12-30'.

I did like this:

  Baby := TSQLBaby.Create;
  if aClient.Retrieve('', Baby, 'min(Birthdate)') then

I feel that I'm missing something but what?

larand54 · 2019-04-08 17:51:59

I thought I made it work but it was only an coincidence:

  Baby := TSQLBaby.Create;
  if aClient.Retrieve('', Baby, 'min(BirthDate)') then
    Memo1.lines.Add(Format('zzzz %s - %s ',[Baby.Name, Baby.Address])+' // '+FormatDateTime('yyyy-mm-dd', Baby.BirthDate));  <<-- Gives an empty Baby
  if aClient.Retrieve('', Baby, '') then
    Memo1.lines.Add(Format('yyyyy %s - %s ',[Baby.Name, Baby.Address])+' // '+FormatDateTime('yyyy-mm-dd', Baby.BirthDate)); <<-- Gives the first record created (it happened to be the one I searched for)

I'm sure I do something wrong but after I have tested so many different things... it looks impossible to make it work.

Last edited by larand54 (2019-04-08 18:30:59)

mORMot Open Source

#1 2019-03-30 12:34:26

Using subqueries and aggregate functions in ORM

#2 2019-03-31 09:14:28

Re: Using subqueries and aggregate functions in ORM

#3 2019-03-31 17:23:29

Re: Using subqueries and aggregate functions in ORM

#4 2019-04-04 21:39:50

Re: Using subqueries and aggregate functions in ORM

#5 2019-04-07 17:09:37

Re: Using subqueries and aggregate functions in ORM

#6 2019-04-08 06:41:43

Re: Using subqueries and aggregate functions in ORM

#7 2019-04-08 12:16:40

Re: Using subqueries and aggregate functions in ORM

#8 2019-04-08 17:51:59

Re: Using subqueries and aggregate functions in ORM

Board footer