Mercurial > packages > magicforger

comparison src/Helpers/RelationshipNavigator.php @ 23:827efbf4d73c main-dev

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Huge changes to the relationships for models and more complex
author Luka Sitas <sitas.luka.97@gmail.com>
date Fri, 11 Apr 2025 20:50:20 -0400
parents
children 31109c61ce02
comparison
equal deleted inserted replaced
22:ee8ef14e158d 23:827efbf4d73c
1 <?php
2
3 namespace Wizard\MagicForger\Helpers;
4
5 use Illuminate\Support\Facades\DB;
6
7 class RelationshipNavigator
8 {
9 public static function handle()
10 {
11 $tables = DB::select('SHOW TABLES');
12 $tableNames = array_map(function ($table) {
13 return current((array) $table);
14 }, $tables);
15
16 foreach ($tableNames as $table) {
17 echo "Table: $table \n";
18
19 $relations = self::getRelations($table);
20 echo "Relationships: \n";
21 foreach ($relations as $relation => $related_tables) {
22 echo $relation.": \n";
23 foreach ($related_tables as $related_table) {
24 echo "\t";
25 foreach ($related_table as $key => $value) {
26 echo "$key : ";
27 if (is_array($value)) {
28 foreach ($related_table as $key => $value) {
29 echo "\n\t\t";
30 echo "$key: $value";
31 }
32 } else {
33 echo $value.' ';
34 }
35 }
36 echo "\n";
37 }
38 }
39 echo "\n --- \n";
40 }
41 }
42
43 public static function getRelations($table)
44 {
45 $relations = [
46 'belongsTo' => [],
47 'hasMany' => [],
48 'hasManyThrough' => [],
49 ];
50
51 $foreignKeys = DB::select("SHOW KEYS FROM $table WHERE Key_name != 'PRIMARY'");
52 $referencedTables = self::getAllReferencedTables($table);
53
54 // BelongsTo Relationships
55 foreach ($foreignKeys as $fk) {
56 $column = $fk->Column_name;
57
58 // skip created and updated by
59 if (in_array($column, ['created_by', 'updated_by'])) {
60 continue;
61 }
62
63 $referencedTable = $referencedTables[$column] ?? null;
64
65 if ($referencedTable) {
66 $relations['belongsTo'][] = ['column' => $column, 'table' => $referencedTable->REFERENCED_TABLE_NAME];
67 }
68
69 }
70
71 // HasMany Relationships
72 if ($reverseRelation = self::findReverseRelation($table)) {
73 foreach ($reverseRelation as $relatedTable) {
74 $relations['hasMany'][] = $relatedTable;
75 }
76 }
77
78 // HasManyThrough Relationships
79 if ($hasManyThroughRelations = self::findHasManyThroughRelations($table)) {
80 foreach ($hasManyThroughRelations as $relatedTable) {
81 $relations['hasManyThrough'][] = $relatedTable;
82 }
83 }
84
85 return $relations;
86 }
87
88 public static function getAllReferencedTables($table)
89 {
90 $results = DB::select("
91 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
92 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
93 WHERE TABLE_SCHEMA = DATABASE()
94 AND TABLE_NAME = '$table'
95 ");
96
97 $tables = self::re_key_array($results, 'COLUMN_NAME');
98
99 return (count($tables) > 0) ? $tables : null;
100 }
101
102 public static function findReverseRelation($table)
103 {
104 $relations = DB::select("
105 SELECT TABLE_NAME, COLUMN_NAME
106 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
107 WHERE TABLE_SCHEMA = DATABASE()
108 AND REFERENCED_TABLE_NAME = '$table'
109 AND REFERENCED_COLUMN_NAME = 'id'
110 AND COLUMN_NAME != 'created_by'
111 AND COLUMN_NAME != 'updated_by'
112 ");
113
114 $relatedTables = array_map(function ($rel) {
115 return ['table' => $rel->TABLE_NAME, 'column' => $rel->COLUMN_NAME];
116 }, $relations);
117
118 return $relatedTables ? $relatedTables : null;
119 }
120
121 public static function findHasManyThroughRelations($table)
122 {
123 $relations = [];
124
125 // Find potential intermediary tables
126 $intermediaryTables = self::findReverseRelation($table);
127
128 if (! is_null($intermediaryTables)) {
129
130 foreach ($intermediaryTables as $intermediary) {
131
132 if ($is_pivot = self::isPivot($intermediary['table'])) {
133 $is_pivot = current($is_pivot);
134
135 // reformat the table based on the current and external
136 $potential_tables = array_keys($is_pivot['tables']);
137 $external_table = $potential_tables[0] == $table ? $is_pivot['tables'][$potential_tables[1]] : $is_pivot['tables'][$potential_tables[0]];
138 $internal_table = $potential_tables[0] == $table ? $is_pivot['tables'][$potential_tables[0]] : $is_pivot['tables'][$potential_tables[1]];
139
140 $hasManyThrough = [
141 'table' => $external_table['table_name'],
142 'through' => [
143 'table' => $is_pivot['table'],
144 'external_column' => $external_table['column'],
145 'internal_column' => $internal_table['column']
146 ]
147 ];
148 $relations[] = $hasManyThrough;
149 }
150 }
151
152 }
153
154 return $relations;
155 }
156
157 public static function isPivot($table)
158 {
159
160 $relations = [];
161 // TODO: alsot get the columns that are relevant
162 $pivotTables = DB::select("
163 SELECT TABLE_NAME, TABLE_COMMENT
164 FROM INFORMATION_SCHEMA.TABLES
165 WHERE TABLE_SCHEMA = DATABASE()
166 AND TABLE_NAME = '{$table}'
167 AND TABLE_COMMENT != ''
168 ");
169
170 if(!is_null($pivotTables) && count($pivotTables) > 0) {
171 $ref = current($pivotTables);
172
173 $pivots = json_decode(str_replace('PIVOT:', '', $ref->TABLE_COMMENT), true);
174
175 $tables = [];
176 if (count($pivots) > 0) {
177 //re-key array
178 $references = self::getAllReferencedTables($table);
179 $references = self::re_key_array($references, 'REFERENCED_TABLE_NAME');
180
181 foreach($pivots as $key => $value) {
182 if($ref_data = ($references[$value] ?? null)) {
183 $tables[$value] = [
184 'table_name' => $value,
185 'column' => $ref_data->COLUMN_NAME
186 ];
187 }
188 }
189 }
190 $relations[] = ['table' => $ref->TABLE_NAME, 'tables' => $tables];
191 }
192
193 return $relations != [] ? $relations : null;
194 }
195
196 public static function re_key_array($old_array, $key) {
197 $new_array = [];
198 if (count($old_array) > 0) {
199 foreach ($old_array as $array) {
200 $new_array[$array->$key] = $array;
201 }
202 }
203 return $new_array;
204 }
205 }