HOWTO - Sashipa development

Advanced listForm: two foreign keys reference the same table.

In a SQL table there are two foreign keys that reference both the same table. In a listForm you wish to display informations referenced by the two foreign keys.

For our example we have Persons, each person originates from a Country and lives in a Country (the same or not). Here is the SQL script for creating table (here with HSQL syntax):

  CREATE TABLE Country (
    Id_Country integer not null primary key,
    Country_Name varchar(100) not null
  );
  CREATE TABLE Person (
    Id_Person integer not null primary key,
    Person_Name varchar(100) not null,
    Person_FirstName varchar(100),
    Ref_Country_Originate integer,
    Ref_Country_Live integer,
    FOREIGN KEY (Ref_Country_Originate) REFERENCES Country (Id_Country),
    FOREIGN KEY (Ref_Country_Live) REFERENCES Country (Id_Country)
  );

Table Person has two foreign keys, that reference the same Country table.

In the listForm that works on the Person table, we wish to display the "originate from" country and the "live in" country. Here is an example:

  <listForm db='dbDemo' delete='yes'>
    <title>List of all persons</title>
    <bounds x='10' y='10' w='700' h='500' />

    <selectQueryBuilder type='list'>
      <fromStatementBuilder>
        <mainInstanceTable schemaTable='tblPerson' />
        <fkJoin join='left'>
          <instanceFk schemaFk='fk_tblPerson_tblCountry_RefCountryOriginate' />
          <referencedInstanceTable schemaTable='tblCountry' nickName='countryOrig' />
        </fkJoin>
        <fkJoin join='left'>
          <instanceFk schemaFk='fk_tblPerson_tblCountry_RefCountryLive' />
          <referencedInstanceTable schemaTable='tblCountry' nickName='countryLive' />
        </fkJoin>
      </fromStatementBuilder>
    </selectQueryBuilder>

    <instanceColumnList>
      <instanceColumn schemaColumn='tblPerson_PersonName' />
      <instanceColumn schemaColumn='tblPerson_PersonFirstname' />
      <!-- User key columns for originate country -->
      <instanceColumn schemaColumn='tblCountry_Name'>
        <title>Originate from</title>
        <instanceTable schemaTable='tblCountry' nickName='countryOrig' />
      </instanceColumn>
      <!-- User key columns for live in country -->
      <instanceColumn schemaColumn='tblCountry_Name'>
        <title>Live in</title>
        <instanceTable schemaTable='tblCountry' nickName='countryLive' />
      </instanceColumn>
    </instanceColumnList>
    <doubleClicScreen screen='SCtblPerson' />
    <insertScreen screen='SCtblPerson' />
  </listForm>

Remarks:

Two joins are defined in the selectQueryBuilder. Two instances of the Country table are required, we give them distinct nickName.
For each column 'tblCountry_Name' we specify an explicit title. Otherwise we'll have two identical column titles.
For each column 'tblCountry_Name' we specify from which table instance is the column.

Top