String representation instead of a foreign key.
lazarus / 16-Databases

----------------------------------------------------------------------
@VladislavL · 2023-02-18 22:29

Hi everyone. I'm a newbie, so don't beat me too hard, please.
I followed MasterDetail example from
https://wiki.freepascal.org/MasterDetail#Countries_and_cities_example.
Example consist of two tables: "countries" - master and "cities" -
details.
Cities contains foreign key "country_id" and TDBGrid of "cities"
displays foreign key. I'd like to know how to display textual
representation of country - I mean how to show country_name instead of
country_id and don't lose foreign key linked with TDBGrid cell, at the
same time? I suppose TDBGrid cell must contain foreign key and textual
representation simultaneously.

----------------------------------------------------------------------
paweld · 2023-02-19 06:56

You don't need to change the table and key structures. In SQL, the
SELECT statement is used to get data, and you need to modify it just
for the details(TSQLQuery), instead of: 
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
*FROM citiesWHERE cities.COUNTRY_ID = :COUNTRY_ID
set: 
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
m.CITY_ID, m.CITY_NAME, k.COUNTRY_NAMEFROM cities m INNER JOIN
countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE m.COUNTRY_ID =
:COUNTRY_ID

----------------------------------------------------------------------
@VladislavL · 2023-02-19 12:10

Quote from: paweld on February 19, 2023, 07:56:11 amYou don't need to
change the table and key structures. In SQL, the SELECT statement is
used to get data, and you need to modify it just for the
details(TSQLQuery), instead of: 
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
*FROM citiesWHERE cities.COUNTRY_ID = :COUNTRY_ID
set: 
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
m.CITY_ID, m.CITY_NAME, k.COUNTRY_NAMEFROM cities m INNER JOIN
countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE m.COUNTRY_ID =
:COUNTRY_IDThank you for reply, but it is not a solution. Your
solution just show textual representation from related table, but
foreign key was lost. For example if I need to implement CRUD
functionality, I need foreign key too. User should see text, but I
need foreign key for INSERT operation for example.

----------------------------------------------------------------------
paweld · 2023-02-19 13:11

change the query as follows:Code: SQL  [Select][+][-]window.onload =
function(){var x1 = document.getElementById("main_content_section");
if (x1) { var x = document.getElementsByClassName("geshi");for (var i
= 0; i SELECT m.CITY_ID, m.CITY_NAME, m.COUNTRY_ID, k.COUNTRY_NAMEFROM
cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE
m.COUNTRY_ID = :COUNTRY_ID 
you can also get the COUNTRY_ID from the master query

----------------------------------------------------------------------
@VladislavL · 2023-02-20 03:58

Quote from: paweld on February 19, 2023, 02:11:39 pmchange the query
as follows:Code: SQL  [Select][+][-]window.onload = function(){var x1
= document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
m.CITY_ID, m.CITY_NAME, m.COUNTRY_ID, k.COUNTRY_NAMEFROM cities m
INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)WHERE
m.COUNTRY_ID = :COUNTRY_ID 
you can also get the COUNTRY_ID from the master query
Thank you, but I need to be more clear. My problem is not with SQL and
retrieving data from a database. My problem is with TDBGrid. Let me
simplify the question. Let's remove master table from UI and keep only
cities. When a user added a new row to cities, he should choose
country, from combobox or from custom dialog, it's not matter.
country_name I need to display for a user in a list of countries, I
cannot allow user to choose country by country_id, so I need
country_name. But when I insert a new row to database table (in INSERT
statement - insert into cities(city_id, city_name, country_id)
values...), I need country_id, not country_name. My question is: how
to do this? It seems to me every cell in country TDBGrid-column should
know not only country_name but also country_id (foreign key for
countries).

----------------------------------------------------------------------
Zvoni · 2023-02-20 08:03

Quote from: @VladislavL on February 20, 2023, 04:58:44 amIt seems to
me every cell in country TDBGrid-column should know not only
country_name but also country_id (foreign key for countries).

Wrong.
Your Grid is a textual representation of your Data, and paweld showed
you how to display Country-Name instead of CountryID in your Cities-
Grid (CountryID being the ForeignKey in Cities)
When you ADD a new City, its ForeignKey is unknown at that moment.
What you need is the Primary-Key of your Country-Table to be available
when the user selects a Country from your Combo-Box (at a guess stored
in the Objects-List)

THIS IS AIRCODE
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i parmCityNam
e:=txtCityName.Text;parmCountryID:=Integer(ComboCountries.Items[ComboC
ountries.ItemIndex].Objects[ComboCountries.ItemIndex]);SQL:='INSERT
INTO cities("CityName", "CountryID") VALUES(:parmCityName,
:parmCountryID)';//Execute 
Another approach might be to use a "real" Primary-/Foreign Key
combination instead of a surrogate one. I'll let you guess what field
that could be

----------------------------------------------------------------------
@VladislavL · 2023-02-20 08:29

Quote from: Zvoni on February 20, 2023, 09:03:10 amQuote from:
@VladislavL on February 20, 2023, 04:58:44 amIt seems to me every cell
in country TDBGrid-column should know not only country_name but also
country_id (foreign key for countries).

Wrong.
Your Grid is a textual representation of your Data, and paweld showed
you how to display Country-Name instead of CountryID in your Cities-
Grid (CountryID being the ForeignKey in Cities)
When you ADD a new City, its ForeignKey is unknown at that moment.
What you need is the Primary-Key of your Country-Table to be available
when the user selects a Country from your Combo-Box (at a guess stored
in the Objects-List)

THIS IS AIRCODE
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i parmCityNam
e:=txtCityName.Text;parmCountryID:=Integer(ComboCountries.Items[ComboC
ountries.ItemIndex].Objects[ComboCountries.ItemIndex]);SQL:='INSERT
INTO cities("CityName", "CountryID") VALUES(:parmCityName,
:parmCountryID)';//Execute 
Another approach might be to use a "real" Primary-/Foreign Key
combination instead of a surrogate one. I'll let you guess what field
that could be
Thank you for clarification.

----------------------------------------------------------------------
Nicole · 2023-03-04 10:26

... or have 2 fields:
The foreign-key hidden or far to the right, the "beautiful" cell shown

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      if
(Column.FieldName='id') then begin          
i:=DataSource1.DataSet.FindField('id').AsInteger;         
s_cell:=IntToStrF(i);       end;  

Both I did in the DBGrid DrawColumnCell event,
which must have a
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Canvas.TextOut(Rect.Left + 2, Rect.Top + 1,s_cell);  to make the text
visible

If you do not know, how this event works exactly, please post again.

----------------------------------------------------------------------
@VladislavL · 2023-03-16 14:14

Quote from: Nicole on March 04, 2023, 11:26:35 am... or have 2 fields:
The foreign-key hidden or far to the right, the "beautiful" cell shown

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i      if
(Column.FieldName='id') then begin          
i:=DataSource1.DataSet.FindField('id').AsInteger;         
s_cell:=IntToStrF(i);       end;  

Both I did in the DBGrid DrawColumnCell event,
which must have a
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i
Canvas.TextOut(Rect.Left + 2, Rect.Top + 1,s_cell);  to make the text
visible

If you do not know, how this event works exactly, please post again.
Thank you Nicole, I've already done exactly like you advise.