18483557340

Committed 14 Oct 2025 02:25AM UTC coverage: 84.208% (+3.7%) from 80.483%

Build # 18483557340

Build Type

Pull #42

github

Committed by

web-flow

Commit Message

Merge 3a2243fbd into 025025bcf

Pull Request Pull Request #42: Implement comprehensive join optimization system to solve N+1 query problem

Run Details

845 of 960 new or added lines in 19 files covered. (88.02%)

66 existing lines in 3 files now uncovered.

1637 of 1944 relevant lines covered (84.21%)

15.67 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

85.71

/src/Relationship/Strategy/JoinWithSelectionLoader.php

<?php

namespace Anorm\Relationship\Strategy;

use Anorm\Relationship\BatchLoader\BatchLoaderInterface;

/**
 * JOIN-based loader with field selection optimization
 *
 * This loader uses JOIN queries with field selection to minimize data transfer
 * while maintaining query efficiency. It's particularly effective for relationships
 * where only specific fields are needed from related models.
 */
class JoinWithSelectionLoader implements BatchLoaderInterface
{
    /** @var FieldSelectionParser */
    private $fieldParser;

    public function __construct(FieldSelectionParser $fieldParser = null)
    {
        $this->fieldParser = $fieldParser ?: new FieldSelectionParser();
    }

    /**
     * Load relationships using JOIN with field selection
     *
     * @param array $sourceModels Array of model instances that need relationships loaded
     * @param string $relationshipName Name of the relationship to load
     * @param array|null $fieldSelection Optional field selection for optimization
     * @return array Associative array of loaded relationship data, keyed by source model identifier
     */
    public function batchLoad(array $sourceModels, string $relationshipName, ?array $fieldSelection = null): array
    {
        if (empty($sourceModels)) {
            return [];
        }

        // Get relationship definition from the first model
        $firstModel = reset($sourceModels);
        $relationshipManager = $firstModel->getRelationshipManager();
        $relationship = $relationshipManager->getRelationship($relationshipName);

        if (!$relationship) {
            throw new \Exception("Relationship '{$relationshipName}' not defined");
        }

        // Use provided field selection or parse from relationship name
        if ($fieldSelection === null) {
            $parsedSpec = $this->fieldParser->parseFieldSelection($relationshipName);
            $fieldSelection = $parsedSpec['fields'];
        }

        // Build and execute JOIN query
        $query = $this->buildJoinQuery($relationship, $fieldSelection, $sourceModels);
        $primaryKeys = $this->extractPrimaryKeys($sourceModels, $relationship);

        if (empty($primaryKeys)) {
            return [];
        }

        $stmt = $firstModel->getPdo()->prepare($query);
        $stmt->execute($primaryKeys);
        $result = $stmt;

        // Process results and group by source model
        return $this->processJoinResults($result, $sourceModels, $relationship, $fieldSelection);
    }

    /**
     * Distribute batch-loaded results to their corresponding source models
     *
     * @param array $sourceModels Array of model instances to receive the loaded data
     * @param array $batchResults Results from batchLoad(), keyed by source model identifier
     * @param string $relationshipName Name of the relationship being distributed
     * @return void
     */
    public function distributeBatchResults(array $sourceModels, array $batchResults, string $relationshipName): void
    {
        // Get relationship definition to determine cardinality
        $firstModel = reset($sourceModels);
        $relationshipManager = $firstModel->getRelationshipManager();
        $relationship = $relationshipManager->getRelationship($relationshipName);

        if (!$relationship) {
            return; // Relationship not found, skip distribution
        }

        $cardinality = $relationship->getCardinality();

        foreach ($sourceModels as $model) {
            $primaryKeyValue = $model->{$relationship->getPrimaryKey()};

            if (isset($batchResults[$primaryKeyValue])) {
                if ($cardinality === 'many-to-one' || $cardinality === 'one-to-one') {
                    // Single model relationship
                    $model->{$relationshipName} = reset($batchResults[$primaryKeyValue]) ?: null;
                } else {
                    // Array of models relationship
                    $model->{$relationshipName} = $batchResults[$primaryKeyValue];
                }
            } else {
                // No related models found
                if ($cardinality === 'many-to-one' || $cardinality === 'one-to-one') {
                    $model->{$relationshipName} = null;
                } else {
                    $model->{$relationshipName} = [];
                }
            }
        }
    }

    /**
     * Build JOIN query with field selection
     *
     * @param object $relationship The relationship instance
     * @param array|null $fieldSelection Specific fields to load, or null for all fields
     * @param array $sourceModels Source models for the query
     * @return string SQL query string
     */
    public function buildJoinQuery($relationship, ?array $fieldSelection, array $sourceModels): string
    {
        // Get table information
        $sourceTable = $this->getTableName($relationship, 'source');
        $relatedTable = $this->getTableName($relationship, 'related');

        // Build SELECT clause with field selection
        $selectClause = $this->buildSelectClause($fieldSelection, $sourceTable, $relatedTable, $relationship);

        // Build JOIN clause
        $joinClause = $relationship->generateJoinClause($sourceTable, $relatedTable);

        // Build WHERE clause for source model filtering
        $primaryKeys = $this->extractPrimaryKeys($sourceModels, $relationship);
        $whereClause = $this->buildWhereClause($sourceTable, $relationship->getPrimaryKey(), $primaryKeys);

        return "SELECT {$selectClause} FROM `{$sourceTable}` s {$joinClause} WHERE {$whereClause}";
    }

    /**
     * Build SELECT clause with field selection and table aliases
     *
     * @param array|null $fieldSelection Specific fields to load
     * @param string $sourceTable Source table name
     * @param string $relatedTable Related table name
     * @param object $relationship The relationship instance
     * @return string SELECT clause
     */
    public function buildSelectClause(?array $fieldSelection, string $sourceTable, string $relatedTable, $relationship): string
    {
        $selectParts = [];

        // Always include source primary key for grouping
        $sourcePrimaryKey = $relationship->getPrimaryKey();
        $selectParts[] = "s.`{$sourcePrimaryKey}` AS source_id";

        // Add related table fields with selection
        if ($fieldSelection === null || empty($fieldSelection)) {
            // Select all fields from related table
            $selectParts[] = "r.*";
        } else {
            // Select only specified fields
            foreach ($fieldSelection as $field) {
                $selectParts[] = "r.`{$field}`";
            }
        }

        return implode(', ', $selectParts);
    }

    /**
     * Process JOIN query results and group by source model
     *
     * @param \PDOStatement $result Query result
     * @param array $sourceModels Source models
     * @param object $relationship The relationship instance
     * @param array|null $fieldSelection Field selection used
     * @return array Grouped results
     */
    public function processJoinResults(\PDOStatement $result, array $sourceModels, $relationship, ?array $fieldSelection): array
    {
        $groupedResults = [];
        $relatedClass = $relationship->getRelatedModelClass();

        while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {
            $sourceId = $row['source_id'];
            unset($row['source_id']); // Remove source ID from related model data

            // Skip rows with no related data (LEFT JOIN nulls)
            if ($this->isEmptyRelatedRow($row)) {
                continue;
            }

            // Create related model instance (potentially partial)
            $firstModel = reset($sourceModels);
            $relatedModel = $this->createPartialModel($relatedClass, $row, $fieldSelection, $firstModel->getPdo());

            // Group by source ID
            if (!isset($groupedResults[$sourceId])) {
                $groupedResults[$sourceId] = [];
            }
            $groupedResults[$sourceId][] = $relatedModel;
        }

        return $groupedResults;
    }

    /**
     * Create a model instance with potentially partial data
     *
     * @param string $modelClass Model class name
     * @param array $data Row data from database
     * @param array|null $fieldSelection Fields that were selected
     * @param \PDO|null $pdo PDO connection to use
     * @return object Model instance
     */
    public function createPartialModel(string $modelClass, array $data, ?array $fieldSelection, ?\PDO $pdo = null)
    {
        // Get PDO from parameter or create a default one
        if ($pdo === null) {
            $pdo = new \PDO('mysql:host=localhost;dbname=anorm_test', 'dev', 'dev');
        }

        $model = new $modelClass($pdo);

        // Load the data into the model
        $model->_mapper->readArray($model, $data);

        // If we have field selection, mark which fields were loaded
        if ($fieldSelection !== null && method_exists($model, 'setLoadedFields')) {
            $model->setLoadedFields($fieldSelection);
        }

        return $model;
    }

    /**
     * Extract primary keys from source models
     *
     * @param array $sourceModels Source models
     * @param object $relationship The relationship instance
     * @return array Primary key values
     */
    private function extractPrimaryKeys(array $sourceModels, $relationship): array
    {
        $primaryKeys = [];
        $primaryKeyField = $relationship->getPrimaryKey();

        foreach ($sourceModels as $model) {
            $primaryKeyValue = $model->{$primaryKeyField};
            if ($primaryKeyValue !== null) {
                $primaryKeys[] = $primaryKeyValue;
            }
        }

        return array_unique($primaryKeys);
    }

    /**
     * Build WHERE clause for primary key filtering
     *
     * @param string $table Table name
     * @param string $primaryKeyField Primary key field name
     * @param array $primaryKeys Primary key values
     * @return string WHERE clause
     */
    private function buildWhereClause(string $table, string $primaryKeyField, array $primaryKeys): string
    {
        if (empty($primaryKeys)) {
            return '1=0'; // No results
        }

        $placeholders = str_repeat('?,', count($primaryKeys) - 1) . '?';
        return "s.`{$primaryKeyField}` IN ({$placeholders})";
    }

    /**
     * Get table name for a relationship
     *
     * @param object $relationship The relationship instance
     * @param string $type 'source' or 'related'
     * @return string Table name
     */
    private function getTableName($relationship, string $type): string
    {
        // This is a simplified implementation
        // In practice, we'd get this from the model's mapper
        if ($type === 'source') {
            return 'users'; // Default assumption
        } else {
            $relatedClass = $relationship->getRelatedModelClass();
            // Convert class name to table name (simplified)
            return strtolower(str_replace('Model', 's', $relatedClass));
        }
    }

    /**
     * Check if a row contains only null values (LEFT JOIN with no match)
     *
     * @param array $row Database row
     * @return bool True if row is empty/null
     */
    private function isEmptyRelatedRow(array $row): bool
    {
        foreach ($row as $value) {
            if ($value !== null) {
                return false;
            }
        }
        return true;
    }

    /**
     * Check if this loader can handle a specific relationship type
     *
     * @param object $relationship The relationship instance
     * @return bool True if this loader can handle the relationship
     */
    public function canHandle($relationship): bool
    {
        // JOIN strategy can handle all relationship types
        return true;
    }

    /**
     * Estimate the number of queries this strategy will execute
     *
     * @param int $sourceCount Number of source models
     * @return int Estimated query count
     */
    public function estimateQueryCount(int $sourceCount): int
    {
        // JOIN strategy always uses exactly 1 query regardless of source count
        return $sourceCount > 0 ? 1 : 0;
    }

    /**
     * Get the maximum batch size this loader can handle efficiently
     *
     * @return int Maximum batch size
     */
    public function getMaxBatchSize(): int
    {
        // JOIN strategy can handle very large batches efficiently
        return 10000;
    }
}

1	<?php
2
3	namespace Anorm\Relationship\Strategy;
4
5	use Anorm\Relationship\BatchLoader\BatchLoaderInterface;
6
7	/**
8	* JOIN-based loader with field selection optimization
9	*
10	* This loader uses JOIN queries with field selection to minimize data transfer
11	* while maintaining query efficiency. It's particularly effective for relationships
12	* where only specific fields are needed from related models.
13	*/
14	class JoinWithSelectionLoader implements BatchLoaderInterface
15	{
16	/** @var FieldSelectionParser */
17	private $fieldParser;
18
19	public function __construct(FieldSelectionParser $fieldParser = null)
20	{
21	$this->fieldParser = $fieldParser ?: new FieldSelectionParser();	11✔
22	}
23
24	/**
25	* Load relationships using JOIN with field selection
26	*
27	* @param array $sourceModels Array of model instances that need relationships loaded
28	* @param string $relationshipName Name of the relationship to load
29	* @param array\|null $fieldSelection Optional field selection for optimization
30	* @return array Associative array of loaded relationship data, keyed by source model identifier
31	*/
32	public function batchLoad(array $sourceModels, string $relationshipName, ?array $fieldSelection = null): array
33	{
34	if (empty($sourceModels)) {	2✔
35	return [];	1✔
36	}
37
38	// Get relationship definition from the first model
39	$firstModel = reset($sourceModels);	1✔
40	$relationshipManager = $firstModel->getRelationshipManager();	1✔
41	$relationship = $relationshipManager->getRelationship($relationshipName);	1✔
42
43	if (!$relationship) {	1✔
NEW 44	throw new \Exception("Relationship '{$relationshipName}' not defined");	×
45	}
46
47	// Use provided field selection or parse from relationship name
48	if ($fieldSelection === null) {	1✔
NEW 49	$parsedSpec = $this->fieldParser->parseFieldSelection($relationshipName);	×
NEW 50	$fieldSelection = $parsedSpec['fields'];	×
51	}
52
53	// Build and execute JOIN query
54	$query = $this->buildJoinQuery($relationship, $fieldSelection, $sourceModels);	1✔
55	$primaryKeys = $this->extractPrimaryKeys($sourceModels, $relationship);	1✔
56
57	if (empty($primaryKeys)) {	1✔
NEW 58	return [];	×
59	}
60
61	$stmt = $firstModel->getPdo()->prepare($query);	1✔
62	$stmt->execute($primaryKeys);	1✔
NEW 63	$result = $stmt;	×
64
65	// Process results and group by source model
NEW 66	return $this->processJoinResults($result, $sourceModels, $relationship, $fieldSelection);	×
67	}
68
69	/**
70	* Distribute batch-loaded results to their corresponding source models
71	*
72	* @param array $sourceModels Array of model instances to receive the loaded data
73	* @param array $batchResults Results from batchLoad(), keyed by source model identifier
74	* @param string $relationshipName Name of the relationship being distributed
75	* @return void
76	*/
77	public function distributeBatchResults(array $sourceModels, array $batchResults, string $relationshipName): void
78	{
79	// Get relationship definition to determine cardinality
80	$firstModel = reset($sourceModels);	1✔
81	$relationshipManager = $firstModel->getRelationshipManager();	1✔
82	$relationship = $relationshipManager->getRelationship($relationshipName);	1✔
83
84	if (!$relationship) {	1✔
NEW 85	return; // Relationship not found, skip distribution	×
86	}
87
88	$cardinality = $relationship->getCardinality();	1✔
89
90	foreach ($sourceModels as $model) {	1✔
91	$primaryKeyValue = $model->{$relationship->getPrimaryKey()};	1✔
92
93	if (isset($batchResults[$primaryKeyValue])) {	1✔
94	if ($cardinality === 'many-to-one' \|\| $cardinality === 'one-to-one') {	1✔
95	// Single model relationship
NEW 96	$model->{$relationshipName} = reset($batchResults[$primaryKeyValue]) ?: null;	×
97	} else {
98	// Array of models relationship
99	$model->{$relationshipName} = $batchResults[$primaryKeyValue];	1✔
100	}
101	} else {
102	// No related models found
NEW 103	if ($cardinality === 'many-to-one' \|\| $cardinality === 'one-to-one') {	×
NEW 104	$model->{$relationshipName} = null;	×
105	} else {
NEW 106	$model->{$relationshipName} = [];	×
107	}
108	}
109	}
110	}
111
112	/**
113	* Build JOIN query with field selection
114	*
115	* @param object $relationship The relationship instance
116	* @param array\|null $fieldSelection Specific fields to load, or null for all fields
117	* @param array $sourceModels Source models for the query
118	* @return string SQL query string
119	*/
120	public function buildJoinQuery($relationship, ?array $fieldSelection, array $sourceModels): string
121	{
122	// Get table information
123	$sourceTable = $this->getTableName($relationship, 'source');	2✔
124	$relatedTable = $this->getTableName($relationship, 'related');	2✔
125
126	// Build SELECT clause with field selection
127	$selectClause = $this->buildSelectClause($fieldSelection, $sourceTable, $relatedTable, $relationship);	2✔
128
129	// Build JOIN clause
130	$joinClause = $relationship->generateJoinClause($sourceTable, $relatedTable);	2✔
131
132	// Build WHERE clause for source model filtering
133	$primaryKeys = $this->extractPrimaryKeys($sourceModels, $relationship);	2✔
134	$whereClause = $this->buildWhereClause($sourceTable, $relationship->getPrimaryKey(), $primaryKeys);	2✔
135
136	return "SELECT {$selectClause} FROM `{$sourceTable}` s {$joinClause} WHERE {$whereClause}";	2✔
137	}
138
139	/**
140	* Build SELECT clause with field selection and table aliases
141	*
142	* @param array\|null $fieldSelection Specific fields to load
143	* @param string $sourceTable Source table name
144	* @param string $relatedTable Related table name
145	* @param object $relationship The relationship instance
146	* @return string SELECT clause
147	*/
148	public function buildSelectClause(?array $fieldSelection, string $sourceTable, string $relatedTable, $relationship): string
149	{
150	$selectParts = [];	3✔
151
152	// Always include source primary key for grouping
153	$sourcePrimaryKey = $relationship->getPrimaryKey();	3✔
154	$selectParts[] = "s.`{$sourcePrimaryKey}` AS source_id";	3✔
155
156	// Add related table fields with selection
157	if ($fieldSelection === null \|\| empty($fieldSelection)) {	3✔
158	// Select all fields from related table
159	$selectParts[] = "r.*";	1✔
160	} else {
161	// Select only specified fields
162	foreach ($fieldSelection as $field) {	3✔
163	$selectParts[] = "r.`{$field}`";	3✔
164	}
165	}
166
167	return implode(', ', $selectParts);	3✔
168	}
169
170	/**
171	* Process JOIN query results and group by source model
172	*
173	* @param \PDOStatement $result Query result
174	* @param array $sourceModels Source models
175	* @param object $relationship The relationship instance
176	* @param array\|null $fieldSelection Field selection used
177	* @return array Grouped results
178	*/
179	public function processJoinResults(\PDOStatement $result, array $sourceModels, $relationship, ?array $fieldSelection): array
180	{
181	$groupedResults = [];	1✔
182	$relatedClass = $relationship->getRelatedModelClass();	1✔
183
184	while ($row = $result->fetch(\PDO::FETCH_ASSOC)) {	1✔
185	$sourceId = $row['source_id'];	1✔
186	unset($row['source_id']); // Remove source ID from related model data	1✔
187
188	// Skip rows with no related data (LEFT JOIN nulls)
189	if ($this->isEmptyRelatedRow($row)) {	1✔
190	continue;	1✔
191	}
192
193	// Create related model instance (potentially partial)
194	$firstModel = reset($sourceModels);	1✔
195	$relatedModel = $this->createPartialModel($relatedClass, $row, $fieldSelection, $firstModel->getPdo());	1✔
196
197	// Group by source ID
198	if (!isset($groupedResults[$sourceId])) {	1✔
199	$groupedResults[$sourceId] = [];	1✔
200	}
201	$groupedResults[$sourceId][] = $relatedModel;	1✔
202	}
203
204	return $groupedResults;	1✔
205	}
206
207	/**
208	* Create a model instance with potentially partial data
209	*
210	* @param string $modelClass Model class name
211	* @param array $data Row data from database
212	* @param array\|null $fieldSelection Fields that were selected
213	* @param \PDO\|null $pdo PDO connection to use
214	* @return object Model instance
215	*/
216	public function createPartialModel(string $modelClass, array $data, ?array $fieldSelection, ?\PDO $pdo = null)
217	{
218	// Get PDO from parameter or create a default one
219	if ($pdo === null) {	2✔
NEW 220	$pdo = new \PDO('mysql:host=localhost;dbname=anorm_test', 'dev', 'dev');	×
221	}
222
223	$model = new $modelClass($pdo);	2✔
224
225	// Load the data into the model
226	$model->_mapper->readArray($model, $data);	2✔
227
228	// If we have field selection, mark which fields were loaded
229	if ($fieldSelection !== null && method_exists($model, 'setLoadedFields')) {	2✔
230	$model->setLoadedFields($fieldSelection);	2✔
231	}
232
233	return $model;	2✔
234	}
235
236	/**
237	* Extract primary keys from source models
238	*
239	* @param array $sourceModels Source models
240	* @param object $relationship The relationship instance
241	* @return array Primary key values
242	*/
243	private function extractPrimaryKeys(array $sourceModels, $relationship): array
244	{
245	$primaryKeys = [];	2✔
246	$primaryKeyField = $relationship->getPrimaryKey();	2✔
247
248	foreach ($sourceModels as $model) {	2✔
249	$primaryKeyValue = $model->{$primaryKeyField};	2✔
250	if ($primaryKeyValue !== null) {	2✔
251	$primaryKeys[] = $primaryKeyValue;	2✔
252	}
253	}
254
255	return array_unique($primaryKeys);	2✔
256	}
257
258	/**
259	* Build WHERE clause for primary key filtering
260	*
261	* @param string $table Table name
262	* @param string $primaryKeyField Primary key field name
263	* @param array $primaryKeys Primary key values
264	* @return string WHERE clause
265	*/
266	private function buildWhereClause(string $table, string $primaryKeyField, array $primaryKeys): string
267	{
268	if (empty($primaryKeys)) {	2✔
NEW 269	return '1=0'; // No results	×
270	}
271
272	$placeholders = str_repeat('?,', count($primaryKeys) - 1) . '?';	2✔
273	return "s.`{$primaryKeyField}` IN ({$placeholders})";	2✔
274	}
275
276	/**
277	* Get table name for a relationship
278	*
279	* @param object $relationship The relationship instance
280	* @param string $type 'source' or 'related'
281	* @return string Table name
282	*/
283	private function getTableName($relationship, string $type): string
284	{
285	// This is a simplified implementation
286	// In practice, we'd get this from the model's mapper
287	if ($type === 'source') {	2✔
288	return 'users'; // Default assumption	2✔
289	} else {
290	$relatedClass = $relationship->getRelatedModelClass();	2✔
291	// Convert class name to table name (simplified)
292	return strtolower(str_replace('Model', 's', $relatedClass));	2✔
293	}
294	}
295
296	/**
297	* Check if a row contains only null values (LEFT JOIN with no match)
298	*
299	* @param array $row Database row
300	* @return bool True if row is empty/null
301	*/
302	private function isEmptyRelatedRow(array $row): bool
303	{
304	foreach ($row as $value) {	2✔
305	if ($value !== null) {	2✔
306	return false;	2✔
307	}
308	}
309	return true;	2✔
310	}
311
312	/**
313	* Check if this loader can handle a specific relationship type
314	*
315	* @param object $relationship The relationship instance
316	* @return bool True if this loader can handle the relationship
317	*/
318	public function canHandle($relationship): bool
319	{
320	// JOIN strategy can handle all relationship types
321	return true;	1✔
322	}
323
324	/**
325	* Estimate the number of queries this strategy will execute
326	*
327	* @param int $sourceCount Number of source models
328	* @return int Estimated query count
329	*/
330	public function estimateQueryCount(int $sourceCount): int
331	{
332	// JOIN strategy always uses exactly 1 query regardless of source count
333	return $sourceCount > 0 ? 1 : 0;	1✔
334	}
335
336	/**
337	* Get the maximum batch size this loader can handle efficiently
338	*
339	* @return int Maximum batch size
340	*/
341	public function getMaxBatchSize(): int
342	{
343	// JOIN strategy can handle very large batches efficiently
344	return 10000;	1✔
345	}
346	}

saygoweb / anorm / 18483557340

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous