README

关于

库扩展 Laravel 的 Eloquent ORM，提供如 leftJoinSubquery 或 fromSubquery 等各种有用的子查询操作，并提供干净的方法来使用 Eloquent 而不需要原始语句

用法

无需安装

只需将 SubqueryMagic 特性添加到您的模型中

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

class SomeModel extends Model
{
    use SubqueryMagic;
}

安装

composer require maksimru/eloquent-subquery-magic

支持的操作（示例）

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()

嵌套查询

它可以在嵌套查询中使用，但需要在每个闭包中手动启动范围

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

复杂示例

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();

它将被执行为

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`))