Mercurial > packages > magicforger

comparison src/Helpers/RelationshipNavigator.php @ 24:31109c61ce02 codex

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Refactor RelationshipNavigator formatting and implement belongsTo, hasMany, belongsToMany injection in ModelGenerator
author Luka Sitas <sitas.luka.97@gmail.com>
date Tue, 22 Apr 2025 21:37:44 -0400
parents 827efbf4d73c
children
comparison
equal deleted inserted replaced
23:827efbf4d73c 24:31109c61ce02
2 2
3 namespace Wizard\MagicForger\Helpers; 3 namespace Wizard\MagicForger\Helpers;
4 4
5 use Illuminate\Support\Facades\DB; 5 use Illuminate\Support\Facades\DB;
6 6
7 /**
8 * Class RelationshipNavigator
9 *
10 * This class is responsible for navigating database table relationships, specifically
11 * identifying and categorizing 'belongsTo', 'hasMany', and 'hasManyThrough' relationships.
12 */
7 class RelationshipNavigator 13 class RelationshipNavigator
8 { 14 {
15 /**
16 * Handles the retrieval and display of table relationships.
17 *
18 * @return void
19 */
9 public static function handle() 20 public static function handle()
10 { 21 {
11 $tables = DB::select('SHOW TABLES'); 22 $tables = DB::select('SHOW TABLES');
12 $tableNames = array_map(function ($table) { 23 $tableNames = array_map(fn($table) => current((array) $table), $tables);
13 return current((array) $table);
14 }, $tables);
15 24
16 foreach ($tableNames as $table) { 25 foreach ($tableNames as $table) {
17 echo "Table: $table \n"; 26 echo "Table: $table \n";
18 27
19 $relations = self::getRelations($table); 28 $relations = self::getRelations($table);
20 echo "Relationships: \n"; 29 echo "Relationships: \n";
21 foreach ($relations as $relation => $related_tables) { 30
22 echo $relation.": \n"; 31 foreach ($relations as $relation => $relatedTables) {
23 foreach ($related_tables as $related_table) { 32 echo "$relation: \n";
33 foreach ($relatedTables as $relatedTable) {
24 echo "\t"; 34 echo "\t";
25 foreach ($related_table as $key => $value) { 35 foreach ($relatedTable as $key => $value) {
26 echo "$key : ";
27 if (is_array($value)) { 36 if (is_array($value)) {
28 foreach ($related_table as $key => $value) { 37 echo "\n\t\t" . implode("\n\t\t", array_map(fn($k, $v) => "$k: $v", array_keys($value), $value));
29 echo "\n\t\t";
30 echo "$key: $value";
31 }
32 } else { 38 } else {
33 echo $value.' '; 39 echo "$key: $value ";
34 } 40 }
35 } 41 }
36 echo "\n"; 42 echo "\n";
37 } 43 }
38 } 44 }
39 echo "\n --- \n"; 45 echo "\n --- \n";
40 } 46 }
41 } 47 }
42 48
49 /**
50 * Retrieves relationships of a specific table.
51 *
52 * @param string $table The table name.
53 * @return array An array containing 'belongsTo', 'hasMany', and 'hasManyThrough' relations.
54 */
43 public static function getRelations($table) 55 public static function getRelations($table)
44 { 56 {
45 $relations = [ 57 $relations = [
46 'belongsTo' => [], 58 'belongsTo' => [],
47 'hasMany' => [], 59 'hasMany' => [],
49 ]; 61 ];
50 62
51 $foreignKeys = DB::select("SHOW KEYS FROM $table WHERE Key_name != 'PRIMARY'"); 63 $foreignKeys = DB::select("SHOW KEYS FROM $table WHERE Key_name != 'PRIMARY'");
52 $referencedTables = self::getAllReferencedTables($table); 64 $referencedTables = self::getAllReferencedTables($table);
53 65
54 // BelongsTo Relationships 66 // Determine 'belongsTo' Relationships
55 foreach ($foreignKeys as $fk) { 67 foreach ($foreignKeys as $fk) {
56 $column = $fk->Column_name; 68 $column = $fk->Column_name;
57 69
58 // skip created and updated by 70 // Skip certain columns
59 if (in_array($column, ['created_by', 'updated_by'])) { 71 if (in_array($column, ['created_by', 'updated_by'])) {
60 continue; 72 continue;
61 } 73 }
62 74
63 $referencedTable = $referencedTables[$column] ?? null; 75 $referencedTable = $referencedTables[$column] ?? null;
64 76
65 if ($referencedTable) { 77 if ($referencedTable) {
66 $relations['belongsTo'][] = ['column' => $column, 'table' => $referencedTable->REFERENCED_TABLE_NAME]; 78 $relations['belongsTo'][] = ['column' => $column, 'table' => $referencedTable->REFERENCED_TABLE_NAME];
67 } 79 }
68 80 }
69 } 81
70 82 // Determine 'hasMany' Relationships
71 // HasMany Relationships
72 if ($reverseRelation = self::findReverseRelation($table)) { 83 if ($reverseRelation = self::findReverseRelation($table)) {
73 foreach ($reverseRelation as $relatedTable) { 84 foreach ($reverseRelation as $relatedTable) {
74 $relations['hasMany'][] = $relatedTable; 85 $relations['hasMany'][] = $relatedTable;
75 } 86 }
76 } 87 }
77 88
78 // HasManyThrough Relationships 89 // Determine 'hasManyThrough' Relationships
79 if ($hasManyThroughRelations = self::findHasManyThroughRelations($table)) { 90 if ($hasManyThroughRelations = self::findHasManyThroughRelations($table)) {
80 foreach ($hasManyThroughRelations as $relatedTable) { 91 foreach ($hasManyThroughRelations as $relatedTable) {
81 $relations['hasManyThrough'][] = $relatedTable; 92 $relations['hasManyThrough'][] = $relatedTable;
82 } 93 }
83 } 94 }
84 95
85 return $relations; 96 return $relations;
86 } 97 }
87 98
99 /**
100 * Retrieves all referenced tables for a given table.
101 *
102 * @param string $table The table name.
103 * @return array|null An associative array of referenced tables, keyed by column name.
104 */
88 public static function getAllReferencedTables($table) 105 public static function getAllReferencedTables($table)
89 { 106 {
90 $results = DB::select(" 107 $results = DB::select("
91 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME 108 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
92 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 109 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
93 WHERE TABLE_SCHEMA = DATABASE() 110 WHERE TABLE_SCHEMA = DATABASE()
94 AND TABLE_NAME = '$table' 111 AND REFERENCED_TABLE_NAME IS NOT NULL
95 "); 112 AND TABLE_NAME = ?
96 113 ", [$table]);
97 $tables = self::re_key_array($results, 'COLUMN_NAME'); 114
98 115 return self::re_key_array($results, 'COLUMN_NAME') ?: null;
99 return (count($tables) > 0) ? $tables : null; 116 }
100 } 117
101 118 /**
119 * Finds 'hasMany' inverse relationships for a given table.
120 *
121 * @param string $table The table name.
122 * @return array|null An array of related tables with column names.
123 */
102 public static function findReverseRelation($table) 124 public static function findReverseRelation($table)
103 { 125 {
104 $relations = DB::select(" 126 $relations = DB::select("
105 SELECT TABLE_NAME, COLUMN_NAME 127 SELECT TABLE_NAME, COLUMN_NAME
106 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 128 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
107 WHERE TABLE_SCHEMA = DATABASE() 129 WHERE TABLE_SCHEMA = DATABASE()
108 AND REFERENCED_TABLE_NAME = '$table' 130 AND REFERENCED_TABLE_NAME = ?
109 AND REFERENCED_COLUMN_NAME = 'id' 131 AND REFERENCED_COLUMN_NAME = 'id'
110 AND COLUMN_NAME != 'created_by' 132 AND COLUMN_NAME NOT IN ('created_by', 'updated_by')
111 AND COLUMN_NAME != 'updated_by' 133 ", [$table]);
112 "); 134
113 135 return array_map(fn($rel) => ['table' => $rel->TABLE_NAME, 'column' => $rel->COLUMN_NAME], $relations) ?: null;
114 $relatedTables = array_map(function ($rel) { 136 }
115 return ['table' => $rel->TABLE_NAME, 'column' => $rel->COLUMN_NAME]; 137
116 }, $relations); 138 /**
117 139 * Finds 'hasManyThrough' relationships for a given table.
118 return $relatedTables ? $relatedTables : null; 140 *
119 } 141 * @param string $table The table name.
120 142 * @return array An array of 'hasManyThrough' relationships.
143 */
121 public static function findHasManyThroughRelations($table) 144 public static function findHasManyThroughRelations($table)
122 { 145 {
123 $relations = []; 146 $relations = [];
124
125 // Find potential intermediary tables
126 $intermediaryTables = self::findReverseRelation($table); 147 $intermediaryTables = self::findReverseRelation($table);
127 148
128 if (! is_null($intermediaryTables)) { 149 if ($intermediaryTables !== null) {
129
130 foreach ($intermediaryTables as $intermediary) { 150 foreach ($intermediaryTables as $intermediary) {
131 151 if ($isPivot = self::isPivot($intermediary['table'])) {
132 if ($is_pivot = self::isPivot($intermediary['table'])) { 152 $isPivot = current($isPivot);
133 $is_pivot = current($is_pivot); 153
134 154 $potentialTables = array_keys($isPivot['tables']);
135 // reformat the table based on the current and external 155 $externalTable = $potentialTables[0] === $table ? $isPivot['tables'][$potentialTables[1]] : $isPivot['tables'][$potentialTables[0]];
136 $potential_tables = array_keys($is_pivot['tables']); 156 $internalTable = $potentialTables[0] === $table ? $isPivot['tables'][$potentialTables[0]] : $isPivot['tables'][$potentialTables[1]];
137 $external_table = $potential_tables[0] == $table ? $is_pivot['tables'][$potential_tables[1]] : $is_pivot['tables'][$potential_tables[0]]; 157
138 $internal_table = $potential_tables[0] == $table ? $is_pivot['tables'][$potential_tables[0]] : $is_pivot['tables'][$potential_tables[1]]; 158 $hasManyThrough = [
139 159 'table' => $externalTable['table_name'],
140 $hasManyThrough = [ 160 'through' => [
141 'table' => $external_table['table_name'], 161 'table' => $isPivot['table'],
142 'through' => [ 162 'external_column' => $externalTable['column'],
143 'table' => $is_pivot['table'], 163 'internal_column' => $internalTable['column'],
144 'external_column' => $external_table['column'], 164 ],
145 'internal_column' => $internal_table['column']
146 ]
147 ]; 165 ];
148 $relations[] = $hasManyThrough; 166 $relations[] = $hasManyThrough;
149 } 167 }
150 } 168 }
151
152 } 169 }
153 170
154 return $relations; 171 return $relations;
155 } 172 }
156 173
174 /**
175 * Determines if a table is a pivot table.
176 *
177 * @param string $table The table name.
178 * @return array|null An array with pivot details or null if not a pivot.
179 */
157 public static function isPivot($table) 180 public static function isPivot($table)
158 { 181 {
159
160 $relations = []; 182 $relations = [];
161 // TODO: alsot get the columns that are relevant
162 $pivotTables = DB::select(" 183 $pivotTables = DB::select("
163 SELECT TABLE_NAME, TABLE_COMMENT 184 SELECT TABLE_NAME, TABLE_COMMENT
164 FROM INFORMATION_SCHEMA.TABLES 185 FROM INFORMATION_SCHEMA.TABLES
165 WHERE TABLE_SCHEMA = DATABASE() 186 WHERE TABLE_SCHEMA = DATABASE()
166 AND TABLE_NAME = '{$table}' 187 AND TABLE_NAME = ?
167 AND TABLE_COMMENT != '' 188 AND TABLE_COMMENT != ''
168 "); 189 ", [$table]);
169 190
170 if(!is_null($pivotTables) && count($pivotTables) > 0) { 191 if (!is_null($pivotTables) && count($pivotTables) > 0) {
171 $ref = current($pivotTables); 192 $ref = current($pivotTables);
172 193 $pivots = json_decode(str_replace('PIVOT:', '', $ref->TABLE_COMMENT), true);
173 $pivots = json_decode(str_replace('PIVOT:', '', $ref->TABLE_COMMENT), true); 194 $tables = [];
174 195
175 $tables = []; 196 if (count($pivots) > 0) {
176 if (count($pivots) > 0) { 197 $references = self::getAllReferencedTables($table);
177 //re-key array 198 $references = self::re_key_array($references, 'REFERENCED_TABLE_NAME');
178 $references = self::getAllReferencedTables($table); 199
179 $references = self::re_key_array($references, 'REFERENCED_TABLE_NAME'); 200 foreach ($pivots as $key => $value) {
180 201 if ($refData = ($references[$value] ?? null)) {
181 foreach($pivots as $key => $value) { 202 $tables[$value] = [
182 if($ref_data = ($references[$value] ?? null)) { 203 'table_name' => $value,
183 $tables[$value] = [ 204 'column' => $refData->COLUMN_NAME,
184 'table_name' => $value, 205 ];
185 'column' => $ref_data->COLUMN_NAME 206 }
186 ]; 207 }
187 } 208 }
188 } 209 $relations[] = ['table' => $ref->TABLE_NAME, 'tables' => $tables];
189 } 210 }
190 $relations[] = ['table' => $ref->TABLE_NAME, 'tables' => $tables]; 211
191 } 212 return !empty($relations) ? $relations : null;
192 213 }
193 return $relations != [] ? $relations : null; 214
194 } 215 /**
195 216 * Re-keys an array of objects using a specific object's property.
196 public static function re_key_array($old_array, $key) { 217 *
197 $new_array = []; 218 * @param array $oldArray The original array of objects.
198 if (count($old_array) > 0) { 219 * @param string $key The key to re-index by.
199 foreach ($old_array as $array) { 220 * @return array The re-keyed array.
200 $new_array[$array->$key] = $array; 221 */
201 } 222 public static function re_key_array($oldArray, $key)
202 } 223 {
203 return $new_array; 224 $newArray = [];
204 } 225 if (count($oldArray) > 0) {
226 foreach ($oldArray as $array) {
227 $newArray[$array->$key] = $array;
228 }
229 }
230 return $newArray;
231 }
205 } 232 }