Eloquent Power Joins Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require kirschbaum-development/eloquent-power-joins
   

   Ensure compatibility with your Laravel version (11.x/12.x/13.x).

2. First Use Case: Replace a basic join with joinRelationship():

   // Traditional join
   User::select('users.*')->join('posts', 'posts.user_id', '=', 'users.id');
   
   // Power Joins
   User::joinRelationship('posts');
   

Where to Look First

• README.md: Focus on the "Usage" section for core methods (joinRelationship, powerJoinHas, orderByPowerJoins).
• Blog Post: Kirschbaum’s Insights for motivation and edge cases.
• Changelog: Check for recent fixes (e.g., nested closures in aliases, trashed models).

Implementation Patterns

Core Workflows

1. Relationship-Based Joins:

   • Replace hardcoded joins with relationship names:

     // Instead of:
     User::join('posts', 'posts.user_id', '=', 'users.id');
     
     // Use:
     User::joinRelationship('posts');
     

   • Nested Relationships:

     User::joinRelationship('posts.comments'); // Joins posts → comments
     

2. Conditional Joins:

   • Apply constraints via callbacks:

     User::joinRelationship('posts', fn($join) => $join->where('posts.approved', true));
     

   • Nested Conditions:

     User::joinRelationship('posts.comments', [
         'posts' => fn($join) => $join->where('posts.published', true),
         'comments' => fn($join) => $join->where('comments.approved', true),
     ]);
     

3. Polymorphic Relationships:

   • Automatically filters by imageable_type:

     Post::joinRelationship('images'); // Adds `where imageable_type = 'App\Models\Post'`
     

4. Querying Existence:

   • Replace whereHas/has with join-based equivalents:

     // Traditional:
     User::has('posts')->whereHas('posts', fn($q) => $q->where('posts.published', true));
     
     // Power Joins:
     User::powerJoinHas('posts')
         ->powerJoinWhereHas('posts', fn($join) => $join->where('posts.published', true));
     

5. Sorting by Related Data:

   • Order by columns/aggregations from joined tables:

     User::orderByPowerJoins('profile.city');
     User::orderByPowerJoinsCount('posts.id', 'desc'); // Sort by post count
     

Integration Tips

• Scopes in Joins: Use model scopes directly in join callbacks (avoid type-hinting $query):

  User::joinRelationship('posts', fn($join) => $join->published());
  

• Soft Deletes: Explicitly include/exclude trashed records:

  UserProfile::joinRelationship('users', fn($join) => $join->withTrashed());
  

• Global Scopes: Enable with withGlobalScopes() (avoid type-hinting Builder):

  UserProfile::joinRelationship('users', fn($join) => $join->withGlobalScopes());
  

• Aliases: Handle duplicate table names:

  Post::joinRelationshipUsingAlias('category.parent');
  

Gotchas and Tips

Pitfalls

1. Scope Type-Hinting:

   • Error: Type-hinting $query in model scopes breaks join callbacks.
   • Fix: Use Builder or no type-hint:

     // ❌ Broken:
     public function scopePublished($query) { ... } // Type-hinted
     
     // ✅ Works:
     public function scopePublished($query) { ... } // No type-hint
     

2. Global Scope Conflicts:

   • Issue: Global scopes may not apply as expected in joins.
   • Fix: Explicitly enable with withGlobalScopes() and avoid Builder type-hinting.

3. Nested Closures in Aliases:

   • Bug: Pre-2.2.2 versions failed with nested closures in aliases.
   • Fix: Update to >=2.2.2 or restructure callbacks.

4. Polymorphic Limitations:

   • Limit: Only one morphable type per join (e.g., Image::joinRelationship('imageable', morphable: Post::class)).

5. Aggregation Quirks:

   • Behavior: orderByPowerJoinsCount uses LEFT JOIN by default (use orderByLeftPowerJoinsCount explicitly if needed).

Debugging Tips

• SQL Inspection: Use toSql() to verify generated queries:

  User::joinRelationship('posts')->toSql();
  

• Alias Debugging: Check for duplicate table names in joins (use joinRelationshipUsingAlias).
• Soft Delete Debugging: Ensure deleted_at conditions are present/absent as expected.

Extension Points

1. Custom Join Logic: Extend the package by publishing a macro or creating a trait:

   // app/Providers/AppServiceProvider.php
   use Illuminate\Database\Eloquent\Builder;
   use Kirschbaum\PowerJoins\PowerJoins;
   
   Builder::macro('customJoin', function ($relation) {
       return $this->joinRelationship($relation, fn($join) => $join->where(...));
   });
   

2. Query Builder Integration: Add methods to QueryBuilder for reusable join patterns:

   // app/Providers/AppServiceProvider.php
   use Illuminate\Database\Query\Builder;
   
   Builder::macro('joinWithConditions', function ($relation, array $conditions) {
       return $this->joinRelationship($relation, $conditions);
   });
   

3. Testing: Mock joins in tests using joinRelationship:

   $user = User::factory()->create();
   Post::factory()->count(3)->create(['user_id' => $user->id]);
   
   $this->assertCount(1, User::joinRelationship('posts')->get());
   

Performance Considerations

• Indexing: Ensure foreign keys and joined columns are indexed.
• Selective Columns: Avoid select('*') to prevent column conflicts:

  User::select(['users.id', 'posts.title'])->joinRelationship('posts');
  

• Left Joins: Use leftJoinRelationship for optional relationships to avoid filtering out records.