Eloquent Power Joins Laravel Package

Getting Started

Minimal Steps

1. Installation: Add the package via Composer:

   composer require kirschbaum-development/eloquent-power-joins
   

   For Laravel < 10, use 3.* version.

2. First Use Case: Replace manual joins with relationship-based joins.

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

3. Key Entry Points:

   • joinRelationship(): Join tables using Eloquent relationships.
   • powerJoinHas()/powerJoinWhereHas(): Replace whereHas/has with joins.
   • orderByPowerJoins*(): Sort by related table columns/aggregations.

Implementation Patterns

Core Workflows

1. Relationship-Based Joins

   • Replace hardcoded joins with relationship names (e.g., 'posts.comments').
   • Supports nested relationships and polymorphic joins automatically.

   User::joinRelationship('posts.comments')
       ->leftJoinRelationship('drafts');
   

2. Conditional Joins

   • Apply constraints via callbacks:

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

   • Nested callbacks for multi-table relationships:

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

3. Model Scopes in Joins

   • Reuse model scopes (e.g., published()) inside join callbacks:

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

   • Note: Avoid type-hinting $query in scopes for joins.

4. Existence Queries

   • Replace whereHas with join-based alternatives:

   User::powerJoinWhereHas('posts', fn($join) => $join->where('posts.views', '>', 100));
   

   • Use array syntax for multi-table relationships:

   User::powerJoinWhereHas('commentsThroughPosts', [
       'comments' => fn($query) => $query->where('body', 'like', '%test%'),
   ]);
   

5. Sorting by Related Data

   • Sort by columns/aggregations in joined tables:

   User::orderByPowerJoins('profile.city')
       ->orderByPowerJoinsAvg('posts.rating', 'desc');
   

   • Left-join variants for nullable fields:

   Post::orderByLeftPowerJoinsCount('comments.votes');
   

Integration Tips

• Aliases: Use joinRelationshipUsingAlias() for duplicate table joins:

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

• Soft Deletes: Automatically excludes soft-deleted models; override with:

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

• Global Scopes: Enable with withGlobalScopes() (avoid type-hinting $builder in global scopes):

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

Gotchas and Tips

Pitfalls

1. Scope Type-Hinting

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

     // ❌ Fails
     public function scopePublished($query: Builder) { ... }
     
     // ✅ Works
     public function scopePublished($query) { ... }
     

2. Polymorphic Joins

   • Issue: Joining polymorphic relationships (e.g., Image::joinRelationship('imageable')) requires specifying the morphable type:

     Image::joinRelationship('imageable', morphable: Post::class);
     

   • Limit: Only one morphable type per join.

3. BelongsToMany Joins

   • Issue: Requires explicit table names in callbacks:

     User::joinRelationship('groups', [
         'groups' => [
             'groups' => fn($join) => $join->where('groups.active', true),
             'group_members' => fn($join) => $join->where('group_members.role', 'admin'),
         ],
     ]);
     

4. Aggregation Sorting

   • Issue: orderByPowerJoins* methods may return unexpected results if the joined table has no matching rows (e.g., NULL for COUNT).
   • Fix: Use left-join variants for nullable fields:

     Post::orderByLeftPowerJoinsCount('comments.votes');
     

5. Global Scopes in Joins

   • Issue: Global scopes with $builder type-hinting fail silently.
   • Fix: Remove type-hinting or mock the builder in tests.

Debugging Tips

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

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

• Alias Conflicts: Ensure unique aliases for duplicate tables:

  Post::joinRelationshipUsingAlias('category', 'category_alias');
  

• Performance: For large datasets, test powerJoinHas vs. native whereHas—joins may be slower for complex subqueries.

Extension Points

1. Custom Join Logic

   • Extend the package by publishing a config file (config/eloquent-power-joins.php) to override default behaviors (e.g., soft-delete handling).

2. Macros

   • Add custom join methods to the query builder:

   use KirschbaumDevelopment\PowerJoins\PowerJoins;
   
   QueryBuilder::macro('customJoin', function($relationship) {
       return $this->joinRelationship($relationship)->where(...);
   });
   

3. Testing

   • Mock join callbacks in tests:

   $mock = Mockery::mock();
   $mock->shouldReceive('where')->once();
   User::joinRelationship('posts', $mock);
   

4. Laravel 11+ Compatibility

   • The package supports Laravel 11–13; ensure your app’s Eloquent version matches the package’s requirements.

```markdown
### Example Debugging Workflow
1. **Problem**: `powerJoinWhereHas` returns incorrect results.
2. **Steps**:
   - Compare `toSql()` output with native `whereHas`.
   - Check for missing `ON` clauses in nested joins.
   - Verify table aliases in complex relationships.
3. **Fix**: Adjust callbacks or use left joins for nullable fields.