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}