Eloquent Has By Join Laravel Package

Getting Started

Minimal Setup

1. Installation:

   composer require mpyw/eloquent-has-by-join
   

   No additional configuration is required—just use the package in your Eloquent models.

2. First Use Case: Replace has() or whereHas() with hasByJoin() for single-result relations (e.g., belongsTo, hasOne). Example:

   // Before (subquery)
   Post::whereHas('comments', fn($q) => $q->where('active', true));
   
   // After (join)
   Post::hasByJoin('comments', fn($q) => $q->where('active', true));
   

3. Where to Look First:

   • Review the README for MySQL/PostgreSQL notes.
   • Check the tests for usage examples.

Implementation Patterns

Core Workflow

1. Replace has()/whereHas(): Use hasByJoin() for relations where you expect a single result (e.g., belongsTo, hasOne).

   // Original
   User::whereHas('profile', fn($q) => $q->where('verified', true));
   
   // Optimized
   User::hasByJoin('profile', fn($q) => $q->where('verified', true));
   

2. Chaining with Other Constraints: Combine with join(), where(), or with() seamlessly:

   Post::hasByJoin('author', fn($q) => $q->where('active', true))
       ->join('tags', 'posts.id', 'tags.post_id')
       ->where('published', true);
   

3. Customizing Join Conditions: Override default join logic via the closure:

   Post::hasByJoin('comments', fn($q) => $q
       ->where('active', true)
       ->select('post_id', 'id') // Explicit columns for join
   );
   

4. Soft Deletes: Works out-of-the-box with SoftDeletes:

   Post::hasByJoin('comments')->withTrashed();
   

Integration Tips

• Performance: Benchmark queries in MySQL (pre-8.0.16) to validate improvements.
• PostgreSQL: Use cautiously—modern versions optimize subqueries well.
• Complex Relations: Avoid nested hasByJoin (e.g., hasByJoin('user.posts')). Flatten logic:

  // Instead of:
  User::hasByJoin('posts.comments', ...);
  
  // Use:
  User::hasByJoin('posts', fn($q) => $q->hasByJoin('comments', ...));
  

Gotchas and Tips

Pitfalls

1. MySQL Version:

   • Pre-8.0.16: Significant performance gains expected.
   • 8.0.16+: Optimizer may negate benefits—test thoroughly.
   • PostgreSQL: Likely no improvement; subqueries are already optimized.

2. Non-Single-Result Relations:

   • Avoid for hasMany, belongsToMany, or polymorphic relations. Use has() instead.
   • Example of wrong usage:

     // ❌ Fails silently (returns all matching rows)
     Post::hasByJoin('comments'); // Comments is hasMany
     

3. Column Selection:

   • Missing select(): May cause Cartesian products if joined tables lack constraints.
   • Fix: Explicitly select columns for the join:

     Post::hasByJoin('author', fn($q) => $q->select('id', 'user_id'));
     

4. Debugging:

   • Log Queries: Use Laravel’s DB::enableQueryLog() to compare has() vs. hasByJoin() output.
   • Common Issue: Missing where clauses in the closure can lead to unintended joins.

Tips

1. Hybrid Approach: Combine with orHasByJoin() for complex conditions:

   Post::whereHas('comments')
       ->orHasByJoin('replies', fn($q) => $q->where('urgent', true));
   

2. Custom Macros: Extend the package by adding macros to your Model:

   use Illuminate\Database\Eloquent\Builder;
   
   Builder::macro('hasActiveByJoin', function($relation) {
       return $this->hasByJoin($relation, fn($q) => $q->where('active', true));
   });
   

3. Testing:

   • Mock the query builder to test hasByJoin behavior:

     $builder = $this->partialMock(Builder::class, [], function($mock) {
         $mock->shouldReceive('getQuery')->andReturnSelf();
     });
     

4. Fallback Logic: Use a helper to auto-switch between has() and hasByJoin() based on DB:

   function hasRelation($relation, $callback = null, $useJoin = true) {
       $db = DB::connection()->getDatabaseName();
       return $useJoin && str_starts_with($db, 'mysql') && version_compare(DB::version(), '8.0.16', '<')
           ? $this->hasByJoin($relation, $callback)
           : $this->has($relation, $callback);
   }
   

5. Indexing: Ensure foreign keys are indexed for optimal join performance:

   Schema::table('comments', function (Blueprint $table) {
       $table->foreignId('post_id')->constrained()->index();
   });