MySQL Self Join - Joining a Table to Itself

About tables using a join to themselves

Some tables may sometimes use a relational key to themselves.
For example a table that contains categories and sub-categories at the same time (see the image below).
In this example, each sub-category has a relationship to a parent category.

How to use self-joined tables with PHP CRUD Generator

PHP CRUD Generator is able to understand these particular joins in the same way as the usual relationships.
The forms generated in the Admin will use these relationships without any problem.

On the other hand, The SQL query used to retrieve records from the READ list must be edited to work properly.

Here's how to do it:

1. Create your READ list from the Generator as usual.

2. Open the PHP class model corresponding to your table, which can be found in admin/class/crud/[TableName].php

3. In this file you'll find 2 lines that build the base SQL query.

   With the above example "categories" we have given, it should look like this:

   $this->join_query = ' LEFT JOIN `categories` ON `categories`.`parent_category_id`=`categories`.`id`';
   $qry_start = 'SELECT
   `categories`.`id`,
   `categories`.`parent_category_id` AS `categories_parent_category_id`,
   `categories`.`name`
   FROM categories';

4. You have to edit this request to:

   • use an alias for the table name that refers to the relationnal field.
   • move the LEFT JOIN into the $qry_start variable.

   Your final code should look like this:

   $this->join_query = '';
   $qry_start = 'SELECT
   `categories`.`id`,
   `c2`.`parent_category_id` AS `categories_parent_category_id`,
   `categories`.`name`
   FROM categories
   LEFT JOIN `categories` c2
   ON `categories`.`parent_category_id`=`c2`.`id`';

   In this example, c2 is an alias for the categories table.
   We use it with the parent_category_id

   ---

   A very good tutorial is available here about MySQL Self Join

PHP CRUD tutorial main page