Mercurial > packages > magicforger

comparison src/Helpers/RelationshipNavigator.php @ 34:f65ab84ee47f default

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merge with codex
author luka
date Wed, 10 Sep 2025 21:00:47 -0400
parents 31109c61ce02
children
comparison
equal deleted inserted replaced
10:a9ff874afdbd 34:f65ab84ee47f
1 <?php
2
3 namespace Wizard\MagicForger\Helpers;
4
5 use Illuminate\Support\Facades\DB;
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 */
13 class RelationshipNavigator
14 {
15 /**
16 * Handles the retrieval and display of table relationships.
17 *
18 * @return void
19 */
20 public static function handle()
21 {
22 $tables = DB::select('SHOW TABLES');
23 $tableNames = array_map(fn($table) => current((array) $table), $tables);
24
25 foreach ($tableNames as $table) {
26 echo "Table: $table \n";
27
28 $relations = self::getRelations($table);
29 echo "Relationships: \n";
30
31 foreach ($relations as $relation => $relatedTables) {
32 echo "$relation: \n";
33 foreach ($relatedTables as $relatedTable) {
34 echo "\t";
35 foreach ($relatedTable as $key => $value) {
36 if (is_array($value)) {
37 echo "\n\t\t" . implode("\n\t\t", array_map(fn($k, $v) => "$k: $v", array_keys($value), $value));
38 } else {
39 echo "$key: $value ";
40 }
41 }
42 echo "\n";
43 }
44 }
45 echo "\n --- \n";
46 }
47 }
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 */
55 public static function getRelations($table)
56 {
57 $relations = [
58 'belongsTo' => [],
59 'hasMany' => [],
60 'hasManyThrough' => [],
61 ];
62
63 $foreignKeys = DB::select("SHOW KEYS FROM $table WHERE Key_name != 'PRIMARY'");
64 $referencedTables = self::getAllReferencedTables($table);
65
66 // Determine 'belongsTo' Relationships
67 foreach ($foreignKeys as $fk) {
68 $column = $fk->Column_name;
69
70 // Skip certain columns
71 if (in_array($column, ['created_by', 'updated_by'])) {
72 continue;
73 }
74
75 $referencedTable = $referencedTables[$column] ?? null;
76
77 if ($referencedTable) {
78 $relations['belongsTo'][] = ['column' => $column, 'table' => $referencedTable->REFERENCED_TABLE_NAME];
79 }
80 }
81
82 // Determine 'hasMany' Relationships
83 if ($reverseRelation = self::findReverseRelation($table)) {
84 foreach ($reverseRelation as $relatedTable) {
85 $relations['hasMany'][] = $relatedTable;
86 }
87 }
88
89 // Determine 'hasManyThrough' Relationships
90 if ($hasManyThroughRelations = self::findHasManyThroughRelations($table)) {
91 foreach ($hasManyThroughRelations as $relatedTable) {
92 $relations['hasManyThrough'][] = $relatedTable;
93 }
94 }
95
96 return $relations;
97 }
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 */
105 public static function getAllReferencedTables($table)
106 {
107 $results = DB::select("
108 SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
109 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
110 WHERE TABLE_SCHEMA = DATABASE()
111 AND REFERENCED_TABLE_NAME IS NOT NULL
112 AND TABLE_NAME = ?
113 ", [$table]);
114
115 return self::re_key_array($results, 'COLUMN_NAME') ?: null;
116 }
117
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 */
124 public static function findReverseRelation($table)
125 {
126 $relations = DB::select("
127 SELECT TABLE_NAME, COLUMN_NAME
128 FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
129 WHERE TABLE_SCHEMA = DATABASE()
130 AND REFERENCED_TABLE_NAME = ?
131 AND REFERENCED_COLUMN_NAME = 'id'
132 AND COLUMN_NAME NOT IN ('created_by', 'updated_by')
133 ", [$table]);
134
135 return array_map(fn($rel) => ['table' => $rel->TABLE_NAME, 'column' => $rel->COLUMN_NAME], $relations) ?: null;
136 }
137
138 /**
139 * Finds 'hasManyThrough' relationships for a given table.
140 *
141 * @param string $table The table name.
142 * @return array An array of 'hasManyThrough' relationships.
143 */
144 public static function findHasManyThroughRelations($table)
145 {
146 $relations = [];
147 $intermediaryTables = self::findReverseRelation($table);
148
149 if ($intermediaryTables !== null) {
150 foreach ($intermediaryTables as $intermediary) {
151 if ($isPivot = self::isPivot($intermediary['table'])) {
152 $isPivot = current($isPivot);
153
154 $potentialTables = array_keys($isPivot['tables']);
155 $externalTable = $potentialTables[0] === $table ? $isPivot['tables'][$potentialTables[1]] : $isPivot['tables'][$potentialTables[0]];
156 $internalTable = $potentialTables[0] === $table ? $isPivot['tables'][$potentialTables[0]] : $isPivot['tables'][$potentialTables[1]];
157
158 $hasManyThrough = [
159 'table' => $externalTable['table_name'],
160 'through' => [
161 'table' => $isPivot['table'],
162 'external_column' => $externalTable['column'],
163 'internal_column' => $internalTable['column'],
164 ],
165 ];
166 $relations[] = $hasManyThrough;
167 }
168 }
169 }
170
171 return $relations;
172 }
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 */
180 public static function isPivot($table)
181 {
182 $relations = [];
183 $pivotTables = DB::select("
184 SELECT TABLE_NAME, TABLE_COMMENT
185 FROM INFORMATION_SCHEMA.TABLES
186 WHERE TABLE_SCHEMA = DATABASE()
187 AND TABLE_NAME = ?
188 AND TABLE_COMMENT != ''
189 ", [$table]);
190
191 if (!is_null($pivotTables) && count($pivotTables) > 0) {
192 $ref = current($pivotTables);
193 $pivots = json_decode(str_replace('PIVOT:', '', $ref->TABLE_COMMENT), true);
194 $tables = [];
195
196 if (count($pivots) > 0) {
197 $references = self::getAllReferencedTables($table);
198 $references = self::re_key_array($references, 'REFERENCED_TABLE_NAME');
199
200 foreach ($pivots as $key => $value) {
201 if ($refData = ($references[$value] ?? null)) {
202 $tables[$value] = [
203 'table_name' => $value,
204 'column' => $refData->COLUMN_NAME,
205 ];
206 }
207 }
208 }
209 $relations[] = ['table' => $ref->TABLE_NAME, 'tables' => $tables];
210 }
211
212 return !empty($relations) ? $relations : null;
213 }
214
215 /**
216 * Re-keys an array of objects using a specific object's property.
217 *
218 * @param array $oldArray The original array of objects.
219 * @param string $key The key to re-index by.
220 * @return array The re-keyed array.
221 */
222 public static function re_key_array($oldArray, $key)
223 {
224 $newArray = [];
225 if (count($oldArray) > 0) {
226 foreach ($oldArray as $array) {
227 $newArray[$array->$key] = $array;
228 }
229 }
230 return $newArray;
231 }
232 }