You are not logged in.
- Topics: Active | Unanswered
#1 2014-05-07 10:14:29
- tech
- Member
- Registered: 2014-01-13
- Posts: 110
How to show joined table on the TSQLRibbonTabParameters
Hi,
I use TSQLRibbonTabParameters like in the MainDemo and I want to show some fields of the joined table instead of the ID.
I suppose that TSQLMemo has a property pointing joinedTable, is it possibile to do that :
(Table: TSQLMemo;
Select: 'Name,Created,Modified,KeyWords,SignatureTime,joinedTable.Field1,joinedTable.Field2'; Group: GROUP_CLEAR; FieldWidth: 'IddIdII'; Actions: DEF_ACTIONS)Thx,
Offline
#3 2014-05-07 10:59:37
- tech
- Member
- Registered: 2014-01-13
- Posts: 110
Re: How to show joined table on the TSQLRibbonTabParameters
Is there a way to detect the existance of a joined field in the TSQLRibbonTabParameters.Select property and then embed automaticaly the whole content ?
Why there's not a TSQLRibbonTabParameters.SQLWhere property to add a filter if needed ?
Thx,
Offline
#5 2014-05-09 16:13:30
- tech
- Member
- Registered: 2014-01-13
- Posts: 110
Re: How to show joined table on the TSQLRibbonTabParameters
Hi AB,
I added two properties to TSQLRibbonTabParameters SQLWhere and FieldAlign
unit mORMot;
....
/// defines the settings for a Tab for User Interface generation
// - used in mORMotToolBar.pas unit and TSQLModel.Create() overloaded method
TSQLRibbonTabParameters = object
public
/// the Table associated to this Tab
Table: TSQLRecordClass;
/// the caption of the Tab, to be translated on the screen
// - by default, Tab name is taken from TSQLRecord.Caption(nil) method
// - but you can override this value by setting a pointer to a resourcestring
CustomCaption: PResStringRec;
/// the hint type of the Tab, to be translated on the screen
// - by default, hint will replace all %s instance by the Tab name, as taken
// from TSQLRecord.Caption(nil) method
// - but you can override this value by setting a pointer to a resourcestring
CustomHint: PResStringRec;
/// SQL fields to be displayed on the data lists
// 'ID,' is always added at the beginning
Select: RawUTF8;
/// SQL where to apply
SQLWhere : RawUTF8; <------------------------------
/// Tab Group number (index starting at 0)
Group: integer;
/// displayed field length mean, one char per field (A=1,Z=26)
// - put lowercase character in order to center the field data
FieldWidth: RawUTF8;
/// displayed field align, one char par field (r=Right, l=Left, c=Center)
FieldAlign: RawUTF8; <------------------------------
....unit mORMotToolBar;
......
constructor Create(aOwner: TComponent; aClient: TSQLRestClientURI;
aClass: TSQLRecordClass; aGrid: TDrawGrid; aIDColumnHide: boolean;
aPager: TSynPager; aImageList32,aImageList16: TImageList;
aOnButtonClick: TSQLListerEvent; aOnValueText: TValueTextEvent;
const aGridSelect: RawUTF8= '*'; const aGridSQLWhere: RawUTF8= ''; <---------------------------------------------
aHideDisabledButtons: boolean=false;
aHeaderCheckboxSelectsInsteadOfSort: Boolean=false); reintroduce; overload;
....
Constructor TSQLLister.Create(aOwner: TComponent; aClient: TSQLRestClientURI;
aClass: TSQLRecordClass; aGrid: TDrawGrid; aIDColumnHide: boolean;
aPager: TSynPager; aImageList32,aImageList16: TImageList;
aOnButtonClick: TSQLListerEvent; aOnValueText: TValueTextEvent;
const aGridSelect: RawUTF8= '*'; const aGridSQLWhere: RawUTF8= ''; <---------------------------------------------
aHideDisabledButtons: boolean=false;
aHeaderCheckboxSelectsInsteadOfSort: Boolean=false);
var T: TSQLTable;
begin
if (aClient=nil) or (aGridSelect='') then
T := nil else
T := aClient.List([aClass],aGridSelect, aGridSQLWhere); <---------------------------------------------
Create(aOwner,aClient,aClass,aGrid,aIDColumnHide,aPager,
aImageList32,aImageList16,aOnButtonClick,aOnValueText,T,aHideDisabledButtons,
aHeaderCheckboxSelectsInsteadOfSort);
end;
.................
constructor TSQLRibbonTab.Create(ToolBar: TSynPager; Body: TSynBodyPager;
aImageList32,aImageList16: TImageList; var aPagesShortCuts: TFreeShortCut;
const aTabParameters: TSQLRibbonTabParameters;
Client: TSQLRestClientURI; aUserRights: TSQLFieldBits;
aOnValueText: TValueTextEvent; SetAction: TSQLRibbonSetActionEvent;
const ActionsTBCaptionCSV, ActionsHintCaption: string; ActionIsNotButton: pointer;
aOnActionClick: TSQLListerEvent; ViewToolbarIndex: integer;
aHideDisabledButtons, aHeaderCheckboxSelectsInsteadOfSort: boolean);
......
begin
......
Lister := TSQLLister.Create(Page,Client,Table,List,not aTabParameters.ShowID,
Toolbar,aImageList32,aImageList16,aOnActionClick,aOnValueText,
'ID,'+aTabParameters.Select,aTabParameters.SQLWhere, <---------------------------------------------
aHideDisabledButtons,aHeaderCheckboxSelectsInsteadOfSort);
......
if TableToGrid<>nil then begin
TableToGrid.SetFieldLengthMean(U,actMark in Act, aTabParameters.FieldAlign); <---------------------------------------------
......
end;unit mORMotUI;
......
procedure SetFieldLengthMean(const Lengths: RawUTF8; aMarkAllowed: boolean; const Aligns: RawUTF8='');<---------------------------------------------
......
procedure TSQLTableToGrid.SetFieldLengthMean(const Lengths: RawUTF8; aMarkAllowed: boolean; const Aligns: RawUTF8='');
var L, i: integer;
c: AnsiChar;
Means: array of cardinal;
a: AnsiChar;<---------------------------------------------
begin
if self=nil then Exit;
fMarkAllowed := aMarkAllowed;
L := length(Lengths);
if L=0 then begin
SetLength(Means,Table.FieldCount);
for i := 0 to Table.FieldCount-1 do
Means[i] := 10; // some fixed width
end else
if Table.FieldCount=L then begin
SetLength(Means,L);
for i := 0 to L-1 do begin
c := Lengths[i+1];
if Aligns <> '' then <---------------------------------------------
begin <---------------------------------------------
a := Aligns[i+1]; <---------------------------------------------
case a of <---------------------------------------------
'l' : Aligned[i] := alLeft; <---------------------------------------------
'r' : Aligned[i] := alRight; <---------------------------------------------
'c' : Aligned[i] := alCenter; <---------------------------------------------
end; <---------------------------------------------
end <---------------------------------------------
else <---------------------------------------------
if c in ['a'..'z'] then begin
Aligned[i] := alCenter;
dec(c,32);
end;
Means[i] := ord(c)+(-ord('A')+1);
end;
Table.SetFieldLengthMean(Means);
end;
if aMarkAllowed then
Table.FieldLengthMeanIncrease(0,2); // space for Marked[] checkbox e.g.
end;But for the joined table fields I'm blocked here:
unit mORMot;
......
function TSQLRestClientURI.List(const Tables: array of TSQLRecordClass;
const SQLSelect, SQLWhere: RawUTF8): TSQLTableJSON;
var Resp, SQL: RawUTF8;
U: RawUTF8;
InternalState: cardinal;
props: TSQLModelRecordProperties; <---------------------------------------------
begin
result := nil;
if high(Tables)<0 then exit;
// GET Collection
props := Model.Props[Tables[0]]; <---------------------------------------------
SQL := props.SQL.SelectAllJoined; <---------------------------------------------
if SQL = '' then <---------------------------------------------
SQL := Model.SQLFromSelectWhere(Tables,SQLSelect,SQLWhere); <-------- SQLSelect='Name,Created,Modified,KeyWords,SignatureTime,joinedTable.Field1'
if high(Tables)=0 then begin
// one Table -> use REST protocol (SQL as parameters)
if not IsRowID(pointer(SQLSelect)) then
// ID selected by default
U := '?select='+UrlEncode(SQLSelect) else
U := '';
if SQLWhere<>'' then begin
if U<>'' then
U := U+'&where=' else
U := U+'?where=';
U := U+UrlEncode(SQLWhere);
end;
with URI(Model.URI[TSQLRecordClass(Tables[0])]+U,'GET',@Resp) do <--- I got 'no such column : JoinedTable.Field1' because the URI accept only one table
if Lo<>HTML_SUCCESS then
exit else
InternalState := Hi;
result := TSQLTableJSON.CreateFromTables([Tables[0]],SQL,Resp); // get data
end else begin
// multiple tables -> send SQL statement as HTTP body
with URI(Model.Root,'GET',@Resp,nil,@SQL) do
if Lo<>HTML_SUCCESS then
exit else
InternalState := Hi;
result := TSQLTableJSON.CreateFromTables(Tables,SQL,Resp); // get data
end;
result.fInternalState := InternalState;
end;thnx,
Offline