mORMot Open Source

corchi72

Member

Registered: 2010-12-10

Posts: 232

I want to write a query with the parameters but I don't know to do

How to convert this Query in a format string (fSQLWhere) for to create a TSQLQvw.CreateAndFillPrepare(ClientDB, fSQLWhere) correctly.
I have to get a resultset of TSQLQvw

 TSQLGroupRoles = class(TSQLRecordMany)
  private
    fSource: TSQLGruppo;
    fDest: TSQLRole;
  published
    property Source: TSQLGruppo read fSource;
    property Dest: TSQLRole read fDest;
  end;

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

  end;

  TSQLRoleQvws = class(TSQLRecordMany)
  private
    fSource: TSQLRole;
    fDest: TSQLQvw;
  published
    property Source: TSQLRole read fSource;
    property Dest: TSQLQvw read fDest;
  end;

  TSQLGroupQvws = class(TSQLRecordMany)
  private
    fSource: TSQLGruppo;
    fDest: TSQLQvw;
  published
    property Source: TSQLGruppo read fSource;
    property Dest: TSQLQvw read fDest;
  end;

 TSQLQvw = class(TSQLFile)
  private
...
 published

    property Users: TSQLUserQvws read fUsers;
    property Groups: TSQLGroupQvws read fGroups;
    property Roles: TSQLRoleQvws read fRoles;
end;

function TSQLUser.LoadQvws(ClientDB: TSQLRestClientUri): TSQLQvw;
var
fIdsQvw,fIdsGroup,fIdsRole:TIntegerDynArray;
begin

  fQvws.DestGet(ClientDB, self.ID, fIdsQvw);
  fGroups.DestGet(ClientDB, self.ID, fIdsGroup);
  fRoles.DestGet(ClientDB, self.ID, fIdsRole);

result := TSQLQvw.CreateAndFillPrepare(ClientDB,
"
SELECT Qvw.ID,Name,Description FROM Qvw
left join GroupQvws
on GroupQvws.Dest= Qvw.ID
left join RoleQvws
on RoleQvws.Dest= Qvw.ID
WHERE GroupQvws.Source IN (2 (=> fIdsGroup) ) or RoleQvws.Source IN (1,2 (=> fIdsRole) ) or Qvw.ID IN (30,33  (=> fIdsQvw))
"
I want to write this query with the parameters but I don't know to do it:
SELECT %.ID,Name FROM %
left join %
on %.Dest= %.ID
left join %
on %.Dest= %.ID
%.Source IN (?) or %.Source IN (?) or %.ID IN (?), not with the values

end;

Thanks corchi

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: I want to write a query with the parameters but I don't know to do

TSQLQvw.CreateAndFillPrepare will fill TSQLQvw published fields from the supplied request.

So TSQLQvw published fields shall match the SQL request returned columns.

This is not the case in your request. So I think it won't work as expected.

corchi72

Member

Registered: 2010-12-10

Posts: 232

Re: I want to write a query with the parameters but I don't know to do

I developed two solutions and the second is faster than a second, but I also decided to include the first solution in my program because it is in line with your framework, and is more understandable and maintainable.

1) solution:

function TSQLUser.LoadQvws(ClientDB: TSQLRestClientUri): TSQLQvw;
var
 fIds,fIds4: TIntegerDynArray;
 fIdsUser,fIdsGroup,fIdsRole,fIdsGroupRoles:TIntegerDynArray;
 i,j:Integer;

 AGroupQvws: TSQLGroupQvws;
 ARoleQvws : TSQLRoleQvws;
begin
  result := nil;
  SetLength(fIds4,0);


 fGroups.DestGet(ClientDB, self.ID, fIdsGroup);
  AGroupQvws := TSQLGroupQvws.CreateAndFillPrepare(ClientDB,IntegerDynArrayToCSV(fIdsGroup,length(fIdsGroup),'Source IN (',')'));
  while AGroupQvws.FillOne do
     AddInteger(fIds4,AGroupQvws.Dest.ID ,true);
  AGroupQvws.FillClose;

  fRoles.DestGet(ClientDB, self.ID, fIdsRole);
  fIdsGroupRoles:=ReadGroupRoles(ClientDB,fIdsGroup);
  AddSortedIntegerArray(fIdsGroupRoles,fIdsRole);
  ARoleQvws := TSQLRoleQvws.CreateAndFillPrepare(ClientDB,IntegerDynArrayToCSV(fIdsRole,length(fIdsRole),'Source IN (',')'));
  while ARoleQvws.FillOne do
     AddInteger(fIds4,ARoleQvws.Dest.ID ,true);
  ARoleQvws.FillClose;

  fQvws.DestGet(ClientDB, self.ID, fIds);
  for I := 0 to Length(fIds) - 1 do
  begin
     AddInteger(fIds4,fIds[i] ,true);
  end;


  if Length(fIds4)>0 then
  begin
     SetLength(fIds,Length(fIds4));
     CopyAndSortInteger(Pointer(fIds4),Length(fIds4),fIds);
     result := TSQLQvw.CreateAndFillPrepare(ClientDB, fIds);
  end;


end;

2) solution:

function TSQLUser.LoadQvws(ClientDB: TSQLRestClientUri): TSQLQvw;
var
 fIds,fIds4: TIntegerDynArray;
 fIdsUser,fIdsGroup,fIdsRole,fIdsGroupRoles:TIntegerDynArray;
 i,j:Integer;

 fSQLSelect: RawUTF8;
 fSQLWhereGroups: RawUTF8;
 fSQLLeftJoinGroups: RawUTF8;
 fSQLWhereRoles: RawUTF8;
 fSQLLeftJoinRoles: RawUTF8;
 fSQLWhereUser: RawUTF8;
 fSQLLeftJoinUser: RawUTF8;

 fSQLWhere: RawUTF8;
 fSQLLeftJoin: RawUTF8;
 aTable: TSQLTable;

begin 
  fIdsGroup := ReadGroups(ClientDB);
  if Length(fIdsGroup)>0 then
  begin
    fSQLLeftJoinGroups :=  format('left join %s on %s.Dest= %s.ID',[TSQLGroupQvws.SQLTableName,TSQLGroupQvws.SQLTableName,TSQLQvw.SQLTableName]);
    fSQLWhereGroups := format('%s.%s',[TSQLGroupQvws.SQLTableName,IntegerDynArrayToCSV(fIdsGroup,length(fIdsGroup),'Source IN (',')')]);
    fSQLWhere := fSQLWhereGroups;
    fSQLLeftJoin := fSQLLeftJoinGroups;
  end;
  fIdsRole := ReadRoles(ClientDB);
  fIdsGroupRoles:=ReadGroupRoles(ClientDB,fIdsGroup);
  AddSortedIntegerArray(fIdsGroupRoles,fIdsRole);
  if Length(fIdsRole)>0 then
  begin
    fSQLLeftJoinRoles :=  format('left join %s on %s.Dest= %s.ID',[TSQLRoleQvws.SQLTableName,TSQLRoleQvws.SQLTableName,TSQLQvw.SQLTableName]);
    fSQLWhereRoles  := format('%s.%s',[TSQLRoleQvws.SQLTableName,IntegerDynArrayToCSV(fIdsRole,length(fIdsRole),'Source IN (',')')]);
    if fSQLWhere<>'' then  fSQLWhere := fSQLWhere + ' or ';
    fSQLWhere := fSQLWhere +  fSQLWhereRoles;
    if fSQLLeftJoin<>'' then fSQLLeftJoin := fSQLLeftJoin + ' ';
    fSQLLeftJoin := fSQLLeftJoin +fSQLLeftJoinRoles;
  end;

  fQvws.DestGet(ClientDB, self.ID, fIds);
  if Length(fIds)>0 then
  begin
    fSQLLeftJoinUser :=  format('left join %s on %s.Dest= %s.ID',[TSQLUserQvws.SQLTableName,TSQLUserQvws.SQLTableName,TSQLQvw.SQLTableName]);
    fSQLWhereUser  := format('%s.Source = %d',[TSQLUserQvws.SQLTableName,self.ID]);
    if fSQLWhere<>'' then  fSQLWhere := fSQLWhere + ' or ';
    fSQLWhere := fSQLWhere +  fSQLWhereUser;
    if fSQLLeftJoin<>'' then fSQLLeftJoin := fSQLLeftJoin + ' ';
    fSQLLeftJoin := fSQLLeftJoin +fSQLLeftJoinUser;
  end;

  fSQLSelect := format('Select %s from %s %s where %s group by %s.ID', [TSQLQvw.RecordProps.SQLTableSimpleFields[true,true],TSQLQvw.SQLTableName,fSQLLeftJoin,fSQLWhere,TSQLQvw.SQLTableName]);
  aTable := ClientDB.ExecuteList([TSQLQvw],fSQLSelect);
  Result := TSQLQvw.Create;
  if aTable = nil then exit;
  Result.FillPrepare(aTable);

This function is used in both solutions

function TSQLUser.ReadGroupRoles(ClientDB: TSQLRestClientUri;fIdsGroup:TIntegerDynArray):TIntegerDynArray;
var
  fIdsRole:TIntegerDynArray;
  AGruppo:TSQLGruppo;
  AGroupRoles:TSQLGroupRoles;
begin
  SetLength(fIdsRole,0);

  AGroupRoles := TSQLGroupRoles.CreateAndFillPrepare(ClientDB,fIdsGroup);
  while AGroupRoles.FillOne do
     AddInteger(fIdsRole,AGroupRoles.Dest.ID,false);

  result := fIdsRole
end;

Thanks corchi