09. Foreign fields in tables

One of the most interesting features in the Adminer Editor is the links to referenced records. When listing data in a table, instead of the values of a foreign key field, Adminer fetches and displays values from the first string-like column in the referenced table.

This feature is available as a toggleable option in Jaxon DbAdmin.
When a table contains one or more foreign keys, a toggle button is showed in the Select page, allowing the user to load the values from the referenced records. Deactivating the toggle button shows back the foreign field values.

Jaxon DbAdmin takes the feature even further by allowing the user, in the foreigns.php config file, to customize the fields that are fetched from the referenced tables.

Below is an example of a foreigns.php file for the well-known Sakila database, for PostgreSQL and MySQL. Note that the PostgreSQL options define an additional level for the schema name.

// The SQL SELECT clauses to get labels for foreign key columns.
return [
    'dbadmin-pgsql' => [
        'sakila' => [ // Database name
            'public' => [ // Schema name
                'actor' => [ // Table name
                    'actor_id' => [ // Foreign key
                        'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
                        'search' => fn(string $search) => "\"first_name\" ILIKE $search OR \"last_name\" ILIKE $search",
                    ],
                ],
                'store' => [
                    'store_id' => [
                        'select' => fn(int $textLength) => "SUBSTR(address.address, 1, $textLength)",
                        'search' => fn(string $search) => "address.address ILIKE $search",
                        'joins' => ["INNER JOIN address ON store.address_id=address.address_id"],
                    ],
                ],
            ],
        ],
    ],
    'dbadmin-mysql' => [
        'sakila' => [ // Database name
            'actor' => [ // Table name
                'actor_id' => [ // Foreign key
                    'select' => fn(int $textLength) => "SUBSTR(CONCAT(first_name, ' ', last_name), 1, $textLength)",
                    'search' => fn(string $search) => "LOWER(first_name) LIKE $search OR LOWER(last_name) LIKE $search",
                ],
            ],
            'store' => [
                'store_id' => [
                    'select' => fn(int $textLength) => "SUBSTR(address.address, 1, $textLength)",
                    'search' => fn(string $search) => "LOWER(address.address) LIKE $search",
                    'joins' => ["INNER JOIN address ON store.address_id=address.address_id"],
                ],
            ],
        ],
    ],
];

For a foreign key in a given table, the select entry defines the clause for the SQL SELECT query that will return the referenced record, while the search entry defines the clause for the SQL SELECT query that will search for a given value in the referenced table.
The latter is useful when editing foreign key values in a table.

Two specific cases are highlighted here: the first is when the displayed value is computed from multiple columns, and the second is when it is read from a third table; hence the joins entry.