search

hoyvoy / laravel-cross-database-subqueries

Eloquent cross database compatibility in subqueries
MIT License
102 stars 43 forks source link

WhereHas with a PivotTable #19

Closed gfernandez-me closed 4 years ago

gfernandez-me commented 4 years ago

Description:

When you do a whereHas on a relationship it uses a pivot table. The pivot table has no prefix

Steps To Reproduce:

1 - Model Users - databaseA 2 - Model Roles - databaseB 3 - Model UserRoles - databaseB

user.php

public function roles()
{
        return $this->setConnection('tenant')->belongsToMany(Role::class, 'user_roles')->using(UserRoles::class)->withTimestamps();
}

"seb5ahng_valor"."roles" = OK select count(*) as aggregate from "users" = OK inner join "user_roles" = WRONG -> should be -> inner join "seb5ahng_valor"."user_roles"

error:

SQLSTATE[42P01]: Undefined table: 7 ERROR: relation "user_roles" does not exist LINE 1: ...select from "seb5ahng_valor"."roles" inner join "user_role... ^ (SQL: select count() as aggregate from "users" where exists (select from "seb5ahng_valor"."roles" inner join "user_roles" on "roles"."id" = "user_roles"."role_id" where "users"."id" = "user_roles"."user_id" and "code" = admin) and "company_id" = 2) {"userId":2,"exception":"[object] (Illuminate\Database\QueryException(code: 42P01): SQLSTATE[42P01]: Undefined table: 7 ERROR: relation \"user_roles\" does not exist LINE 1: ...select from \"seb5ahng_valor\".\"roles\" inner join \"user_role... ^ (SQL: select count() as aggregate from \"users\" where exists (select from \"seb5ahng_valor\".\"roles\" inner join \"user_roles\" on \"roles\".\"id\" = \"user_roles\".\"role_id\" where \"users\".\"id\" = \"user_roles\".\"user_id\" and \"code\" = admin) and \"company_id\" = 2) at /var/www/nexterp/vendor/laravel/framework/src/Illuminate/Database/Connection.php:671)

gfernandez-me commented 4 years ago

Forget it, I removed the library and extended belongsToMany, it was easier. This library only created problems in other parts