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

53.13

/src/Relationship/ManyHasMany.php

<?php

namespace Anorm\Relationship;

use Anorm\DataMapper;
use Anorm\Relationship\BatchLoader\ManyHasManyBatchLoader;

/**
 * Many-to-Many relationship
 * This model has many instances of another model through a join table
 */
class ManyHasMany extends Relationship
{
    /** @var string The join table name */
    protected $joinTable;

    /** @var string The foreign key in the join table for this model */
    protected $joinForeignKey;

    /** @var string The foreign key in the join table for the related model */
    protected $joinRelatedKey;

    public function __construct($relatedModelClass, $propertyName, $joinForeignKey, $joinRelatedKey, $joinTable, $primaryKey = 'id', $options = [])
    {
        parent::__construct($relatedModelClass, $propertyName, $joinForeignKey, $primaryKey, $options);
        $this->joinTable = $joinTable;
        $this->joinForeignKey = $joinForeignKey;
        $this->joinRelatedKey = $joinRelatedKey;
    }

    /**
     * Get the relationship type
     */
    public function getType()
    {
        return 'manyHasMany';
    }

    /**
     * Get the join table name
     */
    public function getJoinTable()
    {
        return $this->joinTable;
    }

    /**
     * Get the foreign key in the join table for this model
     */
    public function getJoinForeignKey()
    {
        return $this->joinForeignKey;
    }

    /**
     * Get the foreign key in the join table for the related model
     */
    public function getJoinRelatedKey()
    {
        return $this->joinRelatedKey;
    }

    /**
     * Load the related models for a many-to-many relationship
     *
     * @param object $sourceModel The model instance that owns the relationship
     * @param \PDO $pdo The database connection
     * @return array Array of related model instances
     */
    public function load($sourceModel, \PDO $pdo)
    {
        $relatedClass = $this->relatedModelClass;
        $sourceValue = $sourceModel->{$this->primaryKey};

        if ($sourceValue === null) {
            return [];
        }

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

        // Build the query to find related models through the join table
        // Use database column names directly
        $sql = "SELECT r.* FROM `{$mapper->table}` r
                INNER JOIN `{$this->joinTable}` j ON r.`{$this->primaryKey}` = j.`{$this->joinRelatedKey}`
                WHERE j.`{$this->joinForeignKey}` = ?";

        $result = $mapper->query($sql, [$sourceValue]);
        $relatedModels = [];

        while ($data = $result->fetch(\PDO::FETCH_ASSOC)) {
            $relatedModel = new $relatedClass($pdo);
            $relatedModel->_mapper->readArray($relatedModel, $data);
            $relatedModels[] = $relatedModel;
        }

        return $relatedModels;
    }

    /**
     * Generate JOIN clause for many-to-many relationship
     *
     * @param string $sourceTable The source table name
     * @param string $relatedTable The related table name
     * @return string The JOIN clause
     */
    public function generateJoinClause($sourceTable, $relatedTable)
    {
        return "LEFT JOIN `{$this->joinTable}` ON `{$sourceTable}`.`{$this->primaryKey}` = `{$this->joinTable}`.`{$this->joinForeignKey}` " .
               "LEFT JOIN `{$relatedTable}` ON `{$this->joinTable}`.`{$this->joinRelatedKey}` = `{$relatedTable}`.`{$this->primaryKey}`";
    }

    /**
     * Generate foreign key constraint SQL for ManyHasMany relationship
     * Creates foreign keys on the join table pointing to both source and target tables
     */
    public function generateForeignKeyConstraints($sourceTable)
    {
        $targetTable = $this->getTableNameFromModelClass($this->relatedModelClass);
        $constraints = [];

        // Foreign key from join table to source table
        $sourceConstraintName = "fk_{$this->joinTable}_{$this->joinForeignKey}";
        $onDelete = $this->constraintOptions['on_delete'];
        $onUpdate = $this->constraintOptions['on_update'];

        $constraints[] = "ALTER TABLE `{$this->joinTable}`
                         ADD CONSTRAINT `{$sourceConstraintName}`
                         FOREIGN KEY (`{$this->joinForeignKey}`)
                         REFERENCES `{$sourceTable}`(`{$this->primaryKey}`)
                         ON DELETE {$onDelete}
                         ON UPDATE {$onUpdate}";

        // Foreign key from join table to target table
        $targetConstraintName = "fk_{$this->joinTable}_{$this->joinRelatedKey}";

        $constraints[] = "ALTER TABLE `{$this->joinTable}`
                         ADD CONSTRAINT `{$targetConstraintName}`
                         FOREIGN KEY (`{$this->joinRelatedKey}`)
                         REFERENCES `{$targetTable}`(`{$this->primaryKey}`)
                         ON DELETE {$onDelete}
                         ON UPDATE {$onUpdate}";

        return $constraints;
    }

    /**
     * Generate SQL to create the join table if it doesn't exist
     */
    public function generateJoinTableSQL($sourceTable)
    {
        $targetTable = $this->getTableNameFromModelClass($this->relatedModelClass);

        $sql = "CREATE TABLE IF NOT EXISTS `{$this->joinTable}` (
                    `{$this->joinForeignKey}` INT(11) NOT NULL,
                    `{$this->joinRelatedKey}` INT(11) NOT NULL,
                    PRIMARY KEY (`{$this->joinForeignKey}`, `{$this->joinRelatedKey}`),
                    INDEX `idx_{$this->joinTable}_{$this->joinForeignKey}` (`{$this->joinForeignKey}`),
                    INDEX `idx_{$this->joinTable}_{$this->joinRelatedKey}` (`{$this->joinRelatedKey}`)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";

        return $sql;
    }

    /**
     * Load relationships for multiple source models in a single batch operation
     *
     * @param array $sourceModels Array of model instances that need relationships loaded
     * @param \PDO $pdo Database connection
     * @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, \PDO $pdo, ?array $fieldSelection = null): array
    {
        $batchLoader = new ManyHasManyBatchLoader();
        return $batchLoader->batchLoad($sourceModels, $this->propertyName, $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
     * @return void
     */
    public function distributeBatchResults(array $sourceModels, array $batchResults): void
    {
        $batchLoader = new ManyHasManyBatchLoader();
        $batchLoader->distributeBatchResults($sourceModels, $batchResults, $this->propertyName);
    }

    /**
     * Estimate the data size for this relationship with given parameters
     *
     * @param int $sourceCount Number of source models
     * @param array|null $fieldSelection Specific fields to load, or null for all fields
     * @return int Estimated data size in bytes
     */
    public function estimateDataSize(int $sourceCount, ?array $fieldSelection = null): int
    {
        // Estimate based on many-to-many cardinality
        $avgRelatedRecords = 3; // Conservative estimate for many-to-many
        $avgRecordSize = 1024; // 1KB per record estimate

        if ($fieldSelection !== null && !empty($fieldSelection)) {
            // Estimate size for selected fields only
            $avgRecordSize = count($fieldSelection) * 50; // 50 bytes per field estimate
        }

        return (int) ($sourceCount * $avgRelatedRecords * $avgRecordSize);
    }

    /**
     * Get the cardinality type of this relationship
     *
     * @return string One of: 'one-to-one', 'one-to-many', 'many-to-one', 'many-to-many'
     */
    public function getCardinality(): string
    {
        return 'many-to-many';
    }
}

1	<?php
2
3	namespace Anorm\Relationship;
4
5	use Anorm\DataMapper;
6	use Anorm\Relationship\BatchLoader\ManyHasManyBatchLoader;
7
8	/**
9	* Many-to-Many relationship
10	* This model has many instances of another model through a join table
11	*/
12	class ManyHasMany extends Relationship
13	{
14	/** @var string The join table name */
15	protected $joinTable;
16
17	/** @var string The foreign key in the join table for this model */
18	protected $joinForeignKey;
19
20	/** @var string The foreign key in the join table for the related model */
21	protected $joinRelatedKey;
22
23	public function __construct($relatedModelClass, $propertyName, $joinForeignKey, $joinRelatedKey, $joinTable, $primaryKey = 'id', $options = [])
24	{
25	parent::__construct($relatedModelClass, $propertyName, $joinForeignKey, $primaryKey, $options);	57✔
26	$this->joinTable = $joinTable;	57✔
27	$this->joinForeignKey = $joinForeignKey;	57✔
28	$this->joinRelatedKey = $joinRelatedKey;	57✔
29	}
30
31	/**
32	* Get the relationship type
33	*/
34	public function getType()
35	{
36	return 'manyHasMany';	3✔
37	}
38
39	/**
40	* Get the join table name
41	*/
42	public function getJoinTable()
43	{
44	return $this->joinTable;	2✔
45	}
46
47	/**
48	* Get the foreign key in the join table for this model
49	*/
50	public function getJoinForeignKey()
51	{
52	return $this->joinForeignKey;	2✔
53	}
54
55	/**
56	* Get the foreign key in the join table for the related model
57	*/
58	public function getJoinRelatedKey()
59	{
60	return $this->joinRelatedKey;	2✔
61	}
62
63	/**
64	* Load the related models for a many-to-many relationship
65	*
66	* @param object $sourceModel The model instance that owns the relationship
67	* @param \PDO $pdo The database connection
68	* @return array Array of related model instances
69	*/
70	public function load($sourceModel, \PDO $pdo)
71	{
72	$relatedClass = $this->relatedModelClass;	2✔
73	$sourceValue = $sourceModel->{$this->primaryKey};	2✔
74
75	if ($sourceValue === null) {	2✔
76	return [];	×
77	}
78
79	// Create an instance of the related model to get its mapper
80	$relatedInstance = new $relatedClass($pdo);	2✔
81	$mapper = $relatedInstance->_mapper;	2✔
82
83	// Build the query to find related models through the join table
84	// Use database column names directly
85	$sql = "SELECT r.* FROM `{$mapper->table}` r	2✔
86	INNER JOIN `{$this->joinTable}` j ON r.`{$this->primaryKey}` = j.`{$this->joinRelatedKey}`	2✔
87	WHERE j.`{$this->joinForeignKey}` = ?";	2✔
88
89	$result = $mapper->query($sql, [$sourceValue]);	2✔
90	$relatedModels = [];	2✔
91
92	while ($data = $result->fetch(\PDO::FETCH_ASSOC)) {	2✔
93	$relatedModel = new $relatedClass($pdo);	1✔
94	$relatedModel->_mapper->readArray($relatedModel, $data);	1✔
95	$relatedModels[] = $relatedModel;	1✔
96	}
97
98	return $relatedModels;	2✔
99	}
100
101	/**
102	* Generate JOIN clause for many-to-many relationship
103	*
104	* @param string $sourceTable The source table name
105	* @param string $relatedTable The related table name
106	* @return string The JOIN clause
107	*/
108	public function generateJoinClause($sourceTable, $relatedTable)
109	{
110	return "LEFT JOIN `{$this->joinTable}` ON `{$sourceTable}`.`{$this->primaryKey}` = `{$this->joinTable}`.`{$this->joinForeignKey}` " .	1✔
111	"LEFT JOIN `{$relatedTable}` ON `{$this->joinTable}`.`{$this->joinRelatedKey}` = `{$relatedTable}`.`{$this->primaryKey}`";	1✔
112	}
113
114	/**
115	* Generate foreign key constraint SQL for ManyHasMany relationship
116	* Creates foreign keys on the join table pointing to both source and target tables
117	*/
118	public function generateForeignKeyConstraints($sourceTable)
119	{
UNCOV 120	$targetTable = $this->getTableNameFromModelClass($this->relatedModelClass);	×
121	$constraints = [];	×
122
123	// Foreign key from join table to source table
UNCOV 124	$sourceConstraintName = "fk_{$this->joinTable}_{$this->joinForeignKey}";	×
125	$onDelete = $this->constraintOptions['on_delete'];	×
126	$onUpdate = $this->constraintOptions['on_update'];	×
127
UNCOV 128	$constraints[] = "ALTER TABLE `{$this->joinTable}`	×
129	ADD CONSTRAINT `{$sourceConstraintName}`	×
130	FOREIGN KEY (`{$this->joinForeignKey}`)	×
131	REFERENCES `{$sourceTable}`(`{$this->primaryKey}`)	×
132	ON DELETE {$onDelete}	×
133	ON UPDATE {$onUpdate}";	×
134
135	// Foreign key from join table to target table
UNCOV 136	$targetConstraintName = "fk_{$this->joinTable}_{$this->joinRelatedKey}";	×
137
UNCOV 138	$constraints[] = "ALTER TABLE `{$this->joinTable}`	×
139	ADD CONSTRAINT `{$targetConstraintName}`	×
140	FOREIGN KEY (`{$this->joinRelatedKey}`)	×
141	REFERENCES `{$targetTable}`(`{$this->primaryKey}`)	×
142	ON DELETE {$onDelete}	×
143	ON UPDATE {$onUpdate}";	×
144
UNCOV 145	return $constraints;	×
146	}
147
148	/**
149	* Generate SQL to create the join table if it doesn't exist
150	*/
151	public function generateJoinTableSQL($sourceTable)
152	{
153	$targetTable = $this->getTableNameFromModelClass($this->relatedModelClass);	1✔
154
155	$sql = "CREATE TABLE IF NOT EXISTS `{$this->joinTable}` (	1✔
156	`{$this->joinForeignKey}` INT(11) NOT NULL,	1✔
157	`{$this->joinRelatedKey}` INT(11) NOT NULL,	1✔
158	PRIMARY KEY (`{$this->joinForeignKey}`, `{$this->joinRelatedKey}`),	1✔
159	INDEX `idx_{$this->joinTable}_{$this->joinForeignKey}` (`{$this->joinForeignKey}`),	1✔
160	INDEX `idx_{$this->joinTable}_{$this->joinRelatedKey}` (`{$this->joinRelatedKey}`)	1✔
161	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";	1✔
162
163	return $sql;	1✔
164	}
165
166	/**
167	* Load relationships for multiple source models in a single batch operation
168	*
169	* @param array $sourceModels Array of model instances that need relationships loaded
170	* @param \PDO $pdo Database connection
171	* @param array\|null $fieldSelection Optional field selection for optimization
172	* @return array Associative array of loaded relationship data, keyed by source model identifier
173	*/
174	public function batchLoad(array $sourceModels, \PDO $pdo, ?array $fieldSelection = null): array
175	{
NEW 176	$batchLoader = new ManyHasManyBatchLoader();	×
NEW 177	return $batchLoader->batchLoad($sourceModels, $this->propertyName, $fieldSelection);	×
178	}
179
180	/**
181	* Distribute batch-loaded results to their corresponding source models
182	*
183	* @param array $sourceModels Array of model instances to receive the loaded data
184	* @param array $batchResults Results from batchLoad(), keyed by source model identifier
185	* @return void
186	*/
187	public function distributeBatchResults(array $sourceModels, array $batchResults): void
188	{
NEW 189	$batchLoader = new ManyHasManyBatchLoader();	×
NEW 190	$batchLoader->distributeBatchResults($sourceModels, $batchResults, $this->propertyName);	×
191	}
192
193	/**
194	* Estimate the data size for this relationship with given parameters
195	*
196	* @param int $sourceCount Number of source models
197	* @param array\|null $fieldSelection Specific fields to load, or null for all fields
198	* @return int Estimated data size in bytes
199	*/
200	public function estimateDataSize(int $sourceCount, ?array $fieldSelection = null): int
201	{
202	// Estimate based on many-to-many cardinality
NEW 203	$avgRelatedRecords = 3; // Conservative estimate for many-to-many	×
NEW 204	$avgRecordSize = 1024; // 1KB per record estimate	×
205
NEW 206	if ($fieldSelection !== null && !empty($fieldSelection)) {	×
207	// Estimate size for selected fields only
NEW 208	$avgRecordSize = count($fieldSelection) * 50; // 50 bytes per field estimate	×
209	}
210
NEW 211	return (int) ($sourceCount * $avgRelatedRecords * $avgRecordSize);	×
212	}
213
214	/**
215	* Get the cardinality type of this relationship
216	*
217	* @return string One of: 'one-to-one', 'one-to-many', 'many-to-one', 'many-to-many'
218	*/
219	public function getCardinality(): string
220	{
NEW 221	return 'many-to-many';	×
222	}
223	}

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