SQLGenerator

uses
  System.SysUtils,
  System.TypInfo,
  System.Rtti,
  SQLG.Table in 'SQLG.Table.pas',
  SQLG.Field in 'SQLG.Field.pas',
  SQLG.Condition in 'SQLG.Condition.pas',
  SQLG.Params in 'SQLG.Params.pas',
  SQLG.Select in 'SQLG.Select.pas',
  SQLG.CreateTable in 'SQLG.CreateTable.pas',
  SQLG.Types in 'SQLG.Types.pas';

type
  [TableName('user')]
  TUser = class(TSQLTable)
    [FieldName('id'),
    UNIQUE,
    PRIMARYKEY]
    Id: TFGUID;
    [FieldName('role_id')]
    RoleId: TFGUID;
    [FieldName('status')]
    Status: TFInteger;
    [FieldName('name')]
    Name: TFString;
  end;

  [TableName('user_role')]
  TUserRole = class(TSQLTable)
    [FieldName('id')]
    Id: TFGUID;
    [FieldName('type')]
    RoleType: TFInteger;
    [FieldName('desc')]
    Desc: TFString;
    [FieldName('name'),
    LENGTH(20)]
    Name: TFVARCHAR;
  end;

procedure Test;
begin
  var User := TUser.Create;
  var UserRole := TUserRole.Create;

  var Params: TSQLParams;
  var Sel :=
    Select([User, UserRole.Desc.Table('ur').&As('description')]).
    From(User).
    LeftJoin(
    Select('*').
    From(UserRole).Where(UserRole.RoleType = 1), 'ur').
    on(User.RoleId = UserRole.Id.Table('ur')).
    Where(not (User.Id = TGUID.NewGuid) or (User.Status in [1, 2, 3])).
    Where(User.Status and 1 = 0).
    Where(User.Name = 'Dan').
    Where(User.Status in
    Select(User.Status).From(User).Where(User.RoleId in [TGUID.NewGuid, TGUID.NewGuid])).
    OrderBy([User.Name, DESC(User.Status)]).
    GroupBy([User.Id]);

  writeln(Sel.Build(Params));
  writeln;
  for var Param in Params do
    writeln(Param.Key, ': ', Param.Value.TypeInfo.Name, ' = ', Param.ToString);

  User.Free;
  UserRole.Free;
end;

begin
  try
    Test;
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
  Readln;
end.

output

SELECT user.*, ur.desc description
 FROM user
 LEFT JOIN (
    SELECT *
     FROM user_role
     WHERE user_role.type = :p0) ur ON user.role_id = ur.id
 WHERE (NOT (user.id = :p1)) OR (user.status in (:p2, :p3, :p4)) AND user.status & 1 = :p5 AND user.name = :p6 AND user.status in (
    SELECT user.status
     FROM user
     WHERE user.role_id in (:p7, :p8))
 ORDER BY user.name, user.status DESC
 GROUP BY user.id

p0: Integer = 1
p1: TGUID = {4D8FD3C0-9972-4269-BBB6-34E3925EABE2}
p2: Integer = 1
p3: Integer = 2
p4: Integer = 3
p5: Integer = 0
p6: string = Dan
p7: TGUID = {2A37B58A-3B2F-4320-9850-A70AA70C3971}
p8: TGUID = {3FBC11F6-381F-4443-8592-1E5FC5CDF479}

GitCnSH-DSLIN/SQLGenerator

SQLGenerator