BelongsToMany with relatedKey

[simong88]

trafficstars

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()}",

[luisdalmolin]

Are you sure about this? Can you describe your current table structure and also give an example on how you are calling the join relationship method?

[beshoo]

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()}",

It looks like you are trying to use the belongsToMany method in Laravel's Eloquent ORM with a custom related key, but it's not producing the expected query.

The belongsToMany method allows you to define a many-to-many relationship between two models, and it takes several parameters to define the relationship. The sixth parameter is the foreign key name in the intermediate table for the model on which the belongsToMany method is called. In your example, you are using "CUSTNO" as the foreign key name.

Based on the generated query you provided, it looks like the belongsToMany method is using the correct foreign key name in the intermediate table. However, the issue seems to be with the join condition for the MATERIAL table. The generated query is using [MATERIAL].[IDMaterial] = [CUSTOMER_MATERIAL].[CARNO], but you expected it to be [MATERIAL].[CARNO] = [CUSTOMER_MATERIAL].[CARNO].

To fix this issue, you can specify the custom related key for the MATERIAL model by passing it as the fifth parameter to the belongsToMany method. This will cause the belongsToMany method to use the correct key in the join condition for the MATERIAL table.

Here's an example of how you can do this:

return $this->belongsToMany("\App\Models\Material", "CUSTOMER_MATERIAL", "CUSTNO", "CARNO", "CUSTNO", "CARNO")
            ->using("\App\Models\CustomerMaterial")
            ->withPivot("CARNO");

I hope this helps! Let me know if you have any question

[luisdalmolin]

Thanks @beshoo - Closing this issue for now. If it's still an issue, let me know