PHP code example of maksimru / eloquent-subquery-magic

1. Go to this page and download the library: Download maksimru/eloquent-subquery-magic library. Choose the download type require.

2. Extract the ZIP file and open the index.php.

3. Add this code to the index.php. 
    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

maksimru / eloquent-subquery-magic example snippets



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

class SomeModel extends Model
{
    use SubqueryMagic;
}



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

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

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

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

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

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

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

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