Eloquent Power Joins With Compoships Laravel Package

Getting Started

Minimal Setup

1. Installation: Add the package via Composer:

   composer require kitloong/eloquent-power-joins-with-compoships
   

2. Model Setup: Ensure your model uses both PowerJoins and Compoships traits:

   use Awobaz\Compoships\Compoships;
   use Kirschbaum\PowerJoins\PowerJoins;
   
   class User extends Model
   {
       use PowerJoins, Compoships;
   }
   

3. Define Composite Relationship: Configure a relationship with composite keys (e.g., hasMany):

   public function posts()
   {
       return $this->hasMany(
           Post::class,
           ['team_id', 'category_id'], // Foreign keys
           ['team_id', 'category_id']  // Local keys
       );
   }
   

4. First Join: Use joinRelationship() to execute a join query:

   $usersWithPosts = User::joinRelationship('posts')->get();
   

Where to Look First

• Documentation: Start with the Medium article for conceptual clarity.
• Source Code: Explore the PowerJoins and Compoships packages for deeper integration details.
• Tests: Check the package’s tests for real-world usage patterns.

Implementation Patterns

Core Workflow

1. Query Construction:

   • Use joinRelationship() to join tables with composite keys seamlessly:

     User::joinRelationship('posts')->where('posts.title', 'like', '%test%')->get();
     

   • Chain with other Eloquent methods (e.g., where, select, with).

2. Composite Key Handling:

   • Define relationships with foreign keys and local keys as arrays:

     $this->belongsToMany(
         Role::class,
         'user_roles',
         ['user_id', 'department_id'], // Foreign keys
         ['user_id', 'department_id']  // Local keys
     );
     

   • The package automatically generates the correct ON clause in SQL.

3. Eager Loading with Joins:

   • Combine joinRelationship() with with() for optimized queries:

     User::with(['posts' => function ($query) {
         $query->joinRelationship('comments'); // Nested joins
     }])->get();
     

4. Dynamic Joins:

   • Use join() with composite keys manually if needed:

     User::join('posts', ['users.team_id', 'users.category_id'], ['posts.team_id', 'posts.category_id'])
         ->select('users.*', 'posts.title')
         ->get();
     

Integration Tips

• API Responses: Use joinRelationship() to fetch joined data in a single query, reducing N+1 issues:

  return User::joinRelationship('posts')->get()->map(function ($user) {
      return [
          'name' => $user->name,
          'posts' => $user->posts,
      ];
  });
  

• Polymorphic Relationships: Works with polymorphic joins (e.g., morphTo/morphWith):

  public function comments()
  {
      return $this->morphToMany(
          Comment::class,
          'commentable',
          'commentables',
          ['user_id', 'scope_id'], // Composite foreign keys
          ['user_id', 'scope_id']  // Composite local keys
      );
  }
  

• Scopes: Create custom scopes for reusable join logic:

  class UserQueryBuilder extends Builder
  {
      public function scopeWithTeamPosts($query)
      {
          return $query->joinRelationship('posts')->where('posts.team_id', auth()->user()->team_id);
      }
  }
  

  Register the scope in your User model’s boot() method.

Gotchas and Tips

Common Pitfalls

1. Key Mismatch Errors:

   • Issue: Forgetting to align foreign/local keys in the relationship definition.
   • Fix: Double-check arrays in hasMany, belongsTo, or belongsToMany:

     // Wrong: Keys are swapped or misaligned
     $this->hasMany(Post::class, ['category_id', 'team_id'], ['team_id', 'category_id']);
     

   • Debug: Use dd($this->posts->getQuery()->toSql()) to inspect the generated SQL.

2. Composite Key Ambiguity:

   • Issue: Duplicate column names in joined tables (e.g., team_id exists in both users and posts).
   • Fix: Explicitly select columns or alias them:

     User::joinRelationship('posts')
         ->select('users.*', 'posts.title as post_title')
         ->get();
     

3. Performance with Large Datasets:

   • Issue: Joins with composite keys can be slow if tables are large.
   • Fix:
     • Add indexes to composite key columns in the database.
     • Use select() to limit columns:

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

     • Consider whereIn or whereExists for filtering.

4. Caching Quirks:

   • Issue: Cached queries may not reflect changes in joined data.
   • Fix: Disable caching for joined queries or use fresh():

     User::joinRelationship('posts')->fresh()->get();
     

Debugging Tips

• SQL Inspection: Use toSql() and getBindings() to verify queries:

  $query = User::joinRelationship('posts');
  dd($query->toSql(), $query->getBindings());
  

• Composite Key Validation: Test with a single composite key first to isolate issues:

  // Test with one key, then add the second
  $this->hasMany(Post::class, ['team_id'], ['team_id']);
  

• Package Conflicts: Ensure no other packages override PowerJoins or Compoships traits.

Extension Points

1. Custom Join Logic:

   • Override the joinRelationship method in your model to add pre/post-processing:

     public function scopeCustomJoin($query, $relation)
     {
         return $query->joinRelationship($relation)->where('posts.published', true);
     }
     

   • Call it as:

     User::customJoin('posts')->get();
     

2. Dynamic Composite Keys:

   • Use closures to define dynamic composite keys (e.g., based on request):

     public function posts()
     {
         $keys = request()->has('dynamic') ? ['team_id', 'category_id'] : ['team_id'];
         return $this->hasMany(Post::class, $keys, $keys);
     }
     

3. Event Hooks:

   • Listen for eloquent.powerjoins.joining events to modify join behavior:

     Event::listen('eloquent.powerjoins.joining', function ($model, $relation) {
         if ($relation === 'posts') {
             $model->query->where('posts.active', true);
         }
     });
     

4. Testing:

   • Mock composite relationships in tests:

     $user = User::factory()->create();
     $post = Post::factory()->create(['team_id' => $user->team_id, 'category_id' => $user->category_id]);
     $user->posts()->save($post);
     
     $this->assertCount(1, User::joinRelationship('posts')->get());