18482713611

Committed 14 Oct 2025 01:31AM UTC coverage: 84.126% (+3.6%) from 80.483%

Build # 18482713611

Build Type

Pull #42

github

Committed by

web-flow

Commit Message

Merge 6f692bbc5 into 025025bcf

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

Run Details

834 of 949 new or added lines in 19 files covered. (87.88%)

80 existing lines in 4 files now uncovered.

1627 of 1934 relevant lines covered (84.13%)

15.51 hits per line

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

52.38

/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}` LEFT JOIN `{$relatedTable}` ON `{$this->joinTable}`.`{$this->joinRelatedKey}` = `{$relatedTable}`.`{$this->primaryKey}`";	1✔
111	}
112
113	/**
114	* Generate foreign key constraint SQL for ManyHasMany relationship
115	* Creates foreign keys on the join table pointing to both source and target tables
116	*/
117	public function generateForeignKeyConstraints($sourceTable)
118	{
UNCOV 119	$targetTable = $this->getTableNameFromModelClass($this->relatedModelClass);	×
UNCOV 120	$constraints = [];	×
121
122	// Foreign key from join table to source table
UNCOV 123	$sourceConstraintName = "fk_{$this->joinTable}_{$this->joinForeignKey}";	×
UNCOV 124	$onDelete = $this->constraintOptions['on_delete'];	×
125	$onUpdate = $this->constraintOptions['on_update'];	×
126
127	$constraints[] = "ALTER TABLE `{$this->joinTable}`	×
UNCOV 128	ADD CONSTRAINT `{$sourceConstraintName}`	×
129	FOREIGN KEY (`{$this->joinForeignKey}`)	×
130	REFERENCES `{$sourceTable}`(`{$this->primaryKey}`)	×
131	ON DELETE {$onDelete}	×
132	ON UPDATE {$onUpdate}";	×
133
134	// Foreign key from join table to target table
UNCOV 135	$targetConstraintName = "fk_{$this->joinTable}_{$this->joinRelatedKey}";	×
136
137	$constraints[] = "ALTER TABLE `{$this->joinTable}`	×
UNCOV 138	ADD CONSTRAINT `{$targetConstraintName}`	×
139	FOREIGN KEY (`{$this->joinRelatedKey}`)	×
140	REFERENCES `{$targetTable}`(`{$this->primaryKey}`)	×
141	ON DELETE {$onDelete}	×
142	ON UPDATE {$onUpdate}";	×
143
144	return $constraints;	×
145	}
146
147	/**
148	* Generate SQL to create the join table if it doesn't exist
149	*/
150	public function generateJoinTableSQL($sourceTable)
151	{
152	$targetTable = $this->getTableNameFromModelClass($this->relatedModelClass);	1✔
153
154	$sql = "CREATE TABLE IF NOT EXISTS `{$this->joinTable}` (	1✔
155	`{$this->joinForeignKey}` INT(11) NOT NULL,	1✔
156	`{$this->joinRelatedKey}` INT(11) NOT NULL,	1✔
157	PRIMARY KEY (`{$this->joinForeignKey}`, `{$this->joinRelatedKey}`),	1✔
158	INDEX `idx_{$this->joinTable}_{$this->joinForeignKey}` (`{$this->joinForeignKey}`),	1✔
159	INDEX `idx_{$this->joinTable}_{$this->joinRelatedKey}` (`{$this->joinRelatedKey}`)	1✔
160	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4";	1✔
161
162	return $sql;	1✔
163	}
164
165	/**
166	* Load relationships for multiple source models in a single batch operation
167	*
168	* @param array $sourceModels Array of model instances that need relationships loaded
169	* @param \PDO $pdo Database connection
170	* @param array\|null $fieldSelection Optional field selection for optimization
171	* @return array Associative array of loaded relationship data, keyed by source model identifier
172	*/
173	public function batchLoad(array $sourceModels, \PDO $pdo, ?array $fieldSelection = null): array
174	{
NEW 175	$batchLoader = new ManyHasManyBatchLoader();	×
NEW 176	return $batchLoader->batchLoad($sourceModels, $this->propertyName, $fieldSelection);	×
177	}
178
179	/**
180	* Distribute batch-loaded results to their corresponding source models
181	*
182	* @param array $sourceModels Array of model instances to receive the loaded data
183	* @param array $batchResults Results from batchLoad(), keyed by source model identifier
184	* @return void
185	*/
186	public function distributeBatchResults(array $sourceModels, array $batchResults): void
187	{
NEW 188	$batchLoader = new ManyHasManyBatchLoader();	×
NEW 189	$batchLoader->distributeBatchResults($sourceModels, $batchResults, $this->propertyName);	×
190	}
191
192	/**
193	* Estimate the data size for this relationship with given parameters
194	*
195	* @param int $sourceCount Number of source models
196	* @param array\|null $fieldSelection Specific fields to load, or null for all fields
197	* @return int Estimated data size in bytes
198	*/
199	public function estimateDataSize(int $sourceCount, ?array $fieldSelection = null): int
200	{
201	// Estimate based on many-to-many cardinality
NEW 202	$avgRelatedRecords = 3; // Conservative estimate for many-to-many	×
NEW 203	$avgRecordSize = 1024; // 1KB per record estimate	×
204
NEW 205	if ($fieldSelection !== null && !empty($fieldSelection)) {	×
206	// Estimate size for selected fields only
NEW 207	$avgRecordSize = count($fieldSelection) * 50; // 50 bytes per field estimate	×
208	}
209
NEW 210	return (int) ($sourceCount * $avgRelatedRecords * $avgRecordSize);	×
211	}
212
213	/**
214	* Get the cardinality type of this relationship
215	*
216	* @return string One of: 'one-to-one', 'one-to-many', 'many-to-one', 'many-to-many'
217	*/
218	public function getCardinality(): string
219	{
NEW 220	return 'many-to-many';	×
221	}
222	}

saygoweb / anorm / 18482713611

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