I have the following tables:

Tables

user

• id
• email

organisations

• id
• name

organisation_members

• id
• organisation_id (FK to organisations)
• user_id (FK to user)
• role

Relationships

• User to organisations members is a one to many relationship.
• Organisation members to organisations is a many to one relationship
• So User to Organisation members is many to many relationship, with organisation_members as the intermediary table.

Eloquent Models

The models looks as follows:

Class User {
   public function organisationMembers(): HasMany
   {
        return $this->hasMany(OrganisationMember::class);
   }

   public function organisations(): BelongsToMany
   {
        return $this->belongsToMany(Organisation::class, 'organisation_members')
            ->withPivot('role');;
   }
}

class Organisation {
    public function organisationMembers(): HasMany
    {
        return $this->hasMany(OrganisationMember::class);
    }

    public function users(): BelongsToMany
    {
        return $this->belongsToMany(Organisation::class, 'organisation_members')
            ->using(OrganisationMember::class);
    }
}

class OrganisationMember {
    public function organisation(): BelongsTo
    {
        return $this->belongsTo(Organisation::class)->withTrashed();
    }

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

The issue

For the following code:

$organisations = User::find($id)->organisations

It works as expected, when the user has organisations.

However, when I do this method on a user with no organisations, instead of getting an empty collection I get the following:

Illuminate\Database\QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'organisation_members.role' in 'field list'
(SQL: select `organisations`.*,
`organisation_members`.`user_id` as `pivot_user_id`,
`organisation_members`.`organisation_id` as `pivot_organisation_id`,
`organisation_members`.`role` as `pivot_role` from `organisations` inner join
`organisation_members` on `organisations`.`id` = `organisation_members`.`organisation_id`
where `organisation_members`.`user_id` = 109 and `organisations`.`deleted_at` is null)'

However, if I modify, the user model, and remove the pivot part:

Class User {
   // ...

   public function organisations(): BelongsToMany
   {
        return $this->belongsToMany(Organisation::class, 'organisation_members');
   }
}

It returns an empty collection as expected.

How can I solve this issue?

CodePudding user response：

Your code seems to work here, even with the pivot in the User class

>>> return User::find(1)->organisations;
=> Illuminate\Database\Eloquent\Collection {#3464
     all: [
       App\Models\Organization {#3457
         id: 1,
         name: "hello",
         created_at: "2021-10-05 22:50:19",
         updated_at: "2021-10-05 22:50:19",
         pivot: Illuminate\Database\Eloquent\Relations\Pivot {#4251
           user_id: 1,
           organization_id: 1,
           role: "f",
         },
       },
     ],
   }

>>> return User::find(3)->organisations;
=> Illuminate\Database\Eloquent\Collection {#4396
     all: [],
   }
>>>

and Its' the same query executed

select `organizations`.*, `organisation_members`.`user_id` as `pivot_user_id`, `organisation_members`.`organization_id` as `pivot_organization_id`, `organisation_members`.`role` as `pivot_role` from `organizations` inner join `organisation_members` on `organizations`.`id` = `organisation_members`.`organization_id` where `organisation_members`.`user_id` = 3

However there's a mistake in the relation in the Organisation Class :

class Organisation {
    public function organisationMembers(): HasMany
    {
        return $this->hasMany(OrganisationMember::class);
    }

    public function users(): BelongsToMany
    {
        return $this->belongsToMany(Organisation::class, 'organisation_members')
            ->using(OrganisationMember::class);
    }
}

You need to change the belongsToMany first paramter with the User class to be like this

class Organisation {

    public function organisationMembers(): HasMany
    {
        return $this->hasMany(OrganisationMember::class);
    }

    public function users(): BelongsToMany
    {
        return $this->belongsToMany(User::class, 'organisation_members')
            ->using(OrganisationMember::class);
    }
}

Check it in the docs here : Laravel Eloquent