With Join Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require sleeping-owl/with-join
   

   Add the service provider to config/app.php under providers:

   SleepingOwl\WithJoin\WithJoinServiceProvider::class,
   

2. First Use Case: Replace a with() eager load with a references() call to convert a belongsTo subquery into a LEFT JOIN:

   // Before (N+1 queries)
   Post::with('author')->where('title', 'like', '%test%')->get();
   
   // After (Single query with JOIN)
   Post::with('author')->references('author')->where('title', 'like', '%test%')->get();
   

Where to Look First

• README: Focus on the Usage section for syntax and examples.
• Source: vendor/sleeping-owl/with-join/src/WithJoinServiceProvider.php for provider logic.
• Macros: Check vendor/sleeping-owl/with-join/src/WithJoinMacros.php for core functionality.

Implementation Patterns

Core Workflow

1. Eager Loading + JOIN Conversion:

   // Traditional eager load (N+1)
   User::with('posts')->get();
   
   // Optimized JOIN (1 query)
   User::with('posts')->references('posts')->get();
   

2. Conditional JOINs: Use includes() for model-level defaults or dynamic relations:

   // Model definition
   class User extends Model {
       protected $includes = ['posts', 'profile'];
   }
   
   // Query (uses JOINs for included relations)
   User::where('name', 'John')->get();
   

3. Hybrid Queries: Mix with() and references() for selective optimization:

   // JOIN 'author' but eager-load 'comments'
   Post::with(['comments'])->references('author')->get();
   

Integration Tips

• Query Scopes: Encapsulate optimized queries in scopes:

  class PostScope {
      public function scopeWithAuthor($query) {
          return $query->with('author')->references('author');
      }
  }
  

• Dynamic Relations: Use references() with dynamic relations (Laravel 5.3+):

  $relation = 'author';
  Post::with($relation)->references($relation)->get();
  

• Ordering by Related Fields: Leverage JOINs for efficient sorting:

  Post::with('author')->references('author')->orderBy('author.name')->get();
  

• Conditional JOINs: Combine with when() for context-aware queries:

  Post::when($showAuthor, function ($query) {
      return $query->with('author')->references('author');
  })->get();
  

Gotchas and Tips

Pitfalls

1. Unsupported Relations:

   • Only works with belongsTo, belongsToMany, and hasOne relations. Avoid hasMany or morphTo without testing.
   • Fix: Manually add LEFT JOIN clauses for unsupported relations.

2. Column Ambiguity: JOINs may cause SQL errors if column names conflict (e.g., created_at in both tables).

   • Fix: Use table aliases or DB::raw:

     Post::with('author')->references('author')->select('posts.*', 'authors.name as author_name')->get();
     

3. Model Caching: The package doesn’t cache relation metadata. Repeated references() calls may re-process the same relations.

   • Fix: Cache relation definitions if performance is critical.

4. Laravel Version Mismatch: The package was last updated in 2014 and may not support Laravel 8/9 features (e.g., query builder changes).

   • Fix: Test thoroughly or fork the package for modern Laravel.

5. Over-Eager Loading: Applying references() to all relations can bloat queries. Use selectively.

   • Fix: Profile queries with DB::enableQueryLog() to identify bottlenecks.

Debugging

• Query Logs: Enable query logging to verify JOINs:

  DB::enableQueryLog();
  Post::with('author')->references('author')->get();
  dd(DB::getQueryLog());
  

• Common Errors:

  • "Relation not found": Ensure the relation exists in the model’s $with or $includes.
  • "Column not found": Check for typos in relation names or table columns.

Tips

1. Performance Gain: Measure impact with Benchmark:

   $time = microtime(true);
   Post::with('author')->get(); // N+1
   $nPlusOneTime = microtime(true) - $time;
   
   $time = microtime(true);
   Post::with('author')->references('author')->get(); // JOIN
   $joinTime = microtime(true) - $time;
   
   // Compare $nPlusOneTime vs $joinTime
   

2. Combining with Other Packages: Works seamlessly with laravel-scout, spatie/laravel-activitylog, etc., as it operates at the query level.

3. Customizing JOIN Clauses: Extend the package by overriding the buildJoin method in a macro:

   QueryBuilder::macro('customJoin', function ($relation) {
       // Custom logic here
       return $this->leftJoin(...);
   });
   

4. Testing: Mock the package’s macros in tests:

   QueryBuilder::shouldReceive('references')->andReturnSelf();
   

5. Fallback Behavior: If references() fails (e.g., unsupported relation), the query falls back to eager loading. No exceptions are thrown.