The Laravel magic you know, now applied to joins.

Overview

Eloquent Power Joins

Laravel Supported Versions Actions Status MIT Licensed Latest Version on Packagist Total Downloads

The Laravel magic you know, now applied to joins.

Joins are very useful in a lot of ways. If you are here, you most likely know about and use them. Eloquent is very powerful, but it lacks a bit of the "Laravel way" when using joins. This package make your joins in a more Laravel way, with more readable with less code while hiding implementation details from places they don't need to be exposed.

A few things we consider is missing when using joins which are very powerful Eloquent features:

  • Ability to use relationship definitions to make joins;
  • Ability to use model scopes inside different contexts;
  • Ability to query relationship existence using joins instead of where exists;
  • Ability to easily sort results based on columns or aggregations from related tables;

You can read a more detailed explanation on the problems this package solves on this blog post.

Installation

You can install the package via composer:

composer require kirschbaum-development/eloquent-power-joins

Usage

On any model you want to be able to use the methods described below, you should use the following trait:

use Kirschbaum\PowerJoins\PowerJoins;

class User extends Model
{
    use PowerJoins;
}

This package provides a few features.

1 - Join Relationship

Let's say you have a User model with a hasMany relationship to the Post model. If you want to join the tables, you would usually write something like:

User::select('users.*')->join('posts', 'posts.user_id', '=', 'users.id');

This package provides you with a new joinRelationship() method, which does the exact same thing.

User::joinRelationship('posts');

Both options produce the same results. In terms of code, you didn't save THAT much, but you are now using the relationship between the User and the Post models to join the tables. This means that you are now hiding how this relationship works behind the scenes (implementation details). You also don't need to change the code if the relationship type changes. You now have more readable and less overwhelming code.

But, it gets better when you need to join nested relationships. Let's assume you also have a hasMany relationship between the Post and Comment models and you need to join these tables, you can simply write:

User::joinRelationship('posts.comments');

So much better, wouldn't you agree?! You can also left or right join the relationships as needed.

User::leftJoinRelationship('posts.comments');
User::rightJoinRelationship('posts.comments');

Joining polymorphic relationships

Let's imagine, you have a Image model that is a polymorphic relationship (Post -> morphMany -> Image). Besides the regular join, you would also need to apply the where imageable_type = Image::class condition, otherwise you could get messy results.

Turns out, if you join a polymorphic relationship, Eloquent Power Joins automatically applies this condition for you. You simply need to call the same method.

Post::joinRelationship('images');

Applying conditions & callbacks to the joins

Now, let's say you want to apply a condition to the join you are making. You simply need to pass a callback as the second parameter to the joinRelationship method.

User::joinRelationship('posts', function ($join) {
    $join->where('posts.approved', true);
})->toSql();

For nested calls, you simply need to pass an array referencing the relationship names.

User::joinRelationship('posts.comments', [
    'posts' => function ($join) {
        $join->where('posts.published', true);
    },
    'comments' => function ($join) {
        $join->where('comments.approved', true);
    }
]);

For belongs to many calls, you need to pass an array with the relationship, and then an array with the table names.

User::joinRelationship('groups', [
    'groups' => [
        'groups' => function ($join) {
            // ...
        },
        // group_members is the intermediary table here
        'group_members' => function ($join) {
            $join->where('group_members.active', true);
        },
    ]
]);

Using model scopes inside the join callbacks 🤯

We consider this one of the most useful features of this package. Let's say, you have a published scope on your Post model:

    public function scopePublished($query)
    {
        $query->where('published', true);
    }

When joining relationships, you can use the scopes defined in the model being joined. How cool is this?

User::joinRelationship('posts', function ($join) {
    // the $join instance here can access any of the scopes defined in Post 🤯
    $join->published();
});

When using model scopes inside a join clause, you can't type hint the $query parameter in your scope. Also, keep in mind you are inside a join, so you are limited to use only conditions supported by joins.

Using aliases

Sometimes, you are going to need to use table aliases on your joins because you are joining the same table more than once. One option to accomplish this is to use the joinRelationshipUsingAlias method.

Post::joinRelationshipUsingAlias('category.parent')->get();

In case you need to specify the name of the alias which is going to be used, you can do in two different ways:

  1. Passing a string as the second parameter (this won't work for nested joins):
Post::joinRelationshipUsingAlias('category', 'category_alias')->get();
  1. Calling the as function inside the join callback.
Post::joinRelationship('category.parent', [
    'category' => function ($join) {
        $join->as('category_alias');
    },
    'parent' => function ($join) {
        $join->as('category_parent');
    },
])->get()

Select * from table

When making joins, using select * from ... can be dangerous as fields with the same name between the parent and the joined tables could conflict. Thinking on that, if you call the joinRelationship method without previously selecting any specific columns, Eloquent Power Joins will automatically include that for you. For instance, take a look at the following examples:

User::joinRelationship('posts')->toSql();
// select users.* from users inner join posts on posts.user_id = users.id

And, if you specify the select statement:

User::select('users.id')->joinRelationship('posts')->toSql();
// select users.id from users inner join posts on posts.user_id = users.id

Soft deletes

When joining any models which uses the SoftDeletes trait, the following condition will be also automatically applied to all your joins:

and "users"."deleted_at" is null

In case you want to include trashed models, you can call the ->withTrashed() method in the join callback.

UserProfile::joinRelationship('users', function ($join) {
    $join->withTrashed();
});

You can also call the onlyTrashed model as well:

UserProfile::joinRelationship('users', function ($join) {
    $join->onlyTrashed();
});

Extra conditions defined in relationships

If you have extra conditions in your relationship definitions, they will get automatically applied for you.

class User extends Model
{
    public function publishedPosts()
    {
        return $this->hasMany(Post::class)->published();
    }
}

If you call User::joinRelationship('publishedPosts')->get(), it will also apply the additional published scope to the join clause. It would produce an SQL more or less like this:

select users.* from users inner join posts on posts.user_id = posts.id and posts.published = 1

2 - Querying relationship existence (Using Joins)

Querying relationship existence is a very powerful and convenient feature of Eloquent. However, it uses the where exists syntax which is not always the best and may not be the more performant choice, depending on how many records you have or the structure of your tables.

This packages implements the same functionality, but instead of using the where exists syntax, it uses joins. Below, you can see the methods this package implements and also the Laravel equivalent.

Laravel Native Methods

User::has('posts');
User::has('posts.comments');
User::has('posts', '>', 3);
User::whereHas('posts', function ($query) {
    $query->where('posts.published', true);
});
User::doesntHave('posts');

Package equivalent, but using joins

User::powerJoinHas('posts');
User::powerJoinHas('posts.comments');
User::powerJoinHas('posts.comments', '>', 3);
User::powerJoinWhereHas('posts', function ($join) {
    $join->where('posts.published', true);
});
User::powerJoinDoesntHave('posts');

3 - Order by

You can also sort your query results using a column from another table using the orderByPowerJoins method.

User::orderByPowerJoins('profile.city');

If you need to pass some raw values for the order by function, you can do like this:

User::orderByPowerJoins(['profile', DB::raw('concat(city, ", ", state)']);

This query will sort the results based on the city column on the user_profiles table. You can also sort your results by aggregations (COUNT, SUM, AVG, MIN or MAX).

For instance, to sort users with the highest number of posts, you can do this:

$users = User::orderByPowerJoinsCount('posts.id', 'desc')->get();

Or, to get the list of posts where the comments contain the highest average of votes.

$posts = Post::orderByPowerJoinsAvg('comments.votes', 'desc')->get();

You also have methods for SUM, MIN and MAX:

Post::orderByPowerJoinsSum('comments.votes');
Post::orderByPowerJoinsMin('comments.votes');
Post::orderByPowerJoinsMax('comments.votes');

In case you want to use left joins in sorting, you also can:

Post::orderByLeftPowerJoinsCount('comments.votes');
Post::orderByLeftPowerJoinsAvg('comments.votes');
Post::orderByLeftPowerJoinsSum('comments.votes');
Post::orderByLeftPowerJoinsMin('comments.votes');
Post::orderByLeftPowerJoinsMax('comments.votes');

Contributing

Please see CONTRIBUTING for details.

Security

If you discover any security related issues, please email [email protected] or [email protected] instead of using the issue tracker.

Credits

Sponsorship

Development of this package is sponsored by Kirschbaum Development Group, a developer driven company focused on problem solving, team building, and community. Learn more about us or join us!

License

The MIT License (MIT). Please see License File for more information.

Laravel Package Boilerplate

This package was generated using the Laravel Package Boilerplate.

Comments
  • POSTGRESQL Column alias is not supported in WHERE or HAVING clauses.

    POSTGRESQL Column alias is not supported in WHERE or HAVING clauses.

    Hi there,

    First of all thank you for your work.

    I'm starting to use your library but I'm stuck because the powerJoinWhereHas function doesn't work in my context. Having count causes the following problem: "SQLSTATE[42703]: Undefined column: 7 ERROR: column "products_count" does not existLINE 1: ...al"::text ILIKE $1 group by "castles".."id" having products_c ..." because in PGSQL you can't put an alias. You have to put the condition again so it will give "HAVING count(table.column) > 1

    Do you have another solution or can you fix this issue without impacting SQL SERVER or MYSQL users?

    Thanks very much, Have a good day

    PGSQL : 14 LARAVEL : 9.23 PHP : 8.1

    https://www.enterprisedb.com/postgres-tutorials/how-use-tables-and-column-aliases-when-building-postgresql-query

    opened by FlorianChretien 11
  • Global scopes

    Global scopes

    I like this package, but global scopes are not applied to anything I join.

    There is that hardcoded case of soft deletes, but no solution ready for other global scopes.

    Is there a good way to add global scopes to a joined relation?

    Example:

    class Bar extends Model {
        public function foos(): HasMany { }
    }
    class Foo extends Model {
        // hide all foo with hidden = 1
        use WithHiddenScope;
    }
    
    // all foo are considered, even the ones with hidden = 1
    $bar = Bar::joinRelationship('foos');
    
    opened by thekonz 11
  • [2.3.3] call multi joinRelationship function query builder

    [2.3.3] call multi joinRelationship function query builder

                $query->where('status', 'ACTIVE');
            });
    
    $queryBuilder->joinRelationship('relationships', function ($query) use ($condition) {
                $query->where('role', 'ADMIN');
            });```
    
    The query just accept first condition, as I see in PowerJoins::scopeJoinRelationship, we check relationshipAlreadyJoined
    So, this function will be return.
    Please help to support multi where statement like above, I tried with order it work well.
    opened by phuongdm1987 11
  • Issue with aliased joins

    Issue with aliased joins

    Hey there,

    I am having issues, using nested relationships that have been aliased. Take this example:

    Purchase::where('purchases.id', 1)
    			->leftJoinRelationShip('serial.product', [
    				'serial' => function ($join) {
    					$join->as('purchase_serial');
    				},
    				'product' => function ($join) {
    					$join->as('purchase_product');
    				},
    			])
    			->first();
    

    This will produce the following error:

    SQLSTATE[42S22]: Column not found: 1054 Unknown column 'serials.product_id' in 'on clause' (SQL: select purchases.* from purchases left join serials as purchase_serial on purchase_serial.code = purchases.purchase_code left join products as purchase_product on serials.product_id = purchase_product.id where purchases.id = 1 limit 1)

    That's because the on clause does not take the aliased table name into account. So, insead of

    on `serials`.`product_id` = `purchase_product`.`id`
    

    it should actually be

    on `purchase_serial`.`product_id` = `purchase_product`.`id`
    

    I went ahead and tried to manually specify the on clause like this

    Purchase::where('purchases.id', 1)
    			->leftJoinRelationShip('serial.product', [
    				'serial' => function ($join) {
    					$join->as('purchase_serial');
    				},
    				'product' => function ($join) {
    					$join->as('purchase_product')->on('purchase_serial.product_id', '=', 'purchase_product.id');
    				},
    			])
    			->first();
    

    Unfortunately this will append the on clause, instead of replacing it and will therefore result in this error:

    SQLSTATE[42S22]: Column not found: 1054 Unknown column 'serials.product_id' in 'on clause' (SQL: select purchases.* from purchases left join serials as purchase_serial on purchase_serial.code = purchases.purchase_code left join products as purchase_product on serials.product_id = purchase_product.id and purchase_serial.product_id = purchase_product.id where purchases.id = 1 limit 1)

    Thanks for your time!

    bug low priority 
    opened by Shadow505 8
  • How to perform the following simple join

    How to perform the following simple join

    Select
        *
    From
        users  INNER JOIN
        user_packages On user_packages.user_id = users.id  Join
        packages On user_packages.package_id = packages.id INNER Join
        package_add_ons On package_add_ons.package_id = packages.id
        
    

    The modules User

        public function UserPackages(): \Illuminate\Database\Eloquent\Relations\HasMany
        {
            return $this->hasMany(UserPackage::class);
        }
    
    

    UserPackage

    public function User(): \Illuminate\Database\Eloquent\Relations\BelongsTo
      {
          return $this->belongsTo(User::class);
      }
    
      public function Packages(): \Illuminate\Database\Eloquent\Relations\BelongsToMany
      {
          return $this->belongsToMany(Package::class);
      }
    

    Package

      public function PackageAddOns(): \Illuminate\Database\Eloquent\Relations\HasMany
       {
           return $this->hasMany(PackageAddOn::class);
       }
    
       public function UserPackages(): \Illuminate\Database\Eloquent\Relations\HasMany
       {
           return $this->hasMany(UserPackage::class);
       }
    

    PackageAddOn

        public function Package(): \Illuminate\Database\Eloquent\Relations\BelongsTo
        {
            return $this->belongsTo(Package::class);
        }
    
        public function UserPackage(): \Illuminate\Database\Eloquent\Relations\BelongsTo
        {
            return $this->belongsTo(UserPackage::class);
        }
    
    opened by beshoo 7
  • Join to self realtion (Feature Request: Join alias)

    Join to self realtion (Feature Request: Join alias)

    Hello.

    First off, Thank you for making this awesome package.

    I have a Model called category and it has a many-to-many relation to itself like this.

    class Category extends Model
    {
        public function subCategories()
        {
            return $this->belongsToMany(Category::class, 'category_children', 'parent_id', 'child_id');
        }
    
        public function parentCategories()
        {
            return $this->belongsToMany(Category::class, 'category_children', 'child_id', 'parent_id');
        }
    }
    

    And also have a Product model.

    class Product extends Model
    {
        public function categories()
        {
            return $this->belongsToMany(Category::class);
        }
    }
    

    Now I can use join relationship in Product.

     Product::joinRelationship('categories')->first();
    

    But can not join nested relationship.

    Product::joinRelationship('categories.parentCategories')->first();
    

    Error:

    Illuminate/Database/QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'categories' (SQL: ...)' 
    

    It would be nice to have join alias feature to fix this. something like this.

    Product::joinRelationship('categories.parentCategories', [
        'categories.parentCategories' => function ($join) {
             $join->as('parent_categories');
        }
    ])->first();
    

    Or

    Product::joinRelationship('categories.parentCategories', ['categories.parentCategories as parent_categories'])->first();
    
    // And/Or
    
    Product::joinRelationship('categories.parentCategories', ['categories.parentCategories as parent_categories' => function ($join) {
        $join->...
    }])->first();
    

    I used ['categories.parentCategories' => ] instead of ['parentCategories' => ] because maybe I have a join like this.

    Product::joinRelationship('categories.parentCategories.parentCategories')
    

    Then parentCategories is ambighous.

    enhancement 
    opened by amir9480 6
  • Nested joins with different join type

    Nested joins with different join type

    hi, thanks for your work

    Is it possible to join nested relationships with different join types?

    User::query()
        ->join('posts', 'posts.user_id', '=', 'users.id')
        ->leftJoin('comments', 'comments.post_id', '=', 'posts.id')
        ->toSql()
    
    enhancement 
    opened by cirolosapio 5
  • unkdown issue

    unkdown issue

    Hi, I'm here again, and I'm sure you're going to hate me.

    I have found a issue, and it was difficult for me to replicate it to give you an example, since I could not see where it came from, here I write the details:

    From Code:

    for ($i = 0; $i < 2; $i++) {
        Page::joinRelationship('translation')->first();
    }
    Page::with('translations')->get();
    dd(Page::joinRelationship('translation')->toSql());
    

    //Output select pages.* from pages


    From Code:

    for ($i = 0; $i < 1; $i++) {
        Page::joinRelationship('translation')->first();
    }
    Page::with('translations')->get();
    dd(Page::joinRelationship('translation')->toSql());
    

    //Output select pages.* from pagesinner joinpage_translationsonpage_translations.page_id=pages.idandpage_translations.locale` = ?


    From Code:

    for ($i = 0; $i < 2; $i++) {
        Page::joinRelationship('translation')->first();
    }
    //Page::with('translations')->get();
    dd(Page::joinRelationship('translation')->toSql());
    

    //Output select pages.* from pagesinner joinpage_translationsonpage_translations.page_id=pages.idandpage_translations.locale` = ?

    Code 2 and 3 are correct, but code 1 is wrong, it is very rare and only appears in certain cases as I have shown you in the examples that I have replicated.

    I hope you can know why this happens because I have no idea.

    Hacktoberfest 
    opened by mcolominas 5
  • issue with saved relationship system

    issue with saved relationship system

    From what I understand from your code, the relationships are stored in an array, and it does not admit 2 with the same name even though they have different parents, for example:

    Product::joinRelationship('translation')->joinRelationship('features.translation')

    The previous code only gets the relationships of 'translation' from the first join, and the second join only gets the relation of 'features', ignoring the 'translation'.

    The problem comes in this line of code:

         /**
         * Checks if the relationship was already joined.
         */
        public function relationshipAlreadyJoined($relation)
        {
            return isset(PowerJoins::$joinRelationshipCache[spl_object_id($this)][$relation]);
        }
    
        /**
         * Marks the relationship as already joined.
         */
        public function markRelationshipAsAlreadyJoined($relation)
        {
            PowerJoins::$joinRelationshipCache[spl_object_id($this)][$relation] = true;
        }
    

    It can be fixed modifying the following: In scopeJoinNestedRelationship update to:

    $latestRelation = null;
    $relationNameWithParents = null;
    foreach ($relations as $index => $relationName) {
        ...
        $relationCallback = null;
        $relationNameWithParents = isset($relationNameWithParents) ? $relationNameWithParents . '.' . $relationName : $relationName;
        ...
    
        if ($this->relationshipAlreadyJoined($relationNameWithParents)) {
            $latestRelation = $relation;
            continue;
        }
        ...
        $this->markRelationshipAsAlreadyJoined($relationNameWithParents);
    }
    
    opened by mcolominas 5
  • Fatal Error

    Fatal Error

    I'm getting this error: Fatal error: Uncaught BadMethodCallException: Call to undefined method Illuminate\Database\Eloquent\Builder::getSelect()

    The issue is that the PowerJoins trait tries to call getSelect in the scopeJoinRelationship method. The code giving me issues is using Laravel 6. Below is what I wrote when calling it.

    $recipients = UserData::joinRelationship('users')->where('resub_sent', false)->dd();

    Here's what UserData looks like.

    use Illuminate\Database\Eloquent\Model;
    use Kirschbaum\PowerJoins\PowerJoins;
    
    class UserData extends Model
    {
    	use PowerJoins;
    }
    

    Full error:

    Fatal error: Uncaught BadMethodCallException: Call to undefined method Illuminate\Database\Eloquent\Builder::getSelect() in [REDACTED]/vendor/illuminate/support/Traits/ForwardsCalls.php:50
    Stack trace:
    #0 [REDACTED]/vendor/illuminate/support/Traits/ForwardsCalls.php(36): Illuminate\Database\Eloquent\Builder::throwBadMethodCallException()
    #1 [REDACTED]/vendor/illuminate/database/Eloquent/Builder.php(1377): Illuminate\Database\Eloquent\Builder->forwardCallTo()
    #2 [REDACTED]/vendor/kirschbaum-development/eloquent-power-joins/src/PowerJoins.php(43): Illuminate\Database\Eloquent\Builder->__call()
    #3 [REDACTED]/vendor/illuminate/database/Eloquent/Builder.php(986): [WordPressProj]\Models\UserData->scopeJoinRelationship()
    #4 [REDACTED]/vendor/illuminate/database/Eloquent/Builder.php(1370): in [REDACTED]/vendor/illuminate/support/Traits/ForwardsCalls.php on line 50
    

    Please note that this implementation is being used outside of a Laravel project, but it uses the following things inside composer.json.

    "illuminate/database" : "^6.0",
    "illuminate/filesystem" : "^6.0",
    "illuminate/translation" : "^6.0",
    "illuminate/validation" : "^6.0",
    "illuminate/pagination" : "^6.0",
    "kirschbaum-development/eloquent-power-joins" : "^2.1"
    

    If I'm missing something that PowerJoins relies on, please let me know. It just seems strange that in the years I've been working with Laravel on this same project, that this would be the first time I'd have something missing that Eloquent ORM needs to function in a given way.

    opened by rickmacgillis 5
  • Join missing from query

    Join missing from query

    If you have two different relations to the same table and then have a shared relation on those relations, then if you trying querying them you end up missing 1 join in the resulting query. An example will make this much clearer I think:

     $query =  \App\Models\Event::query()
                ->leftJoinRelationShip('location.planet', [
                    'location' => function($join){
                        $join->as('location_alias');
                    },
                    'planet' => function($join){
                        $join->as('location_planet_alias');
                    }
                ])
                ->leftJoinRelationShip('secondaryLocation.planet', [
                    'secondaryLocation' => function($join){
                        $join->as('secondary_location_alias');
                    },
                    'planet' => function($join){
                        $join->as('secondary_location_planet_alias');
                    }
               ])
               ;
    

    Results in:

    select `events`.* from `events` 
        left join `locations` as `location_alias` on `events`.`location_id` = `location_alias`.`id` 
        left join `planets` as `location_planet_alias` on `location_alias`.`planet_id` = `location_planet_alias`.`id` 
        left join `locations` as `secondary_location_alias` on `events`.`secondary_location_id` = `secondary_location_alias`.`id`
    

    The there should also have been another left join to planets for the secondary location.

    I think it caused by the usage of the $relationJoinCache in the method scopeJoinNestedRelationship.

    $relationJoinCache = $index === 0
                    ? "{$relation->getQuery()->getModel()->getTable()}.{$index}.{$relationName}"
                    : "{$relation->getQuery()->getModel()->getTable()}.{$latestRelation->getModel()->getTable()}.{$relationName}";
    

    It uses the {$latestRelation->getModel()->getTable()} (tablename) but I think it should probably be useing the latestRelation's relationname right?

    This seems to fix the problem:

    $relationJoinCache = $index === 0
                    ? "{$relation->getQuery()->getModel()->getTable()}.{$index}.{$relationName}"
                    : "{$relation->getQuery()->getModel()->getTable()}.{$latestRelation->getRelationName()}.{$relationName}";
    

    Is this something you can change / would merge if I create a Pr?

    bug 
    opened by jonneroelofs 4
  • Bug when joining same relation multiple times

    Bug when joining same relation multiple times

    Hi,

    look at this query builder:

    $query = Product::query()->powerJoinWhereHas(
                'groups',
                [
                    'groups' => function ($join) {
                        $join->as('group_1')->where('id', 2);
                    },
                    'group_product' => function ($join) {
                        $join->as('pivot_group_1');
                    }
                ]
            )->powerJoinWhereHas(
                'groups',
                [
                    'groups' => function ($join) {
                        $join->as('group_2')->where('id', 3);
                    },
                    'group_product' => function ($join) {
                        $join->as('pivot_group_2');
                    }
                ]
            );
    

    and at result query:

    SELECT
        `products`.*,
        COUNT(groups.id) AS groups_count,
        COUNT(groups.id) AS groups_count
    FROM
        `products`
    LEFT JOIN `group_product` AS `pivot_group_1`
    ON
        `group_product`.`product_id` = `products`.`id`
    LEFT JOIN `groups` AS `group_1`
    ON
        `group_1`.`id` = `group_product`.`group_id` AND `id` = 2
    LEFT JOIN `group_product` AS `pivot_group_2`
    ON
        `group_product`.`product_id` = `products`.`id`
    LEFT JOIN `groups` AS `group_2`
    ON
        `group_2`.`id` = `group_product`.`group_id` AND `id` = 3
    GROUP BY
        `products`.`id`
    HAVING
        groups_count >= 1 AND groups_count >= 1
    

    Here is some problems:

    1. This
     COUNT(groups.id) AS groups_count,
        COUNT(groups.id) AS groups_count
    

    should use alias, and be this:

     COUNT(group_1.id) AS group_1_count,
        COUNT(group_2.id) AS group_2_count
    
    1. This
    HAVING
        groups_count >= 1 AND groups_count >= 1
    

    should be this:

    HAVING
        group_1_count >= 1 AND group_2_count >= 1
    
    1. This
    LEFT JOIN `group_product` AS `pivot_group_1`
    ON
        `group_product`.`product_id` = `products`.`id`
    

    should be this:

    LEFT JOIN `group_product` AS `pivot_group_1`
    ON
        `pivot_group_1`.`product_id` = `products`.`id`
    

    and similarly for the second group.

    1. This
    LEFT JOIN `groups` AS `group_1`
    ON
        `group_1`.`id` = `group_product`.`group_id` AND `id` = 2
    

    should be this:

    LEFT JOIN `groups` AS `group_1`
    ON
        `group_1`.`id` = `group_product`.`group_id` AND .`group_1`.`id` = 2
    

    but this can be done in where condition.

    Checking multiple times same relations is useful in many cases, e.g. product filteres based on categories, groups, facets or something similar.

    opened by webard 1
  • Morph to Many using JoinRelationShipUsingAlias('relation_name') not working correctly

    Morph to Many using JoinRelationShipUsingAlias('relation_name') not working correctly

    Hello,

    I join the table attributes with a MoprhMany to lookups with lookupables. In attributes I create the relation in this way

    return $this->morphToMany(Lookup::class, 'lookupable')->where('lookups.type', 'attribuut_datatype');

    I call the powerjoinfunction: joinRelationshipUsingAlias, with only the name of the relation as a parameter

    Powerjoin creates the following query:

    inner join `lookupables` as `31816ced6e12347e02dbcd4865c7f779` on `31816ced6e12347e02dbcd4865c7f779`.`lookupable_id` = `attributen`.`id` 
    inner join `lookups` as `52c1d48dd796b76bfe00982d5469c0d5` on `52c1d48dd796b76bfe00982d5469c0d5`.`id` = `31816ced6e12347e02dbcd4865c7f779`.`lookup_id` 
            and `52c1d48dd796b76bfe00982d5469c0d5`.`deleted_at` is null 
            and `lookupables`.`lookupable_type` = ? and `52c1d48dd796b76bfe00982d5469c0d5`.`type` = ?
    

    As you can see. the lookupable_type is not using the alias.

    Kind Regards Jurjen

    opened by JurjenRoels 1
  • Can't seem to filter when using power join

    Can't seem to filter when using power join

    We're attempting to use this package to easily swap out our queries written with whereHas to use joins instead.

    However, when we went to change some of our queries to use the powerJoinWhereHas method, we see that the join is there in the SQL query but we can't see the ->whereIn('bed_types.id', $ids) bit in our SQL query anywhere.

    Code

    // app/Models/Product.php
    
    public function scopeHasBedType($query, $id)
    {
        $ids = Arr::wrap($id);
    
        return $query->powerJoinWhereHas('bedTypes', function ($query) use ($ids) {
            $query->whereIn('bed_types.id', $ids);
        });
    }
    
    public function bedTypes()
    {
        return $this
            ->belongsToMany(BedType::class)
            ->using(BedTypeProduct::class);
    }
    
    // app/Models/BedType.php
    <?php
    
    namespace App\Models;
    
    use Illuminate\Database\Eloquent\Factories\HasFactory;
    use Illuminate\Database\Eloquent\Model;
    use Illuminate\Support\Facades\Cache;
    use Rennokki\QueryCache\Traits\QueryCacheable;
    use Spatie\EloquentSortable\SortableTrait;
    
    class BedType extends Model
    {
        use HasFactory;
        use SortableTrait;
        use QueryCacheable;
    
        public $sortable = [
            'order_column_name' => 'sort_order',
            'sort_when_creating' => true,
        ];
    
        protected $guarded = [];
    
        /**
         * Invalidate the cache automatically
         * upon update in the database.
         *
         * @var bool
         */
        protected static $flushCacheOnUpdate = true;
    
        /**
         * Specify the amount of time to cache queries.
         * Do not specify or set it to null to disable caching.
         *
         * @var int|\DateTime
         */
        public $cacheFor = 86400;
    
        public function products()
        {
            return $this->belongsToMany(Product::class);
        }
    }
    

    Table Structure

    bed_types

    • id
    • name
    • created_at
    • updated_at
    • sort_order

    bed_type_product

    • id
    • bed_type_id
    • product_id
    • created_at
    • updated_at

    Additional information

    • Laravel 9.31.0
    • PHP 8.1.8
    • Power Joins 2.6.4
    opened by duncanmcclean 1
  • Conditioning of joins is not consistent

    Conditioning of joins is not consistent

    I have these two examples:

    $queryBuilder1 = User::query()->joinRelationship('posts.comments', [ 'posts' => function ($join) { $join->where('posts.published', true); }, ]); and $queryBuilder2 = User::query()->joinRelationship('posts', [ 'posts' => function ($join) { $join->where('posts.published', true); }, ]); dump($queryBuilder1->toSql(), $queryBuilder2->toSql()); The only difference is the nesting o relationships... BUT... While the first one (with 'posts.comments') applies correctly where condition - the second one - ignores it completely.

    opened by dmikam 2
  • Join type in nested sub-relations

    Join type in nested sub-relations

    Possibility to indicate join type for each relation in joinRelationship callbacks:

    User::joinRelationship('posts.comments', [     'posts' => function ($join) {         $join->where('posts.published', true);     },     'comments' => function ($join) {         $join->where('comments.approved', true);         $join->left(); // you may also use $join->inner() or directly $join->joinType('leftPowerJoin')     } ]);

    Probable not the most effective way, but the least intrusive.

    tests missing 
    opened by dmikam 6
  • Aliases cannot be used on the main model

    Aliases cannot be used on the main model

    Versions of packages used and PHP. PHP: 8.1 Laravel: 9.16 Eloquent-power-joins: 2.6.4

    If I set an alias to the main model from which the relationships originate, the relationships fail, because they refer to the name of the model's table, instead of the alias if it exists. At all times I see that reference is made to getModel()->getTable(), that is why the table is always searched, when I think that reference should be made to getQuery()->from, which contains the name of the model table or, failing that, the name with the defined alias. In case I am wrong in referring to the way to get the alias, then use the best way to get the name of the table with its alias if it exists. In my case I like to use aliases, because sometimes the names of the tables are very long and it is very inconvenient to write the name several times when using relationships.

    Successful example

    $data = ProviderProduct::query()
       ->joinRelationshipUsingAlias('provider', 'p')
       ->joinRelationship(
           'productVariation.parentProduct',
           [
               'productVariation' => fn($join) => $join->as('pvp'),
               'parentProduct' => fn($join) => $join->as('pr'),
           ]
       )
       ->select(
           [
               'provider_products.id AS provider_product_id',
               'provider_products.provider_id',
               'p.company_id',
               'provider_products.product_brand_id',
               'provider_products.product_variation_product_id AS variation_id',
               'pvp.product_id',
               'provider_products.provider_code',
               'provider_products.cost AS provider_cost',
               'provider_products.price AS provider_price',
           ]
       )
       ->get();
    

    Failed example

    $data = ProviderProduct::from('product_providers', 'pp')
       ->joinRelationshipUsingAlias('provider', 'p')
       ->joinRelationship(
           'productVariation.parentProduct',
           [
               'productVariation' => fn($join) => $join->as('pvp'),
               'parentProduct' => fn($join) => $join->as('pr'),
           ]
       )
       ->select(
           [
               'pp.id AS provider_product_id',
               'pp.provider_id',
               'p.company_id',
               'pp.product_brand_id',
               'pp.product_variation_product_id AS variation_id',
               'pvp.product_id',
               'pp.provider_code',
               'pp.cost AS provider_cost',
               'pp.price AS provider_price',
           ]
       )
       ->get();
    
    Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 
    'provider_products.product_variation_product_id' in 'on clause' (SQL: select `pp`.`id` as `provider_product_id`, `pp`.`provider_id`, 
    `p`.`company_id`, `pp`.`product_brand_id`, `pp`.`product_variation_product_id` as `variation_id`, `pvp`.`product_id`, `pp`.`provider_code`, 
    `pp`.`cost` as `provider_cost`, `pp`.`price` as `provider_price` from `provider_products` as `pp` inner join `providers` as `p` on 
    `provider_products`.`provider_id` = `p`.`id` inner join `product_variation_products` as `pvp` on 
    `provider_products`.`product_variation_product_id` = `pvp`.`id` inner join `products` as `pr` on `pvp`.`product_id` = `pr`.`id` limit 5)'
    

    On the other hand, I wanted to comment that in the PowerJoins trait on lines 139 and 139 the variable $alias is repeated.

    138     $alias = $this->getAliasName($useAlias, $relation, $relationName, $relation->getQuery()->getModel()->getTable(), $callback);
    139     $alias = $useAlias ? $this->generateAliasForRelationship($relation, $relationName) : null;
    
    opened by waiaryi 3
Releases(2.6.5)
  • 2.6.5(Dec 9, 2022)

    What's Changed

    • Improve docs and tests on joins with a intermediate table by @marickvantuil in https://github.com/kirschbaum-development/eloquent-power-joins/pull/103
    • Fix class name in Joining polymorphic relationships section of Readme by @fabalv in https://github.com/kirschbaum-development/eloquent-power-joins/pull/108
    • Allow Macro calls on JoinClause by @glamax13 in https://github.com/kirschbaum-development/eloquent-power-joins/pull/112

    New Contributors

    • @marickvantuil made their first contribution in https://github.com/kirschbaum-development/eloquent-power-joins/pull/103
    • @fabalv made their first contribution in https://github.com/kirschbaum-development/eloquent-power-joins/pull/108
    • @glamax13 made their first contribution in https://github.com/kirschbaum-development/eloquent-power-joins/pull/112

    Full Changelog: https://github.com/kirschbaum-development/eloquent-power-joins/compare/2.6.4...2.6.5

    Source code(tar.gz)
    Source code(zip)
  • 2.6.4(Apr 6, 2022)

    What's Changed

    • Fixed using withTrashed and having an alias on a join clause by @jonneroelofs in https://github.com/kirschbaum-development/eloquent-power-joins/pull/97

    Full Changelog: https://github.com/kirschbaum-development/eloquent-power-joins/compare/2.6.3...2.6.4

    Source code(tar.gz)
    Source code(zip)
  • 2.6.3(Mar 5, 2022)

  • 2.6.2(Mar 5, 2022)

    Using some smarter logic to generate the aliases which fixes some of the checks for already joined relationships.

    Fixed #90 Hopefully fixes #77

    Source code(tar.gz)
    Source code(zip)
  • 2.6.1(Feb 15, 2022)

  • 2.6.0(Feb 9, 2022)

  • 2.5.2(Feb 6, 2022)

  • 2.5.1(Nov 18, 2021)

  • 2.5.0(Oct 24, 2021)

  • 2.4.1(Aug 30, 2021)

  • 2.4.0(Apr 18, 2021)

  • 2.3.3(Apr 7, 2021)

  • 2.3.2(Dec 29, 2020)

  • 2.3.1(Dec 22, 2020)

  • 2.3.0(Nov 28, 2020)

  • 2.2.3(Oct 30, 2020)

  • 2.2.2(Oct 24, 2020)

  • 2.2.1(Oct 14, 2020)

  • 2.2.0(Oct 9, 2020)

  • 2.1.0(Sep 10, 2020)

    Added the ability to include trashed results by using the join callback:

    UserProfile::joinRelationship('users', function ($join) {
        $join->withTrashed();
    });
    
    ```php
    UserProfile::joinRelationship('users', function ($join) {
        $join->onlyTrashed();
    });
    
    Source code(tar.gz)
    Source code(zip)
  • 2.0.0(Aug 30, 2020)

    • Laravel 8 support;
    • PHP 8 support;
    • A new trait that needs to be used in models;
    • Namespace change which should not affect migrating users from 1.0;
    • Automatically apply extra conditions from the relationship;
    • Ability to order by using left joins;
    • Changed the method signature for sorting;
    • Changed the method signature for querying relationship existence;
    • Bugfixes that were not possible by using the architecture from 1.0;
    Source code(tar.gz)
    Source code(zip)
  • 1.1.1(May 14, 2020)

  • 1.1.0(Apr 13, 2020)

    This release implemented the ability to use table aliases in the joins being made. It can be used in two ways.

    Automatic

    By calling the joinRelationshipUsingAlias method.

    Post::joinRelationshipUsingAlias('category.parent')->get();
    

    This generates the following query:

    SELECT "posts".* FROM "posts"
        INNER JOIN "categories" AS "40071de3fbcf4e9399e166813abb7718" ON "posts"."category_id" = "40071de3fbcf4e9399e166813abb7718"."id"
        INNER JOIN "categories" AS "ccecda2ab44b29ec904f7bdea7e5aa2e" ON "40071de3fbcf4e9399e166813abb7718"."parent_id" = "ccecda2ab44b29ec904f7bdea7e5aa2e"."id"
    

    By calling ->alias

    You can also manually specify the table alias you want to use in the join callbacks.

    Post::joinRelationship('category.parent', [
        'parent' => function ($join) {
            $join->as('category_parent');
        },
    ])->get();
    

    This generates the following query:

    SELECT "posts".* FROM "posts"
        INNER JOIN "categories" ON "posts"."category_id" = "categories"."id"
        INNER JOIN "categories" AS "category_parent" ON "categories"."parent_id" = "category_parent"."id"
    
    Source code(tar.gz)
    Source code(zip)
Owner
Kirschbaum Development Group, LLC
We are a team of carefully curated Laravel experts with a history of delivering practical and efficient solutions to complex problems.
Kirschbaum Development Group, LLC
GeoLocation-Package - This package helps you to know the current language of the user, the country from which he is browsing, the currency of his country, and also whether he is using it vpn

GeoLocation in PHP (API) ?? ?? ?? This package helps you to know a lot of information about the current user by his ip address ?? ?? ?? This package h

Abdullah Karam 4 Dec 8, 2022
Laravel Seo package for Content writer/admin/web master who do not know programming but want to edit/update SEO tags from dashboard

Laravel Seo Tools Laravel is becoming more and more popular and lots of web application are developing. In most of the web application there need some

Tuhin Bepari 130 Dec 23, 2022
The magic of Inertia.js with the simplicity of Blade

Laravel Splade The magic of Inertia.js, with the simplicity of Blade. Support Splade. We proudly support the community by developing Laravel packages

Protone Media 702 Dec 29, 2022
A Laravel Wrapper for the CoinDCX API. Now easily connect and consume the CoinDCX Public API in your Laravel apps without any hassle.

This package provides a Laravel Wrapper for the CoinDCX API and allows you to easily communicate with it. Important Note This package is in early deve

Moinuddin S. Khaja 2 Feb 16, 2022
Send ping to search engine by Index now protocol.

Index now PHP protocol Easy to use protocol that websites can call to notify search engines whenever website contents on any URL is updated or created

Baraja packages 3 Sep 10, 2022
If you are beginner in WordPress plugin development or if you want to develop your own store product plugin you use this plugin

hirwa-products-plugin If you are beginner in WordPress plugin development or if you want to develop your own store product plugin you use this plugin

NIYIBIZI HIRWA 1 Aug 23, 2022
Foreman is a Laravel scaffolding application that automates common tasks you typically perform with each new Laravel app you create

Foreman is a Laravel scaffolding application that automates common tasks you typically perform with each new Laravel app you create. The directives you want Forman to perform are outlined in a JSON based template file.

Indatus 145 Apr 13, 2022
laravel package help you to implement geographical calculation, with several algorithms that help you deal with coordinates and distances.

Geographical Calculator Geographical Calculator was developed for laravel 5.8+ to help you to implement geographical calculation, with With several al

karam mustafa 342 Dec 31, 2022
A series of methods that let you manipulate colors. Just incase you ever need different shades of one color on the fly.

PHPColors A series of methods that let you manipulate colors. Just incase you ever need different shades of one color on the fly. Requirements PHPColo

Arlo Carreon 423 Dec 20, 2022
With dadjokes every time you load your control panel you'll be greeted by an epic dad joke on the dashboard.

Filament Dad Jokes Widget With DadJokes every time you load your control panel you'll be greeted by an epic dad joke on the dashboard. Installation Yo

Craig Smith 15 Jan 7, 2023
You already have your dream house? Sam Building will help you find the perfect home.

SAM BUILDING Setup Fork or clone this repo! git clone github.com/Igorballo/Real-Estate-App.git Installation des dépendances npm install #or yarn insta

null 4 Nov 29, 2022
Electrik is a full-featured, open-source, starter-kit to help you build you your SaaS application.

Electrik Electrik is a full-featured and open-source stater-kit for for your next SaaS application. It's built on top of Laravel, Livewire, neerajsoha

Electrik 129 Dec 31, 2022
Laravel Kickstart is a Laravel starter configuration that helps you build Laravel websites faster.

Laravel Kickstart What is Laravel Kickstart? Laravel Kickstart is a Laravel starter configuration that helps you build Laravel websites faster. It com

Sam Rapaport 46 Oct 1, 2022
Laravel Larex lets you translate your whole Laravel application from a single CSV file.

Laravel Larex Translate Laravel Apps from a CSV File Laravel Larex lets you translate your whole Laravel application from a single CSV file. You can i

Luca Patera 68 Dec 12, 2022
A Laravel chat package. You can use this package to create a chat/messaging Laravel application.

Chat Create a Chat application for your multiple Models Table of Contents Click to expand Introduction Installation Usage Adding the ability to partic

Tinashe Musonza 931 Dec 24, 2022
Laravel Podcast is Laravel 5.5 web app that enables you to manage RSS feeds for your favorite podcasts and listen to the episodes in a seamless UI and User Authentication.

Laravel Podcast is Laravel 5.5 web app that enables you to manage RSS feeds for your favorite podcasts and listen to the episodes in a seamless UI and

Jeremy Kenedy 35 Dec 19, 2022
Aliyun oss filesystem storage adapter for laravel 5. You can use Aliyun OSS just like laravel Storage as usual

Aliyun oss filesystem storage adapter for laravel 5. You can use Aliyun OSS just like laravel Storage as usual

jacob 517 Dec 29, 2022
Laravel Query Helper was developed for laravel 7.2+ to help you optimize sql queries

Laravel Query Helper Laravel Query Helper was developed for laravel 7.2+ to help you optimize sql queries, this package will contain all advanced SQL

Syrian Open Source 15 Nov 20, 2022
Packagit is amazing laravel modules management, you could manage huge project with many separate laravel modules.

Packagit is amazing laravel modules management, you could manage huge project with many separate laravel modules.

null 364 Dec 12, 2022