MySQL - Two foreign keys pointing to the same table

About structures using two joints to a single table

Using two joins to the same table is a perfectly logical structure in some cases, but this type of structure is often impractical in use.

Let's take a simple example:

To select records from the projects table we need to use this sort of query:

SELECT
    projects.name,
    manager.name AS manager_name,
    manager.firstname AS manager_firstname,
    employee.name AS employee_name,
    employee.firstname AS employee_firstname
FROM
    projects
    LEFT JOIN persons AS manager ON projects.manager_id = manager.id
    LEFT JOIN persons AS employee ON projects.employee_id = employee.id

This query uses aliases both on the fields of the persons table, but also for the name of the table itself as it is queried twice (once for each join).

This is not the right solution. PHPCG uses an advanced filter system that allows you to search and filter records in your lists, but it does not support the use of aliases for tables.

There are two alternative solutions.

First solution: change the structure of your database

In the example above, the persons table groups two different entities in the same table: managers and employees.

This is not a good idea. It is always better to create a table for each data type (each entity).

Second solution: edit the READ list SQL query manually

Let's take a slightly different structure:

In this second case, the projects table refers to two employees.
It would not be consistent to create two tables for employees, as employees represent a unique entity.

The solution is to edit the PHP class that creates the employees, and replace the query generated by the generator with two separate queries.

Here's how to do it:

1. Open the file admin/class/crud/Projects.php in your code editor
   (replace Projects with your table name)
2. Find the query in the code, and delete the fields and join that refer to the second join.
   The join part of the query begins with $this->join_query = ' LEFT JOIN ...' and the fields part begins with $qry_start = 'SELECT ...'
3. Find the code part where the records are registered. It begins with while (!$db->endOfSeek()) { ...
4. In this loop, remove the results that refer to the second join
5. After the loop, add a new one to loop inside all the records and get the records from the second join.
   In our example this would be:

   for ($i=0; $i < $this->records_count; $i++) {
       $qry = 'SELECT `employees`.`id` AS `employees_id`, `employees`.`name` AS `employees_name` FROM projects
       LEFT JOIN `employees` ON `projects`.`employees2_id`=`employees`.`id`
       WHERE projects.id = ' .  $this->id[$i] . ' LIMIT 1';
       $db = new DB();
       $db->query($qry);
       $db_count = $db->rowCount();
       if (!empty($db_count)) {
           $row = $db->fetch();
           $this->employees2_id[] = $row->employees_id . '[|]' . $row->employees_name;
       } else {
           $this->employees2_id[] = '';
       }
   }

After you made these custom changes to the code, if you have later to rebuild your READ list from the generator don't forget that the File comparison tool is available:
Regenerate list & forms that have been customized (How to use the CRUD Generator file comparison tool)

Of course this solution requires some coding skills, if you need some help please contact us and we'll do our best.

PHP CRUD tutorial main page