Joining a table to itself is not a built-in feature, but can be done simply by modifying the SQL code generated by the CRUD Generator.
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.
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:
Open the PHP class model corresponding to your table, which can be found in
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';
You have to edit this request to:
LEFT JOINinto the
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
We use it with the
A very good tutorial is available here about MySQL Self Join