PHPackages                             maksimru/eloquent-subquery-magic - PHPackages - PHPackages  [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register)

1. [Directory](/)
2. /
3. [Database & ORM](/categories/database)
4. /
5. maksimru/eloquent-subquery-magic

ActiveLibrary[Database & ORM](/categories/database)

maksimru/eloquent-subquery-magic
================================

Eloquent extension providing ability to use a lot of subquery functions like fromSubquery or leftJoinSubquery

v0.13(6y ago)1918.6k5MITPHPPHP >=5.6CI failing

Since Dec 22Pushed 6y ago1 watchersCompare

[ Source](https://github.com/maksimru/eloquent-subquery-magic)[ Packagist](https://packagist.org/packages/maksimru/eloquent-subquery-magic)[ RSS](/packages/maksimru-eloquent-subquery-magic/feed)WikiDiscussions master Synced 1w ago

READMEChangelog (1)Dependencies (3)Versions (10)Used By (0)

[![Scrutinizer Code Quality](https://camo.githubusercontent.com/4a209caf6263093950a729b4e855589a2f892c4e59818d623d649f7c69f5b772/68747470733a2f2f7363727574696e697a65722d63692e636f6d2f672f6d616b73696d72752f656c6f7175656e742d73756271756572792d6d616769632f6261646765732f7175616c6974792d73636f72652e706e673f623d6d6173746572)](https://scrutinizer-ci.com/g/maksimru/eloquent-subquery-magic/?branch=master)[![codecov](https://camo.githubusercontent.com/9370c7319e37cb1937c4b746c89d366eb0475ac3687de89aac0a0e96cc29612e/68747470733a2f2f636f6465636f762e696f2f67682f6d616b73696d72752f656c6f7175656e742d73756271756572792d6d616769632f6272616e63682f6d61737465722f67726170682f62616467652e737667)](https://codecov.io/gh/maksimru/eloquent-subquery-magic)[![StyleCI](https://camo.githubusercontent.com/e7e0f07edd16a331e97b113380ec9561bb610d8875fee1b833ca0965d9ec1bec/68747470733a2f2f7374796c6563692e696f2f7265706f732f3131353039393130312f736869656c643f6272616e63683d6d6173746572)](https://styleci.io/repos/115099101)[![CircleCI](https://camo.githubusercontent.com/b1c0e7c72578a175fcadf72d826dc7e7b862c8246a3668a5ea238398ad0a0318/68747470733a2f2f636972636c6563692e636f6d2f67682f6d616b73696d72752f656c6f7175656e742d73756271756572792d6d616769632e7376673f7374796c653d737667)](https://circleci.com/gh/maksimru/eloquent-subquery-magic)

About
-----

[](#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
-----

[](#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
------------

[](#installation)

```
composer require maksimru/eloquent-subquery-magic
```

### Supported operations (with examples)

[](#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

[](#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', '