Eloquent Power Joins Laravel Package

Getting Started

Minimal Steps

1. Installation:

   composer require kirschbaum-development/eloquent-power-joins
   

   For Laravel <10, use 3.* version.

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

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

3. Key Files to Review:

   • app/Models/ (your Eloquent models with defined relationships)
   • app/Providers/AppServiceProvider.php (if extending package behavior)
   • config/database.php (if customizing join defaults)

Where to Look First

• Relationship Definitions: Verify your model relationships (e.g., hasMany, belongsTo) in app/Models/.
• Query Builder: Check how joinRelationship() integrates with existing queries in your controllers/repositories.
• Package Docs: Blog Post for conceptual depth.

Implementation Patterns

Core Workflows

1. Relationship-Based Joins:

   // Basic join
   User::joinRelationship('posts')->get();
   
   // Nested joins
   User::joinRelationship('posts.comments')->get();
   
   // Left/Right joins
   User::leftJoinRelationship('posts')->get();
   

2. Conditional Joins:

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

3. Scope Integration:

   // Using model scopes in joins
   User::joinRelationship('posts', fn($join) => $join->published());
   

4. Existence Queries:

   // Replace `whereHas` with joins
   User::powerJoinHas('posts')->get();
   User::powerJoinWhereHas('posts', fn($join) => $join->where('posts.views', '>', 100));
   

5. Sorting with Joins:

   // Sort by related table columns
   User::orderByPowerJoins('profile.city')->get();
   
   // Sort by aggregations
   User::orderByPowerJoinsCount('posts.id', 'desc')->get();
   

Integration Tips

• Repository Pattern: Centralize complex joins in repository methods:

  class UserRepository {
      public function withActivePosts() {
          return User::joinRelationship('posts', fn($join) => $join->where('posts.active', true));
      }
  }
  

• Service Layer: Use joins in service methods to encapsulate business logic:

  class PostService {
      public function getPopularPosts() {
          return Post::orderByPowerJoinsCount('comments.id', 'desc')->take(10)->get();
      }
  }
  

• View Composers: Preload joined data for views:

  public function compose(Post $post) {
      $post->load(['comments' => function($query) {
          $query->joinRelationship('users'); // Nested join
      }]);
  }
  

• API Resources: Shape responses with joined data:

  public function toArray($request) {
      return [
          'user' => $this->user,
          'posts' => $this->whenLoaded('posts', fn() => $this->posts),
          'profile' => $this->whenLoaded('profile', fn() => $this->profile),
      ];
  }
  

• Testing: Mock joins in unit tests:

  $user = User::factory()->create();
  $post = Post::factory()->create(['user_id' => $user->id]);
  
  $this->assertDatabaseHas('users', [
      'id' => $user->id,
  ]);
  $this->assertDatabaseHas('posts', [
      'user_id' => $user->id,
      'published' => true,
  ]);
  
  $result = User::joinRelationship('posts')->whereHas('posts', fn($q) => $q->where('published', true))->first();
  

Gotchas and Tips

Pitfalls

1. Scope Type-Hinting:

   • Issue: Model scopes with typed $query parameters (e.g., Builder $query) will fail in join callbacks.
   • Fix: Use untyped $query or Builder $query without type-hinting:

     // ❌ Fails
     public function scopePublished($query) { ... }
     
     // ✅ Works
     public function scopePublished($query) { ... } // No type-hint
     

2. Global Scopes in Joins:

   • Issue: Global scopes may not apply automatically in joins unless explicitly enabled.
   • Fix: Use withGlobalScopes() in the join callback:

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

3. Polymorphic Relationships:

   • Issue: Joining polymorphic relationships requires specifying the morphable type:

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

   • Gotcha: Only one morphable type is supported per join.

4. Soft Deletes:

   • Issue: Joins default to excluding soft-deleted models. Use withTrashed() or onlyTrashed() to include them:

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

5. Alias Conflicts:

   • Issue: Nested joins with the same table name (e.g., posts.posts) require aliases:

     Post::joinRelationshipUsingAlias('posts.comments')->get();
     

   • Fix: Use as() in callbacks for granular control:

     Post::joinRelationship('posts.comments', [
         'posts' => fn($join) => $join->as('post_alias'),
     ]);
     

6. BelongsToMany Joins:

   • Issue: Requires explicit table definitions in the callback array:

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

7. Aggregation Sorting:

   • Issue: orderByPowerJoins* methods may not work as expected with LEFT JOIN if the joined table has no matches.
   • Fix: Use orderByLeftPowerJoins* for left joins:

     Post::orderByLeftPowerJoinsCount('comments.id', 'desc')->get();
     

8. Query Caching:

   • Issue: Joins may bypass query caching if not explicitly cached.
   • Fix: Cache the query builder:

     $query = User::joinRelationship('posts')->remember(60);
     

Debugging Tips

1. SQL Inspection:

   • Use toSql() to verify generated queries:

     $sql = User::joinRelationship('posts')->toSql();
     dd($sql);
     

2. Binding Analysis:

   • Check bindings with getQuery()->getBindings():

     dd(User::joinRelationship('posts')->getQuery()->getBindings());
     

3. Step-by-Step Joins:

   • Build joins incrementally to isolate issues:

     $query = User::query();
     $query->joinRelationship('posts');
     $query->joinRelationship('posts.comments');
     dd($query->toSql());
     

4. Relationship Debugging:

   • Verify relationship definitions with getRelationships():

     dd(User::first()->getRelationships());
     

Extension Points

1. Custom Join Macros:

   • Extend the query builder with custom join methods:

     use Illuminate\Database\Query\Builder;
     use Kirschbaum\PowerJoins\PowerJoins;
     
     Builder::macro('customJoin', function($relation, $callback) {
         return $this->joinRelationship($relation, $callback);
     });
     

2. Model Observers:

   • Hook into join logic via model observers:

     class PostObserver {
         public function retrieving(Post $post) {
             if (app()->bound('joinRelationshipCallback')) {
                 $post->setRelation('posts', $post->posts()->joinRelationship(...));
             }
         }
     }
     

3. Service Provider:

   • Override package behavior in AppServiceProvider:

     public function boot() {
         PowerJoins::macro('customAlias', function($relation, $alias) {
             return $this->joinRelationshipUsingAlias($relation, $alias);