Lazarus

Programming => Databases => Topic started by: @VladislavL on February 18, 2023, 11:29:39 pm

Title: String representation instead of a foreign key.
Post by: @VladislavL on February 18, 2023, 11:29:39 pm
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 (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.
Title: Re: String representation instead of a foreign key.
Post by: paweld on February 19, 2023, 07:56:11 am
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
Title: Re: String representation instead of a foreign key.
Post by: @VladislavL on February 19, 2023, 01:10:34 pm
Quote from: paweld on February 19, 2023, 07:56:11 am
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.
Title: Re: String representation instead of a foreign key.
Post by: paweld on February 19, 2023, 02:11:39 pm
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
Title: Re: String representation instead of a foreign key.
Post by: @VladislavL on February 20, 2023, 04:58:44 am
Quote from: paweld on February 19, 2023, 02:11:39 pm
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).
Title: Re: String representation instead of a foreign key.
Post by: Zvoni on February 20, 2023, 09:03:10 am
Quote from: @VladislavL on February 20, 2023, 04:58:44 am
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
Title: Re: String representation instead of a foreign key.
Post by: @VladislavL on February 20, 2023, 09:29:05 am
Quote from: Zvoni on February 20, 2023, 09:03:10 am
Quote from: @VladislavL on February 20, 2023, 04:58:44 am
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.
Title: Re: String representation instead of a foreign key.
Post by: 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][+][-]
  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.
Title: Re: String representation instead of a foreign key.
Post by: @VladislavL on March 16, 2023, 03:14:50 pm
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][+][-]
  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.
TinyPortal © 2005-2018