mORMot Open Source

corchi72

Member

Registered: 2010-12-10

Posts: 232

find record by property "name" and ecc...

I have created 2 classes and the relationship "one to many"  between the two classes.

  TSQLQvwFields = class(TSQLRecordMany)
  private
    fSource: TSQLQvw;
    fDest: TSQLField;
  published
    property Source:  TSQLQvw read fSource;
    property Dest: TSQLField read fDest;

  end;

  TSQLQvw = class(TSQLFile)
  private
  ..
    fFields: TSQLQvwFields;

end;

TSQLField = class(TSQLFile)
  private
    fOwner: RawUTF8;
    fEnabled: boolean;
  public

  published
    property Name;
    property Created;
    property Modified;
    property KeyWords;
    property SignatureTime;
    property Signature;

    property Owner: RawUTF8 read fOwner write fOwner;
    property Enabled: boolean read fEnabled write fEnabled;
  end;

Then I have created a function that exposes only the child records in the table SQLQvw

{ TSQLField }
function LoadFieldForQvw(ADatabase: TSQLRestClient;const AQvw: TSQLQvw):TSQLField;
var
 fIds: TIntegerDynArray;
begin
  result := nil;
  AQvw.Fields.DestGet(ADatabase, AQvw.ID, fIds);
  if Length(fIds)>0 then
  begin
    result := TSQLField.CreateAndFillPrepare(ADatabase, fIds);
  end;
end;

now I need to do a search in the range of data that I have created, what should I write?

//Example

procedure TFrmEditFileQvw.ActFieldsExecute(Sender: TObject);
var
 AField: TSQLField;
 List:TStringlist;
 i:Integer;
begin
  try
  
     List:=TStringlist.Create;
     List.Add('A001');
     List.Add('B001');

     AField:= LoadFieldForQvw( currentDatabase ,Rec);
     if Assigned(AField) then
     begin
        for i := 0 to List.Count -1 do
          if AField = List[i]  then // here  what should I write??????????
              Showmessage('I found it:...');


     end;
  finally
     FreeAndnil(List);
    
  end;

end;

Thanks

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: find record by property "name" and ecc...

The documentation says that:

    {{ this constructor initializes the object as above, and prepare itself to
      loop through a statement using a specified WHERE clause
      - this method creates a TSQLTableJSON, retrieves all records corresponding
        to the WHERE clause, then call FillPrepare - previous Create(aClient)
        methods retrieve only one record, this one more multiple rows
      - you should then loop for all rows using 'while Rec.FillOne do ...'
      - the TSQLTableJSON will be freed by TSQLRecord.Destroy
      - the WHERE clause should use inlined parameters (like 'Name=:('Arnaud'):')
      for better server speed }
    constructor CreateAndFillPrepare(aClient: TSQLRest; const aSQLWhere: RawUTF8); overload;

So you just have to use

while AField.FillOne do
  for i := 0 to List.Count-1 do
    if AField.Name=List[i] then
    begin
      // match
      break;
    end;

But this is definitively a complicated way of searching data.

You should better use a search for the name with a WHERE clause, if your name list is not big: 'Name IN ("A001","B001")' so all the search will be done by SQLite, and not in client application.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: find record by property "name" and ecc...

I'm sorry but maybe I was not clear, I have already run the above command
"TSQLField.CreateAndFillPrepar (ADatabase, FIDS), and now I just wanted to do a search on the list already exists, but if I understand it is not possible, I have to scroll all the records with "Fillone" to compare data.

var
 fIds: TIntegerDynArray;
begin
  result := nil;
  AQvw.Fields.DestGet(ADatabase, AQvw.ID, fIds);
  if Length(fIds)>0 then
  begin
    result := TSQLField.CreateAndFillPrepare(ADatabase, fIds);

I come to the point:

I am seeing a list of codes in a grid and I wanted to do a lookup table with TSQLField (daughter of TSQLQvw), also in the grid to display the description for.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,240

Website

Re: find record by property "name" and ecc...

You should better create a custom request returning a TSQLTableJSON, with a join of multiple records, in order to get directly the description of your Field table items.

It will be faster to do this request on the Server, whereas on the client.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: find record by property "name" and ecc...

There are examples with TSQLTableJSON?

Thanks