Eloquent Has By Non Dependent Subquery Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require mpyw/eloquent-has-by-non-dependent-subquery
   

   No additional configuration is required—just use it with Eloquent queries.

2. First Use Case: Replace a slow has() or whereHas() query with a non-dependent subquery for MySQL optimization:

   // Before (dependent subquery)
   User::whereHas('posts', function ($query) {
       $query->where('title', 'like', '%test%');
   })->get();
   
   // After (non-dependent subquery, optimized)
   User::hasByNonDependentSubquery('posts', function ($query) {
       $query->where('title', 'like', '%test%');
   })->get();
   

3. Where to Look First:

   • Check the README for MySQL-specific notes.
   • Use hasByNonDependentSubquery() or whereHasByNonDependentSubquery() as drop-in replacements for Eloquent’s built-in methods.

Implementation Patterns

Core Workflows

1. Replacing has():

   // Original
   User::has('posts')->get();
   
   // Optimized
   User::hasByNonDependentSubquery('posts')->get();
   

2. Replacing whereHas():

   // Original
   User::whereHas('posts', fn($q) => $q->where('published', true))->get();
   
   // Optimized
   User::whereHasByNonDependentSubquery('posts', fn($q) => $q->where('published', true))->get();
   

3. Combining with Other Constraints:

   User::where('active', true)
       ->whereHasByNonDependentSubquery('posts', fn($q) => $q->where('views', '>', 100))
       ->orderBy('created_at', 'desc')
       ->get();
   

4. Dynamic Relationships:

   $relation = 'posts';
   User::whereHasByNonDependentSubquery($relation, fn($q) => $q->where('title', 'like', '%'.$search.'%'))->get();
   

Integration Tips

• Use in Repositories: Replace has()/whereHas() calls in repository methods with the optimized versions.

  public function findActiveUsersWithPublishedPosts()
  {
      return User::where('active', true)
          ->whereHasByNonDependentSubquery('posts', fn($q) => $q->where('published', true))
          ->get();
  }
  

• API Controllers: Apply optimizations in query scopes or controller logic:

  public function index(Request $request)
  {
      $query = User::query();
      if ($request->has('search')) {
          $query->whereHasByNonDependentSubquery('posts', fn($q) => $q->where('title', 'like', '%'.$request->search.'%'));
      }
      return $query->get();
  }
  

• Service Layer: Encapsulate optimized queries in services for reusability:

  class UserService {
      public function getUsersWithRecentPosts(int $days = 7)
      {
          return User::whereHasByNonDependentSubquery('posts', fn($q) =>
              $q->where('created_at', '>', now()->subDays($days))
          )->get();
      }
  }
  

Gotchas and Tips

Pitfalls

1. MySQL-Specific Optimization:

   • Only use this for MySQL (not PostgreSQL or SQLite). PostgreSQL’s optimizer already handles dependent subqueries efficiently.
   • MySQL 8.0.16+: The package is no longer maintained due to MySQL’s improved optimizer. Test performance gains in your specific version.

2. Query Builder Limitations:

   • Nested whereHas() calls may not work as expected. Flatten logic where possible.
   • Complex joins or subqueries inside whereHasByNonDependentSubquery() might not convert correctly.

3. Debugging:

   • Use toSql() and toSql() with bindings() to verify generated SQL:

     $query = User::whereHasByNonDependentSubquery('posts', fn($q) => $q->where('title', 'like', '%test%'));
     dd($query->toSql(), $query->getBindings());
     

   • Compare output with the original has()/whereHas() to ensure correctness.

4. Performance Caveats:

   • Not a silver bullet: Test with EXPLAIN to confirm improvements. Some queries may perform worse due to subquery overhead.
   • Indexing matters: Ensure related tables (e.g., posts) have proper indexes on filtered columns (e.g., title, published).

Tips

1. Partial Replacement:

   • Start by replacing only the slowest queries. Profile with DB::enableQueryLog() to identify bottlenecks:

     DB::enableQueryLog();
     User::whereHas('posts', fn($q) => $q->where('title', 'like', '%test%'))->get();
     dd(DB::getQueryLog());
     

2. Fallback Logic:

   • Use feature flags or environment checks to toggle the optimization:

     if (app()->environment('production') && !config('database.connections.mysql.version_8_plus')) {
         User::whereHasByNonDependentSubquery(...);
     } else {
         User::whereHas(...);
     }
     

3. Testing:

   • Write integration tests to compare results between has() and hasByNonDependentSubquery():

     public function test_has_by_non_dependent_subquery()
     {
         $usersWithPosts = User::factory()->has('posts')->count();
         $optimizedCount = User::hasByNonDependentSubquery('posts')->count();
         $this->assertEquals($usersWithPosts, $optimizedCount);
     }
     

4. Extension Points:

   • Custom Macros: Extend the package by adding macros to Eloquent’s query builder:

     use Illuminate\Database\Eloquent\Builder;
     Builder::macro('hasOptimized', function ($relation, $callback = null, $operator = '>=', $count = 1) {
         return $this->whereHasByNonDependentSubquery($relation, $callback, $operator, $count);
     });
     

   • Service Provider: Bind the package’s traits or extend Eloquent’s behavior globally if needed.

5. Monitoring:

   • Track query performance in production using tools like Laravel Debugbar or New Relic to validate optimizations.