Doctrinetemptable Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require alexjumperman/doctrinetemptable
   

   Ensure your composer.json includes doctrine/orm:^2.5 and doctrine/doctrine-bundle:^1.8.

2. Enable the Bundle (if using Symfony): Add new AlexJumperman\TempTableBundle\AlexJumpermanTempTableBundle() to config/bundles.php.

3. First Use Case: Create a repository trait or service factory integration in a controller to filter a large dataset (e.g., 1M products) into a temporary table for efficient querying.

Implementation Patterns

Repository Trait Integration

1. Extend Base Repository:

   use AlexJumperman\TempTableBundle\Utils\TempTableTrait;
   
   class ProductRepository extends \Doctrine\ORM\EntityRepository
   {
       use TempTableTrait;
   }
   

   • Use createQueryBuilderForTempTable($alias) to generate a query builder for temporary tables.

2. Controller Workflow:

   $qb = $this->getDoctrine()
       ->getRepository(Product::class)
       ->createQueryBuilderForTempTable('p')
       ->where('p.category_id = :category')
       ->setParameter('category', 1);
   

3. Create Temp Table Repository:

   $tempRepo = $qb->createTempTableRepository('temp_products');
   

   • Use $tempRepo for lightweight queries (e.g., counts, filtered results) without hitting the main table.

Service Factory Pattern

1. Inject Factory:

   use AlexJumperman\TempTableBundle\Factory\TempTableFactory;
   
   public function __construct(TempTableFactory $tempTableFactory) {
       $this->tempTableFactory = $tempTableFactory;
   }
   

2. Usage:

   $qb = $this->tempTableFactory->createQueryBuilderForTempTable(
       $this->getDoctrine()->getRepository(Product::class),
       'p'
   );
   

Query Optimization Patterns

• Filter Early: Apply WHERE clauses in the temp table query builder to reduce dataset size before materializing the temp table.
• Batch Processing: Use setMaxResults() and pagination on the temp table for large result sets.
• Aggregations: Leverage temp tables for COUNT, SUM, or GROUP BY operations to avoid expensive joins on the main table.

Gotchas and Tips

Pitfalls

1. Connection Handling:

   • Temp tables are database-specific (e.g., #temp in MySQL, temp schema in PostgreSQL). Ensure your DBMS supports them.
   • Debugging: Use $tempRepo->getEntityManager()->getConnection()->fetchAll('SHOW TABLES') (MySQL) to verify temp tables exist.

2. Transaction Scope:

   • Temp tables are tied to the current database connection/transaction. Close connections or roll back transactions to drop them automatically.
   • Workaround: Explicitly drop temp tables with:

     $tempRepo->getEntityManager()->getConnection()->executeStatement('DROP TABLE IF EXISTS temp_products');
     

3. Performance Caveats:

   • Indexing: Temp tables lack indexes by default. For large datasets, manually add indexes:

     $qb->createTempTableRepository('temp_products')->addIndex('idx_price', 'price');
     

   • Memory Usage: Materializing 1M rows into a temp table consumes memory. Test with smaller datasets first.

Debugging Tips

1. Query Inspection:

   • Use getSQL() on the temp table query builder to verify generated SQL:

     $sql = $qb->select('p.id')->getSQL();
     

2. Logging:

   • Enable Doctrine logging to trace temp table creation:

     # config/packages/doctrine.yaml
     doctrine:
         dbal:
             logging: true
             profiling: true
     

3. Configuration Quirks:

   • Bundle Auto-Registration: If using Symfony Flex, ensure the bundle is loaded in config/bundles.php. For manual setups, register it in AppKernel.php.

Extension Points

1. Custom Temp Table Naming:

   • Override the default naming strategy by extending TempTableTrait and implementing getTempTableName():

     protected function getTempTableName(): string
     {
         return 'custom_temp_' . uniqid();
     }
     

2. Schema Customization:

   • Extend TempTableRepository to add custom columns or constraints:

     $tempRepo = $qb->createTempTableRepository('temp_products', [
         'additional_column' => 'VARCHAR(255)'
     ]);
     

3. Integration with Caching:

   • Cache temp table results for read-heavy scenarios using Symfony’s cache system or Redis:

     $cacheKey = 'temp_products_' . md5($qb->getSQL());
     $cachedResults = $this->cache->get($cacheKey, function() use ($tempRepo) {
         return $tempRepo->findAll();
     });