String representation instead of a foreign key.

[@VladislavL]

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]

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][+][-]

1. SELECT *
2. FROM cities
3. WHERE cities.COUNTRY_ID = :COUNTRY_ID

set: 

Code: SQL  [Select][+][-]

1. SELECT m.CITY_ID, m.CITY_NAME, k.COUNTRY_NAME
2. FROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)
3. WHERE m.COUNTRY_ID = :COUNTRY_ID

[@VladislavL]

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][+][-]

1. SELECT *
2. FROM cities
3. WHERE cities.COUNTRY_ID = :COUNTRY_ID

set: 

Code: SQL  [Select][+][-]

1. SELECT m.CITY_ID, m.CITY_NAME, k.COUNTRY_NAME
2. FROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)
3. WHERE m.COUNTRY_ID = :COUNTRY_ID

Thank 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]

change the query as follows:

Code: SQL  [Select][+][-]

1. SELECT m.CITY_ID, m.CITY_NAME, m.COUNTRY_ID, k.COUNTRY_NAME
2. FROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)
3. WHERE m.COUNTRY_ID = :COUNTRY_ID

 
you can also get the COUNTRY_ID from the master query

[@VladislavL]

change the query as follows:

Code: SQL  [Select][+][-]

1. SELECT m.CITY_ID, m.CITY_NAME, m.COUNTRY_ID, k.COUNTRY_NAME
2. FROM cities m INNER JOIN countries k ON (m.COUNTRY_ID=k.COUNTRY_ID)
3. 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]

It 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][+][-]

1. parmCityName:=txtCityName.Text;
2. parmCountryID:=Integer(ComboCountries.Items[ComboCountries.ItemIndex].Objects[ComboCountries.ItemIndex]);
3. SQL:='INSERT INTO cities("CityName", "CountryID") VALUES(:parmCityName, :parmCountryID)';
4. //Execute
5.  

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]

It 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][+][-]

1. parmCityName:=txtCityName.Text;
2. parmCountryID:=Integer(ComboCountries.Items[ComboCountries.ItemIndex].Objects[ComboCountries.ItemIndex]);
3. SQL:='INSERT INTO cities("CityName", "CountryID") VALUES(:parmCityName, :parmCountryID)';
4. //Execute
5.  

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]

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

Code: Pascal  [Select][+][-]

1.      if (Column.FieldName='id') then begin
2.           i:=DataSource1.DataSet.FindField('id').AsInteger;
3.           s_cell:=IntToStrF(i);
4.        end;  

Both I did in the DBGrid DrawColumnCell event,
which must have a

Code: Pascal  [Select][+][-]

1. 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]

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

Code: Pascal  [Select][+][-]

1.      if (Column.FieldName='id') then begin
2.           i:=DataSource1.DataSet.FindField('id').AsInteger;
3.           s_cell:=IntToStrF(i);
4.        end;  

Both I did in the DBGrid DrawColumnCell event,
which must have a

Code: Pascal  [Select][+][-]

1. 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.