kirschbaum-development / eloquent-power-joins Goto Github PK

How to orderByPowerJoins if the field is need like
DB::raw('concat(firstname, ' ', lastname)')

im try to install and get this error:

In ProviderRepository.php line 208:

Class 'KirschbaumDevelopment\EloquentJoins\PowerJoinsServiceProvider' not found

hello can you see it, is a bug?

https://prnt.sc/uag1yq

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

This is a great package have you considered to request that this becomes a part of Laravel? Specially the join on relationships is the lost feature of eloquent.

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.

Hello,

How would you return a result with powerjoins when you have an order table that belongs to customer can have employee and you also need to know the creator.

customer_id <-- references customers table to whom this order belongs to
employee_id <-- the order manager who is assigned to this order,
creator_id <-- user who created this order,
creator <-- user type customer/employee

currently i'm using raw query where i join both tables customers and employees for creator_id and then do if condition using value of creator column..

the scenerio is very specific but I was exploring your package so i thought you could help.

P.s I know I should move creator to different table.

Thanks in advance!

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

Currently, the soft delete clauses are always applied to the joins if the model uses soft deletes. But, you might want to include trashed models in your query.

Just relying on the withTrashed() from the query itself is not really reliable, as you are not telling that you want to include trashed models on the joined table.

This could be a method you call fluently, like:

User::withTrashedJoins('posts')->joinRelationship('posts');

It could be also a method like:

User::joinRelationshipWithTrashed('posts')

Open to suggestions for the API.

When using Model::orderByUsingJoins() you use an inner join query, however, this causes rows to be missing if they don't have the required relationship filled.

Is there are way to use left joins when using Mode::orderByUsingJoins()?

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.

Hey, I really like you extension! It helped me optimizing my queries a lot! But I came across an Exception when using nested where clauses inside a join clause:

$query = Events::joinRelationship('dates', function ($join) use ($dateFrom, $dateTo) {
    $join->as('date')
        ->where(function ($query) use ($dateFrom) {
            $query->where("date.start", ">=", $dateFrom)
                ->orWhere("date.end", ">=", $dateFrom);
        })
        ->where("date.start", "<=", $dateTo);
});

The reason is that PowerJoinClause::where doesn't accept a Closure as it's first parameter. An Exception is thrown from the Str::contains function, because $column is not a string:

If you take a look at the method from the parent class Illuminate\Database\Query\Builder::where, you can see that the $column argument can be a Closure, which results in a nested where clause: https://github.com/laravel/framework/blob/2cd2877fb003d395b5b292a363b917e31cf60200/src/Illuminate/Database/Query/Builder.php#L695-L700

Do you think nested where clauses could be implemented?

If you don't have the time or my request would be too complicated, don't worry about it. I can find another way to build my query.

Hi @luisdalmolin

Just following up on #54
I am still unable to get this to work correctly and im unsure where i am going wrong?

$records = Order::joinRelationshipUsingAlias('salesExec', 'salesExec')
            ->select([
                'orders.id',
                'orders.vehicle_reg',
                'orders.created_at',
                'salesExec.first_name'
            ])
            ->toSql();

Using ->get() i get the following error:

Column not found: 1054 Unknown column 'salesExec.first_name' in 'field list'

When using ->toSql() i can see that its using a random string as the alias instead of salesExec?

select orders.id, orders.vehicle_reg, orders.created_at, salesExec.first_name from orders inner join users as be48e2c2f8d67d4ad035546f40ad055e on be48e2c2f8d67d4ad035546f40ad055e.id = orders.sales_exec_user_id

I am using the latest version: 2.5.0

Laravel dont merging joins, when using withCount method on relation.
I'm have hasMany relation, where i'm need joins, but i'm cant use it with withCount
Because this method, called from Builder::withCount->withAggregate
\Illuminate\Database\Eloquent\Concerns\QueriesRelationships::mergeConstraintsFrom

I use an sql union statement to chain multiple queries together.
It all works great unless you join the same relation twice.

$query1 = User::joinRelationship('posts');
$query2 = User::joinRelationship('posts.comments');
// errors: unknown column posts.id
$query1->union($query2)->get();

The resulting query is something like this:

select * from users
join posts on posts.user_id = user.id
union
select * from users
-- join on posts is missing here
join comments on comments.post_id = posts.id

I modified the method \Kirschbaum\PowerJoins\PowerJoins::clearPowerJoinCaches to also clear the joinRelationshipCache and it now works without an error.

    public function clearPowerJoinCaches()
    {
        $this->joinRelationshipCache = []; // <- this is the new line
        PowerJoins::$powerJoinAliasesCache = [];

        return $this;
    }

Hello guys!
I'm facing with this issue:

class User extends Model
{
	public function country()
	{
		return $this->belongsTo(Country::class);
	}

	public function organization()
	{
		return $this->belongsTo(Organization::class);
	}
}

class Organization extends Model
{
	public function country()
	{
		return $this->belongsTo(Country::class);
	}
}

class Country extends Model
{
	//
}

User::leftJoinRelationship('country')->leftJoinRelationShip('organization.country')->count() // error: table name "countries" specified more than once

Probably, need to extend already created join with «or» instead of creating new one.
Seems to be related to #37

Since Eloquent can be used as a stand alone ORM system how do you use this package when you aren't using Laravel?

I have a test project with user/post models

User.php

class User {
    use PowerJoins;

    public function posts() {
        return $this->hasMany(Post::class);
    }
}

Post.php is almost empty

Query

Route::get('/', function () {
    return User::joinRelationship('posts')->find(1);
});

And, i see

{
  "id": 1,
  "name": "Mrs. Elvera Considine MD",
  "email": "[email protected]",
  "email_verified_at": "2021-02-24T10:35:31.000000Z",
  "created_at": "2021-02-24T10:35:31.000000Z",
  "updated_at": "2021-02-24T10:35:31.000000Z"
}

How to return posts? getPostsAttribute? $visible = [] ?

$user = User::joinRelationship('posts')->find(1);
return $user->posts;

Gives me a new db query SELECT * FROM "posts" WHERE "posts"."user_id" = '1' and "posts"."user_id" IS not NULL

How to use that ?

The following looks for the latest relationship name from the root model of the query.

The following query from the tests works because there is a comments relationship defined on the User model.

If you comment out the comments relationship, you will get the following error:

Call to undefined method KirschbaumDevelopment\EloquentJoins\Tests\Models\User::comments()

Or if you try this query, it will fail:
User::orderByUsingJoins('posts.category.title', 'desc')->get();

I have Admin model with a relation called inviter which indicates the admin that invited this one to the system (self-relationship).

so the inviter relation is:

function inviter() { return $this->belongsTo(self::class,'invited_by'); }

calling : joinRelationship('inviter')->find(1) result on

Syntax error or access violation: 1066 Not unique table/alias: 'admins' (SQL: select `admins`.* from `admins` inner join `admins` on `admins`.`invited_by` = `admins`.`id` where `admins`.`id` = 1 limit 1)

As said in the docs, when joining any models which use the SoftDeletes trait, the "deleted_at" is null condition added to any joins.
But is there a way to join without this condition? Like withTrashed() model method?

I cannot get this to work at all.

my relationship is called salesExec.
public function salesExec() { return $this->hasOne(User::class, 'id', 'sales_exec_user_id'); }

$records = Order::leftJoinRelationship('salesExec')->select('users.first_name')->first();
Works but no good when i need to join the users table again.

$records = Order::leftJoinRelationshipUsingAlias('salesExec')->select('salesExec.first_name')->first();
Does not work: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'salesExec.first_name'

$records = Order::leftJoinRelationshipUsingAlias('salesExec')->select('sales_exec.first_name')->first();
Does not work: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_exec.first_name'

$records = Order::leftJoinRelationshipUsingAlias('salesExec')->select('users.first_name')->first();
Does not work: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.first_name'

$records = Order::leftJoinRelationshipUsingAlias('salesExec')->toSql();

Seems to join as a random string, so have no way to select specifc columns

select orders.* from orders left join users as b69c457af5e262af3fe7823187d6beb7 on b69c457af5e262af3fe7823187d6beb7.id = orders.sales_exec_user_id

$records = Order::leftJoinRelationship('salesExec', [
            'salesExec' => function ($join) {
                $join->as('sales_exec');
            },
        ])->select('sales_exec.first_name')->first();

Does not work: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_exec.first_name'

$records = Order::leftJoinRelationship('salesExec', [
            'users' => function ($join) {
                $join->as('sales_exec');
            },
        ])->select('sales_exec.first_name')->first();

Does not work: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'sales_exec.first_name'

Not quite sure what im doing wrong here?

On a separate note, it should be great to join using as alias like this:

$records = Order::leftJoinRelationshipUsingAlias('salesExec', 'sales_exec')

Instead of

$records = Order::leftJoinRelationship('salesExec', [
            'users' => function ($join) {
                $join->as('sales_exec');
            },
        ])->select('sales_exec.first_name')->first();

Item.php

class Item extends Model
{
   use \Kirschbaum\PowerJoins\PowerJoins;

    public function order(): BelongsTo
    {
        return $this->belongsTo(Order::class);
    }
}

Order.php

class Order extends Model
{
  use \Kirschbaum\PowerJoins\PowerJoins;

  public function scopePending($query)
  {
      return $query->where('status', 1); //pending status
  }
  
  public function scopeConfirmed($query)
  {
      return $query->where('status', 2); //confirmed status
  }

  public function scopeCancelled($query)
  {
      return $query->where('status', 3); //cancelled status
  }

  public function scopeNotExpired($query)
  {
      return $query->where('expires_at', '>', now());
  }
}

Fetching all items doesn't having order (pending and expired)
This is not working

Item::powerJoinWhereHas('order', function ($query) {
                $query->where(function ($qroup) {
                    $qroup->where('status', '!=', 1)
                        ->orWhere(function ($q) {
                            $q->pending()->notExpired()
                        });
                });
            })

Throws error

InvalidArgumentException with message 'Method pending does not exist in PowerJoinClause class'

Following works

Item::powerJoinWhereHas('order', function ($query) {
                $query->where(function ($qroup) {
                    $qroup->where('status', '!=', 1)
                        ->orWhere(function ($q) {
                            $scoped=Order::make()->scopePending($q);
                            Order::make()->scopeNotExpired($scoped);
                        });
                });
            })

Hi, I am using 'powerJoinWhereHas' with a relation of 'hasOneThrough' or 'hasManyThrough', but it doesn't call the callback function, however, for example with a relation of 'hasMany', call the callback function.

Hi, do you have plans to support multiple keys for the packages such as https://github.com/topclaudy/compoships

Hi,

PowerJoins does not work with belongToMany when we use custom related key (6th parameter), for example :
return $this->belongsToMany("\App\Models\Material", "CUSTOMER_MATERIAL", "CUSTNO", "CARNO", "CUSTNO", "CARNO");

Generated query is :
select * from [CUSTOMER] left join [CUSTOMER_MATERIAL] on [CUSTOMER_MATERIAL].[CUSTNO] = [CUSTOMER].[CUSTNO] left join [MATERIAL] on [MATERIAL].[IDMaterial] = [CUSTOMER_MATERIAL].[CARNO]
Should be :
select * from [CUSTOMER] left join [CUSTOMER_MATERIAL] on [CUSTOMER_MATERIAL].[CUSTNO] = [CUSTOMER].[CUSTNO] left join [MATERIAL] on [MATERIAL].[CARNO] = [CUSTOMER_MATERIAL].[CARNO]

I think line 108 in RelationshipsExtraMethods.php should be:
"{$this->getModel()->getTable()}.{$this->getRelatedKeyName()}",

In my code, in the relation I have added a function inside the where, looking like this:

return $this->hasOne($translateModel, $this->getForeignKey(), $this->getKeyName())->where(function ($q){ ... });

And it gives me the following error:
Undefined index: column

I have looked a bit and the problem comes from the file "eloquent-power-joins/src/Mixins/RelationshipsExtraMethods.php" in the method "shouldNotApplyExtraCondition()",

Have a:
return $condition['column'] === $key;

But it has inside of "$condition":
array:3 [▼ "type" => "Nested" "query" => Illuminate\Database\Query\Builder {#1737 ▶} "boolean" => "and" ]

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.

DB config:
DB_CONNECTION=sqlite
DB_HOST=127.0.0.1
DB_PORT=3306

Step to reproduce:

1. Call relationship(hasMany) query
2. Then, Call powerJoinWhereHas
   Code Sample:

$this->members()
          ->powerJoinWhereHas(
              'socialOnlineStatus',
              fn ($query) => $query->online()
          )->excludeSelf()->exists();

Trace:

{\n
    "message": "Call to undefined method Illuminate\\Database\\Eloquent\\Builder::getSelect()",\n
    "exception": "BadMethodCallException",\n
    "file": "/Users/raymond/Desktop/iebuy-api/vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php",\n
    "line": 50,\n
    "trace": [\n
        {\n
            "file": "/Users/raymond/Desktop/iebuy-api/vendor/laravel/framework/src/Illuminate/Support/Traits/ForwardsCalls.php",\n
            "line": 36,\n
            "function": "throwBadMethodCallException",\n
            "class": "Illuminate\\Database\\Eloquent\\Builder",\n
            "type": "::"\n
        },\n
        {\n
            "file": "/Users/raymond/Desktop/iebuy-api/vendor/laravel/framework/src/Illuminate/Database/Eloquent/Builder.php",\n
            "line": 1511,\n
            "function": "forwardCallTo",\n
            "class": "Illuminate\\Database\\Eloquent\\Builder",\n
            "type": "->"\n
        },\n
        {\n
            "file": "/Users/raymond/Desktop/iebuy-api/vendor/kirschbaum-development/eloquent-power-joins/src/PowerJoins.php",\n
            "line": 270,\n
            "function": "__call",\n
            "class": "Illuminate\\Database\\Eloquent\\Builder",\n
            "type": "->"\n
        },

It seems that this package does not support PHP 8.0? Does it only support ^7.1 or ^8.1?

Any reason why 8.0 is skipped?

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

We have the following code:

$user = auth()->user();

$query1 = $user->comments()
    ->orderByUsingJoins('post.created_at', 'desc')
    ->get();

$query2 = $user->comments()
    ->orderByUsingJoins('post.created_at', 'desc')
    ->get();

Query 1 runs successfully. However, query 2 fails:

Unknown column 'posts.created_at' in 'order clause'

This happens because the join statement is missing from the 2nd query, while the order by part is still present.

Calling $user->refresh() between the two queries, works around this issue.

As powerJoinWhereHas expects instance of Illuminate\Database\Eloquent\Builder, we are unable to use it in below query

return Category::with(['getBrands' => function ($brand) {
            $brand->distinct('brands.id')
            ->powerJoinWhereHas('products', function ($join) {
                return $join->where(['active' => true, 'website' => true])->powerJoinWhereHas('channel', function ($channel) {
                    return $channel->where(['active' => true, 'website' => true]);
                });
            })
            ->withCount('products');
        }])->withCount('products')->get();

this throws
Argument 1 passed to App\\Models\\Product\\Product::scopePowerJoinWhereHas() must be an instance of Illuminate\\Database\\Eloquent\\Builder, instance of Kirschbaum\\PowerJoins\\PowerJoinClause given

along with this issue, a not unique table/alias issue also occured when i tried again after removing nested powerJoinWherHas like :

return Category::with(['getBrands' => function ($brand) {
            $brand->distinct('brands.id')
            ->powerJoinWhereHas('products', function ($join) {
                return $join->where(['active' => true, 'website' => true]);
            })
            ->withCount('products');
        }])->withCount('products')->get();

both of the above queries works when used with default whereHas.

Thanks.

When I try to use "powerJoinHas" I get the following error:

Country::powerJoinHas('translation')->get()

SQLSTATE[42000]: Syntax error or access violation: 1055 'countries.iso2' isn't in GROUP BY (SQL: select countries.*, count(country_translations.id) as country_translations_count from countries left join country_translations on country_translations.country_id = countries.id and country_translations.locale = en group by countries.id having country_translations_count >= 1 limit 1)

First of all: thanks for this cool library! 😊

Right now, I'm scratching my head because when I do the join all the data is retrieved correctly but all attributes are stored in the attributes of the model (even foreign attributes). Is it somehow possible to store the retrieved data automatically in the corresponding relations?

So after using $post->joinRelationship('user')->get() I want to be able to use $post->user. Is that even possible?
Thanks!

I'm getting this error:

Call to undefined method Illuminate\Database\Eloquent\Builder::getSelect()

My code call:
EmpresaGrupo::joinRelationship('pessoa');

I believe is the same as #43

I'm using Lumen 8

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!

I have 2 models that have similar column names i.e. name, joins throw this error:

SQLSTATE[23000]: Integrity constraint violation: 1052 Column 'name' in on clause is ambiguous

select `videos`.* from `videos` inner join `tracked_jobs` on `tracked_jobs`.`trackable_id` = `videos`.`id` and `trackable_type` = App\\Models\\Video and `name` = ProcessVideo and `status` = finished

Model Video:

    public function last_successful_job()
    {
        return $this->morphOne(TrackedJob::class, 'trackable')->finished();
    }

Model TrackedJob:

    public function scopeFinished($query)
    {
        return $query->where('name', 'ProcessVideo')
            ->where('status', 'finished')
            ->orderBy(TrackedJob::CREATED_AT, 'desc');
    }

However if I try to use joinRelationshipUsingAlias like so, I get a different error.
I also noticed that the SQL generated is different from above, as it is ignoring my scope.

$video->joinRelationshipUsingAlias('last_successful_job', 'testme')->get();

SQLSTATE[HY093]: Invalid parameter number 

select `videos`.* from `videos` inner join `tracked_jobs` as `testme` on `testme`.`trackable_id` = `videos`.`id`

Any way of getting around this?

need update composer.json

The joins do not consider if the relationship they are pointing to belongs to another database (within the same server).

Also if they have different prefixes, just add the prefix of the model where the join is made.

I was testing my app with PHP 8.1 and got these deprecation notices.

Accessing static trait property Kirschbaum\PowerJoins\PowerJoins::$joinMethodsMap is deprecated, it should only be accessed on a class using the trait in /app/vendor/kirschbaum-development/eloquent-power-joins/src/PowerJoins.php on line 42

Accessing static trait property Kirschbaum\PowerJoins\PowerJoins::$powerJoinAliasesCache is deprecated, it should only be accessed on a class using the trait in /app/vendor/kirschbaum-development/eloquent-power-joins/src/PowerJoins.php on line 386

Accessing static trait property Kirschbaum\PowerJoins\PowerJoins::$powerJoinAliasesCache is deprecated, it should only be accessed on a class using the trait in /app/vendor/kirschbaum-development/eloquent-power-joins/src/Mixins/RelationshipsExtraMethods.php on line 305

First of all: Thank you for the awesome package and happy easter 🐰 !

In our current project, we are using the relationshipJoins with various many-to-many relations, but do not really care about the data on the relationship table and instead only need to join the pivot table.
So I wanted to suggest adding a function, which does the same as relationshipJoin, which already does an inner join on the pivot, but does not also join the table of the related model.

Thanks

I have a code:

Models\CatalogProductEntity::leftJoinRelationship('catalog_product_entity_varchars.eav_attribute')->limit(1)->get();

and select is

select `catalog_product_entity`.* from `catalog_product_entity` left join `catalog_product_entity_varchar` on `catalog_product_entity_varchar`.`row_id` = `catalog_product_entity`.`row_id` left join `eav_attribute` on `catalog_product_entity_varchar`.`attribute_id` = `eav_attribute`.`attribute_id` limit 1"

Select is :

or even simple:

\Models\CatalogProductEntity::leftJoinRelationship('catalog_product_entity_varchars')->limit(1)->get();

Select Is:

"select `catalog_product_entity`.* from `catalog_product_entity` left join `catalog_product_entity_varchar` on `catalog_product_entity_varchar`.`row_id` = `catalog_product_entity`.`row_id` limit 1"

Should be

"select `catalog_product_entity`.*, `catalog_product_entity_varchar`.*   from `catalog_product_entity` left join `catalog_product_entity_varchar` on `catalog_product_entity_varchar`.`row_id` = `catalog_product_entity`.`row_id` limit 1"

You have this described in the doc however you don't have doc how to add fields from the join. It doesn't have any sence join and not select fields:

https://github.com/kirschbaum-development/eloquent-power-joins#select--from-table

I have written some solution could you consider adding this to the core. and select with TableName.*

$query->leftJoinRelationship('eav_attribute')->addSelect(['eav_attribute.*']);

Thank you for the awesome package and really makes working with joins easier.

In our current project, we are using the relationshipJoins to filter on datatables, however we don't always have the filters active we rely alot on the ->when(bool, closure(Builder $query){//}) functionality however since the closure is an instance of the query builder we lose the functionality of the package

To this we thought about two potential solutions

1. adding a powerJointWhen function suite
2. Extending the functionality for the underlying querybuilder somhow (might be too involved)

However perhaps I'm missing something and this is already feasible with the current package.

This code produces an SQL Exception, because the relationship is joined twice in the query:

Model::query()
  ->joinRelationship('relation')
  ->joinRelationship('relation')
  ->dd();

outputs:

select `model`.* from `model` inner join `relation` on `relation`.`id` = `model`.`id` inner join `relation` on `relation`.`id` = `model`.`id`

I've tried to get to the bottom of the bug and for me it seems like the PowerJoins::$joinRelationshipCache cache gets emptied for some reason.

But this only happens in this case. When joining two nested relationships (as in the test case) the sql query is correct, that's why this bug wasn't covered by the tests.

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?

Hello,

I have a package that accepts model classes to create tables for it, but when it comes to sorting and filtering, it needs joins instead of relationship instances. But since the classes are dynamic and given from outside world, I don't have the chance to extend the model classes with traits. I can do it with using eval, but it's not recommended, and will not work in most servers, it's not an option. So I need to use PowerJoins trait methods without the trait...

Is it somehow possible? For example

$model = PowerJoins::joinRelationship($model, ["related1", "related2"]);

Thanks.

for example

SELECT
	`drivers`.*,
	(
		SELECT
			count(*)
		FROM
			`policies`
		WHERE
			`drivers`.`id` = `policies`.`driver_id`
			AND `to_be_collected` =) AS `policies_count`, (
			SELECT
				count(*)
			FROM
				`policies`
			WHERE
				`drivers`.`id` = `policies`.`driver_id`
				AND `to_be_collected` = 1) AS `receipts_count`, (sum(policies.items_total) + sum(policies.freight - policies.amount)) AS `debt`
		FROM
			`drivers`
		LEFT JOIN `policies` ON `policies`.`driver_id` = `drivers`.`id`
			AND `to_be_collected` =
			AND `is_returned` =
			AND `is_delivered` =
			AND `is_closed` =
		GROUP BY
			`drivers`.`id`
		ORDER BY
			`debt` DESC

note all the to_be_collected / is_returned /etc.. = should have 0 for a value which correspond to the original scope

public function scopeIsOpen($q)
{
    return $q->where('is_returned', false)
              ->where('is_delivered', false)
              ->where('is_closed', false)
              ->where('to_be_collected', false);
}

also here is the query

$query->leftJoinRelationship('opened_policies')
      ->selectRaw('(sum(policies.items_total) + sum(policies.freight - policies.amount)) AS `debt`')
      ->groupBy('drivers.id')
      ->orderBy('debt', $sort_dir);

am not sure if am missing something or if its a bug but when adding 0 to all the fields and run it directly, the data is correctly ordered,
if u need any more info plz do tell.

When using whereHasUsingJoins inside a model scope, it fails as Laravel tries to use dynamic wheres instead of using the proper method. Need to figure out a good approach for this, but may need to ship a new version where we include a trait in the models instead of adding macros due to how Laravel works.