Column 'tenant_id' is ambiguous when using Joins

[matt-tonks]

Hi there,

Firstly thank you for your work on this project, it's made what I plan to do so much easier.

However, I'm having issues using this package when using join statements between two models that inherit scoping of the current tenant.

I believe this is due to the where clause that looks for the tenant_id being ambiguous since both models have the tenant_id in them and a specific tenant_id is not specified i.e.table_name.tenant_id.

Would you consider this an issue? or do you have any recommendations for resolving this?

[Naoray]

Hi @matt-tonks,

I don't quite understand your question. If you apply the tenant scope to one model and retrieve related models via relationships, they should also be within the tenant scope.

$someModel = Tenant::findByDomain('tenantA')->someModels->first();

// still in tenant scope
$otherModels = $someModel->otherModels;

[matt-tonks]

Hi @Naoray,

Sorry, it might help if I clarify a few things. I followed the instructions for installing your package i.e setting up middleware etc. and I have two models, Model A and Model B. Both of these use the trait BelongsToTenant.

On one of these models I have a method that joins the two corresponding db tables

$query->join('model B', 'model B.id' , '=', 'model A.id')->get();

This query no longer works when I install your package because your package adds on the where statement ->where('tenant_id', 'whateverTheTenantIdIs');

But because both tables in the database have tenant_id I get an error because tenant_id is ambiguous

I hope this makes sense, I am probably missing something obvious I just can't see it

Thanks for your help

[Naoray]

Why do you use a join clause and not just add a belongsTo relationship method to model A and hasOne to model B?

// model b
public function modelB()
{
    return $this->hasOne(ModelA::class);
}

// model a
public function modelA()
{
    return $this->belongsTo(ModelB::class, 'id');
}

I am a bit confused why you join both models on their id, which is supposed to be a unique identifier...?!

[nicoqh]

@Naoray, this applies to any JOIN query because the tenant_id column is ambiguous (it can refer to either table used in the query).

You can reproduce the problem by JOINing two tables that both use the tenant scope.

Discouraging JOINs doesn't solve the problem: Eloquent's belongsToMany relationship method uses a JOIN and cannot be used with this package.

[bradenkeith]

Careful with those large statements. I use belongsToMany all over my applications utilizing this package.

I'll be happy to help troubleshoot this issue. I'll need sometime before I can review the request and will post back when I can.

[bradenkeith]

Resolved in latest release. Let me know if any other issues.

[nicoqh]

Careful with those large statements. I use belongsToMany all over my applications utilizing this package.

I'm sorry about the hasty comment. You're right -- I didn't take this piece of code into consideration:

https://github.com/romegadigital/Multitenancy/blob/91b0e9e7122612f8a80812c4ba7ad2c88e3b97ef/src/Multitenancy.php#L76-L78

... which actually does quality the column name.

So this problem applies to manual JOINs which doesn't automatically qualify the column.

Update: The above embedded code is actually from the new release :) I'm not quite sure how belongsToMany worked before this fix, though. Thanks for the fix!

[bradenkeith]

Excellent... let me know if you experience any other issues!