18482579375

Committed 14 Oct 2025 01:22AM UTC coverage: 84.134% (+3.7%) from 80.483%

Build # 18482579375

Build Type

Pull #42

github

Committed by

web-flow

Commit Message

Merge f81a33b4f into 025025bcf

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

Run Details

833 of 948 new or added lines in 19 files covered. (87.87%)

75 existing lines in 4 files now uncovered.

1628 of 1935 relevant lines covered (84.13%)

15.51 hits per line

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

41.94

/src/Relationship/BatchLoader/ManyHasManyBatchLoader.php

<?php

namespace Anorm\Relationship\BatchLoader;

/**
 * Batch loader for Many-to-Many relationships (hasManyThrough)
 *
 * Optimizes loading of many-to-many relationships by collecting all primary keys
 * and executing a single complex JOIN query through the pivot table instead of N individual queries.
 */
class ManyHasManyBatchLoader implements BatchLoaderInterface
{
    /**
     * Load many-to-many relationships for multiple source models in a single batch
     *
     * @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 primary key
     */
    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");
        }

        // Collect all primary key values from source models
        $primaryKeys = [];
        foreach ($sourceModels as $model) {
            $primaryKeyValue = $model->{$relationship->getPrimaryKey()};
            if ($primaryKeyValue !== null) {
                $primaryKeys[] = $primaryKeyValue;
            }
        }

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

        // Remove duplicates and prepare for IN clause
        $primaryKeys = array_values(array_unique($primaryKeys));

        // Create an instance of the related model to get its mapper
        $relatedClass = $relationship->getRelatedModelClass();
        $relatedInstance = new $relatedClass($firstModel->getPdo());
        $mapper = $relatedInstance->_mapper;

        // Build the complex JOIN query through the pivot table
        $placeholders = str_repeat('?,', count($primaryKeys) - 1) . '?';

        // Handle field selection (basic implementation for now)
        $selectClause = 'r.*';
        if ($fieldSelection && !empty($fieldSelection)) {
            // For now, still select all fields to avoid parameter binding issues
            // Field selection optimization will be implemented in Phase 3
            $selectClause = 'r.*';
        }

        $sql = "SELECT {$selectClause}, j.`{$relationship->getJoinForeignKey()}` as source_id
                FROM `{$mapper->table}` r
                INNER JOIN `{$relationship->getJoinTable()}` j ON r.`{$relationship->getPrimaryKey()}` = j.`{$relationship->getJoinRelatedKey()}`
                WHERE j.`{$relationship->getJoinForeignKey()}` IN ({$placeholders})";

        // Execute the batch query
        $result = $mapper->query($sql, $primaryKeys);

        // Group results by source primary key
        $groupedResults = [];
        while ($data = $result->fetch(\PDO::FETCH_ASSOC)) {
            $sourceId = $data['source_id'];

            // Remove the source_id from the data before creating the model
            unset($data['source_id']);

            // Create related model instance
            $relatedModel = new $relatedClass($firstModel->getPdo());
            $relatedModel->_mapper->readArray($relatedModel, $data);

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

        return $groupedResults;
    }

    /**
     * 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 primary key
     * @param string $relationshipName Name of the relationship being distributed
     * @return void
     */
    public function distributeBatchResults(array $sourceModels, array $batchResults, string $relationshipName): void
    {
        // Handle empty source models
        if (empty($sourceModels)) {
            return;
        }

        // Get relationship definition to access primary key
        $firstModel = reset($sourceModels);
        $relationshipManager = $firstModel->getRelationshipManager();
        $relationship = $relationshipManager->getRelationship($relationshipName);

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

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

            // Assign the related models array to the model property
            if (isset($batchResults[$primaryKeyValue])) {
                $model->{$relationshipName} = $batchResults[$primaryKeyValue];
            } else {
                // No related models found - assign empty array
                $model->{$relationshipName} = [];
            }
        }
    }

    /**
     * Estimate the number of queries this batch loader would execute
     *
     * @param int $sourceCount Number of source models
     * @return int Number of queries (always 1 for batch loading)
     */
    public function estimateQueryCount(int $sourceCount): int
    {
        return $sourceCount > 0 ? 1 : 0;
    }

    /**
     * Check if this batch loader can handle the given relationship
     *
     * @param object $relationship The relationship to check
     * @return bool True if this loader can handle the relationship
     */
    public function canHandle($relationship): bool
    {
        return $relationship->getType() === 'manyHasMany';
    }

    /**
     * Get the maximum recommended batch size for this loader
     *
     * @return int Maximum number of source models to process in one batch
     */
    public function getMaxBatchSize(): int
    {
        // More conservative limit for many-to-many due to potential result explosion
        return 500;
    }

    /**
     * Estimate the complexity of the many-to-many relationship
     *
     * @param array $sourceModels Source models to analyze
     * @param string $relationshipName Name of the relationship
     * @return array Complexity metrics
     */
    public function estimateComplexity(array $sourceModels, string $relationshipName): array
    {
        $sourceCount = count($sourceModels);

        // Estimate based on typical many-to-many patterns
        $estimatedRelatedPerSource = 3; // Conservative estimate
        $estimatedTotalRelated = $sourceCount * $estimatedRelatedPerSource;

        return [
            'source_count' => $sourceCount,
            'estimated_related_per_source' => $estimatedRelatedPerSource,
            'estimated_total_related' => $estimatedTotalRelated,
            'complexity_score' => min($estimatedTotalRelated / 100, 10), // Scale 0-10
            'recommended_batch_size' => min($this->getMaxBatchSize(), max(50, 1000 / $estimatedRelatedPerSource))
        ];
    }
}

1	<?php
2
3	namespace Anorm\Relationship\BatchLoader;
4
5	/**
6	* Batch loader for Many-to-Many relationships (hasManyThrough)
7	*
8	* Optimizes loading of many-to-many relationships by collecting all primary keys
9	* and executing a single complex JOIN query through the pivot table instead of N individual queries.
10	*/
11	class ManyHasManyBatchLoader implements BatchLoaderInterface
12	{
13	/**
14	* Load many-to-many relationships for multiple source models in a single batch
15	*
16	* @param array $sourceModels Array of model instances that need relationships loaded
17	* @param string $relationshipName Name of the relationship to load
18	* @param array\|null $fieldSelection Optional field selection for optimization
19	* @return array Associative array of loaded relationship data, keyed by source model primary key
20	*/
21	public function batchLoad(array $sourceModels, string $relationshipName, ?array $fieldSelection = null): array
22	{
23	if (empty($sourceModels)) {	3✔
24	return [];	1✔
25	}
26
27	// Get relationship definition from the first model
28	$firstModel = reset($sourceModels);	2✔
29	$relationshipManager = $firstModel->getRelationshipManager();	2✔
30	$relationship = $relationshipManager->getRelationship($relationshipName);	2✔
31
32	if (!$relationship) {	2✔
33	throw new \Exception("Relationship '{$relationshipName}' not defined");	2✔
34	}
35
36	// Collect all primary key values from source models
NEW 37	$primaryKeys = [];	×
NEW 38	foreach ($sourceModels as $model) {	×
NEW 39	$primaryKeyValue = $model->{$relationship->getPrimaryKey()};	×
NEW 40	if ($primaryKeyValue !== null) {	×
NEW 41	$primaryKeys[] = $primaryKeyValue;	×
42	}
43	}
44
NEW 45	if (empty($primaryKeys)) {	×
NEW 46	return [];	×
47	}
48
49	// Remove duplicates and prepare for IN clause
NEW 50	$primaryKeys = array_values(array_unique($primaryKeys));	×
51
52	// Create an instance of the related model to get its mapper
NEW 53	$relatedClass = $relationship->getRelatedModelClass();	×
NEW 54	$relatedInstance = new $relatedClass($firstModel->getPdo());	×
NEW 55	$mapper = $relatedInstance->_mapper;	×
56
57	// Build the complex JOIN query through the pivot table
NEW 58	$placeholders = str_repeat('?,', count($primaryKeys) - 1) . '?';	×
59
60	// Handle field selection (basic implementation for now)
NEW 61	$selectClause = 'r.*';	×
NEW 62	if ($fieldSelection && !empty($fieldSelection)) {	×
63	// For now, still select all fields to avoid parameter binding issues
64	// Field selection optimization will be implemented in Phase 3
NEW 65	$selectClause = 'r.*';	×
66	}
67
NEW 68	$sql = "SELECT {$selectClause}, j.`{$relationship->getJoinForeignKey()}` as source_id	×
NEW 69	FROM `{$mapper->table}` r	×
NEW 70	INNER JOIN `{$relationship->getJoinTable()}` j ON r.`{$relationship->getPrimaryKey()}` = j.`{$relationship->getJoinRelatedKey()}`	×
NEW 71	WHERE j.`{$relationship->getJoinForeignKey()}` IN ({$placeholders})";	×
72
73	// Execute the batch query
NEW 74	$result = $mapper->query($sql, $primaryKeys);	×
75
76	// Group results by source primary key
NEW 77	$groupedResults = [];	×
NEW 78	while ($data = $result->fetch(\PDO::FETCH_ASSOC)) {	×
NEW 79	$sourceId = $data['source_id'];	×
80
81	// Remove the source_id from the data before creating the model
NEW 82	unset($data['source_id']);	×
83
84	// Create related model instance
NEW 85	$relatedModel = new $relatedClass($firstModel->getPdo());	×
NEW 86	$relatedModel->_mapper->readArray($relatedModel, $data);	×
87
88	// Group by source primary key
NEW 89	if (!isset($groupedResults[$sourceId])) {	×
NEW 90	$groupedResults[$sourceId] = [];	×
91	}
NEW 92	$groupedResults[$sourceId][] = $relatedModel;	×
93	}
94
NEW 95	return $groupedResults;	×
96	}
97
98	/**
99	* Distribute batch-loaded results to their corresponding source models
100	*
101	* @param array $sourceModels Array of model instances to receive the loaded data
102	* @param array $batchResults Results from batchLoad(), keyed by source model primary key
103	* @param string $relationshipName Name of the relationship being distributed
104	* @return void
105	*/
106	public function distributeBatchResults(array $sourceModels, array $batchResults, string $relationshipName): void
107	{
108	// Handle empty source models
109	if (empty($sourceModels)) {	2✔
NEW 110	return;	×
111	}
112
113	// Get relationship definition to access primary key
114	$firstModel = reset($sourceModels);	2✔
115	$relationshipManager = $firstModel->getRelationshipManager();	2✔
116	$relationship = $relationshipManager->getRelationship($relationshipName);	2✔
117
118	if (!$relationship) {	2✔
119	return; // Relationship not found, skip distribution	2✔
120	}
121
NEW 122	foreach ($sourceModels as $model) {	×
NEW 123	$primaryKeyValue = $model->{$relationship->getPrimaryKey()};	×
124
125	// Assign the related models array to the model property
NEW 126	if (isset($batchResults[$primaryKeyValue])) {	×
NEW 127	$model->{$relationshipName} = $batchResults[$primaryKeyValue];	×
128	} else {
129	// No related models found - assign empty array
NEW 130	$model->{$relationshipName} = [];	×
131	}
132	}
133	}
134
135	/**
136	* Estimate the number of queries this batch loader would execute
137	*
138	* @param int $sourceCount Number of source models
139	* @return int Number of queries (always 1 for batch loading)
140	*/
141	public function estimateQueryCount(int $sourceCount): int
142	{
143	return $sourceCount > 0 ? 1 : 0;	1✔
144	}
145
146	/**
147	* Check if this batch loader can handle the given relationship
148	*
149	* @param object $relationship The relationship to check
150	* @return bool True if this loader can handle the relationship
151	*/
152	public function canHandle($relationship): bool
153	{
154	return $relationship->getType() === 'manyHasMany';	1✔
155	}
156
157	/**
158	* Get the maximum recommended batch size for this loader
159	*
160	* @return int Maximum number of source models to process in one batch
161	*/
162	public function getMaxBatchSize(): int
163	{
164	// More conservative limit for many-to-many due to potential result explosion
165	return 500;	2✔
166	}
167
168	/**
169	* Estimate the complexity of the many-to-many relationship
170	*
171	* @param array $sourceModels Source models to analyze
172	* @param string $relationshipName Name of the relationship
173	* @return array Complexity metrics
174	*/
175	public function estimateComplexity(array $sourceModels, string $relationshipName): array
176	{
177	$sourceCount = count($sourceModels);	1✔
178
179	// Estimate based on typical many-to-many patterns
180	$estimatedRelatedPerSource = 3; // Conservative estimate	1✔
181	$estimatedTotalRelated = $sourceCount * $estimatedRelatedPerSource;	1✔
182
183	return [	1✔
184	'source_count' => $sourceCount,	1✔
185	'estimated_related_per_source' => $estimatedRelatedPerSource,	1✔
186	'estimated_total_related' => $estimatedTotalRelated,	1✔
187	'complexity_score' => min($estimatedTotalRelated / 100, 10), // Scale 0-10	1✔
188	'recommended_batch_size' => min($this->getMaxBatchSize(), max(50, 1000 / $estimatedRelatedPerSource))	1✔
189	];	1✔
190	}
191	}

saygoweb / anorm / 18482579375

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