maksimru/eloquent-subquery-magic

About

Library extends Laravel's Eloquent ORM with various helpful sub query operations such as leftJoinSubquery or fromSubquery and provide clean methods to use Eloquent without raw statements

Usage

No installation required

Simply add SubqueryMagic trait into your models

use Illuminate\Database\Eloquent\Model;
use MaksimM\SubqueryMagic\SubqueryMagic;

class SomeModel extends Model
{
    use SubqueryMagic;
}

Installation

composer require maksimru/eloquent-subquery-magic

Supported operations (with examples)

1. leftJoinSubquery

   User::selectRaw('user_id,comments_by_user.total_count')->leftJoinSubquery(
     //subquery
     Comment::selectRaw('user_id,count(*) total_count')
         ->groupBy('user_id'),
     //alias
     'comments_by_user', 
     //closure for "on" statement
     function ($join) {
         $join->on('users.id', '=', 'comments_by_user.user_id');
     }
   )->get();
   

2. joinSubquery

   User::selectRaw('user_id,comments_by_user.total_count')->joinSubquery(
     //subquery
     Comment::selectRaw('user_id,count(*) total_count')
         ->groupBy('user_id'),
     //alias
     'comments_by_user', 
     //closure for "on" statement
     function ($join) {
         $join->on('users.id', '=', 'comments_by_user.user_id');
     }
   )->get();
   

3. rightJoinSubquery

   User::selectRaw('user_id,comments_by_user.total_count')->rightJoinSubquery(
       //subquery
       Comment::selectRaw('user_id,count(*) total_count')
          ->groupBy('user_id'),
       //alias
       'comments_by_user', 
       //closure for "on" statement
       function ($join) {
          $join->on('users.id', '=', 'comments_by_user.user_id');
       }
   )->get();
   

4. whereInSubquery

   User::whereInSubquery('id', Comment::selectRaw('distinct(user_id)'))->get();
   

5. whereNotInSubquery

   User::whereNotInSubquery('id', Comment::selectRaw('distinct(user_id)'))->get();
   

6. orWhereInSubquery

   User::where('is_enabled','=',true)->orWhereInSubquery('id', Comment::selectRaw('distinct(user_id)'))->get();
   

7. orWhereNotInSubquery

   User::where('is_enabled','=',true)->orWhereNotInSubquery('id', Comment::selectRaw('distinct(user_id)'))->get();
   

8. fromSubquery

   User::selectRaw('info.min_id,info.max_id,info.total_count')->fromSubquery(
       //subquery
       User::selectRaw('min(id) min_id,max(id) max_id,count(*) total_count'),
       //alias
       'info'
   )->get()
   

Nested queries

It is possible to use it in nested queries, but you need to boot scope manually in each closure

User::where(function ($nested_query) {
    (new SubqueryMagicScope())->extend($nested_query);
    $nested_query->where('id', '<', 10);
    $nested_query->orWhereNotInSubquery('id', Comment::selectRaw('distinct(user_id)'));
})

Complex example

User::selectRaw('users.name,filtered_members_with_stats.total_count')
    ->where(function ($nested_query) {
        (new SubqueryMagicScope())->extend($nested_query);
        $nested_query->where('id', '<', 10);
        $nested_query->orWhereNotInSubquery('id', Comment::selectRaw('distinct(user_id)'));
    })->rightJoinSubquery(
        User::selectRaw('user_id,comments_by_user.total_count')->leftJoinSubquery(
            Comment::selectRaw('user_id,count(*) total_count')
                ->groupBy('user_id'),
            'comments_by_user', function ($join) {
                $join->on('users.id', '=', 'comments_by_user.user_id');
            }
        )->where('id','<',20),
        'filtered_members_with_stats', function ($join) {
            $join->on('users.id', '=', 'filtered_members_with_stats.user_id');
        }
    )
    ->get();

It will be executed as:

SELECT users.name,
       filtered_members_with_stats.total_count
FROM `users`
RIGHT JOIN
  (SELECT name,
          comments_by_user.total_count
   FROM `users`
   LEFT JOIN
     (SELECT user_id,
             count(*) total_count
      FROM `comments`
      GROUP BY `user_id`) `comments_by_user` ON `users`.`id` = `comments_by_user`.`user_id`
   WHERE `id` < 20) `filtered_members_with_stats` ON `users`.`id` = `filtered_members_with_stats`.`user_id`
WHERE (`id` < 10
       OR `id` NOT IN
         (SELECT distinct(user_id)
          FROM `comments`))