Jump to content


Photo

Ideas to edit joined SELECT?


  • Please log in to reply
3 replies to this topic

#1 littlebigfred

littlebigfred
  • Members
  • 176 posts

Posted 09 September 2009 - 01:40 AM

Hello

Before I go ahead and write some code... I was wondering how you guys manage editing data that were SELECTed from multiple tables, ie. joined, and displayed in a grid:

CODE
SELECT article.id, type.label FROM article,type WHERE article.type_id = type.id


In case we need to let the user edit any column from any of the tables that ended up in the SELECT, I was thinking of displaying all the columns in a vertical grid as "key=value", and display each section in a different color to make it obvious that we are editing data that live in different tables (here's a screenshot):

CODE
With NextGrid1 do begin
  //Create three columns, with first column hidden
  Columns.Add(TNxTextColumn,'SQL');
  Columns[0].Name := 'sql';
  Columns[0].Visible := False;
  Columns[0].Options := Columns[0].Options - [coCanSort];
  Columns[0].Options := Columns[0].Options + [coFixedSize];

  Columns.Add(TNxTextColumn,'Key');
  Columns[1].Name := 'key';
  Columns[1].Enabled := False;
  Columns[1].Color := clBtnFace;
  Columns[1].Options := Columns[1].Options - [coCanSort];
  Columns[1].Options := Columns[1].Options + [coFixedSize];

  Columns.Add(TNxTextColumn,'Value');
  Columns[2].Name := 'value';
  Columns[2].Options := Columns[2].Options - [coCanSort];
  Columns[2].Options := Columns[2].Options + [coFixedSize];

  FixedCols := 1;

  Columns[Columns.Count - 1].Options := Columns[Columns.Count - 1].Options + [coAutoSize];

  //Add rows, with the first group of rows = table1, and second group = table2
  AddRow(10);
  
  //SELECT article.id, type.label FROM article,type WHERE article.type_id = type.id
  CellsByName['sql',0] := 'article.id';
  CellsByName['key',0] := 'Article ID';
  CellsByName['value',0] := '123';
  //How to set color of cell using the column name?
  Cell[2,0].Color := clMoneyGreen;

  CellsByName['sql',1] := 'article.price';
  CellsByName['key',1] := 'Price';
  CellsByName['value',1] := '12';
  Cell[2,1].Color := clMoneyGreen;

  //Background color back to normal
  CellsByName['sql',2] := 'type.id';
  CellsByName['key',2] := 'Type ID';
  CellsByName['value',2] := '456';

  CellsByName['sql',3] := 'type.label';
  CellsByName['key',3] := 'Label';
  CellsByName['value',3] := 'Coat';

  for index := 0 to Columns.Count - 1 do begin
    BestFitColumn(index, bfBoth);
  end;
  Columns.ResizeColumns();
end;


What would be the best way to pull this trick? Is there an easy way to select a group of rows and change their background color in one go?

Thank you.

#2 littlebigfred

littlebigfred
  • Members
  • 176 posts

Posted 22 September 2009 - 08:07 PM

In case rows from a joined SELECT are displayed horizontally as usual, I haven't found a good solution to find a column's parent ID so that I can build the UPDATE query.

For instance:
1. Fill a grid with the following query: SELECT article_id, article_type_code, type_id, type_label FROM article,type WHERE article_type_code=type_id
2. In the grid, hide columns "article_id" and "type_id" since those are useless to the user, and are only needed to perform updates to the database
3. In the grid's AfterEdit event, find the table name and row ID the cell belongs to, and send query. For example, if the user has changed the content of the type_label column:

CODE
With NextGrid1 do begin
    query := Format('UPDATE how_to_find_table SET type_label="%s" WHERE how_to_find_row_ID=%s',[Cell[ACol,ARow],Cell[How_to_find_row_id]]);


Has someone come up with a good solution for this issue?

Thank you.

#3 littlebigfred

littlebigfred
  • Members
  • 176 posts

Posted 24 September 2009 - 02:36 PM

For those interested, I came up with this solution: In case of a joined SELECT...
  1. include all the columns of all the tables involved
  2. make all columns read-only; if the user might need to edit some columns, make those editable
  3. hide the ID columns (eg. T1_id, T2_id), unless the user has a need for this information. Those are needed only to build the UPDATE query in the AfterEdit event
  4. for each column, use its Tag property: If set to nil, it's an "ID column" (ie. contains that part of the row's unique ID), if set to anything else, it's a "non-ID column" (ie. data linked to the parent ID) and the Tag contains the column index of its ID parent


For instance:
CODE
ColumnByName['T1_id'].Tag := nil;
ColumnByName['T1_label'].Tag := ColumnByName['T1_id'].index;
ColumnByName['T2_id'].Tag := nil;
ColumnByName['T2_somecolumn'].Tag := ColumnByName['T2_id'].index;


I'd rather that NextGrid provide a ParentIndex property instead of using the vague Tag property, but it does the job.

HTH,

#4 Boki (Berg)

Boki (Berg)

    Boki (Berg)

  • Forum Admin
  • PipPipPipPipPip
  • 8,191 posts
  • Gender:Male

Posted 26 September 2009 - 11:34 PM

Hello Fred,

I have no comments on code, but I hope that your solution will help someone.

Best regards
boki@bergsoft.net | LinkedIn Profile
--
BergSoft Home Page: www.bergsoft.net
Users Section: users.bergsoft.net
Articles and Tutorials: help.bergsoft.net (Developers Network)
--
BergSoft Facebook page
--
Send us applications made with our components and we will submit them on: www.bergsoft.net/apps.htm. Link to this page will be also set on home page too.




1 user(s) are reading this topic

0 members, 1 guests, 0 anonymous users