Counting eloquent relations from pivot table in L4

Question

I have the following tables:

users

    Schema::create('users', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('username', 30);
        $table->string('email')->unique();
        $table->string('password', 60);
        $table->string('remember_token')->nullable();
        $table->timestamps();
    });

organisations

    Schema::create('organisations', function(Blueprint $table)
    {
        $table->increments('id');
        $table->string('name')->unique('name');
        $table->integer('owner_id')->unsigned()->index()->nullable();
        $table->foreign('owner_id')->references('id')->on('users');
        $table->timestamps();
    });

and this is my organisation_user pivot table:

public function up()
{
    Schema::create('organisation_user', function(Blueprint $table)
    {
        $table->increments('id');
        $table->integer('organisation_id')->unsigned()->index();
        $table->foreign('organisation_id')->references('id')->on('organisations')->onDelete('cascade');
        $table->integer('staff_id')->unsigned()->index();
        $table->foreign('staff_id')->references('id')->on('users')->onDelete('cascade');
    });
}

My model's rules are:

• An organisation belongs to one user (owner) - not always, i.e. nullable owner_id
• An organisation may have many users under it (staffs)

Therefore, my Organisation eloquent model looks like this:

class Organisation extends Eloquent {

    /**
     * @return \Illuminate\Database\Eloquent\Relations\HasOne
     */
    public function owner()
    {
        return $this->belongsTo('User', 'owner_id', 'id');
    }

    /**
     * @return \Illuminate\Database\Eloquent\Relations\HasMany
     */
    public function staffs()
    {
        return $this->hasMany('User', 'staff_id', 'id');
    }

}

This is how I load the model in my controller and pass it to the view:

public function index()
{
    return View::make('organisations.index')
        ->with('organisations', Organisation::with('owner', 'staffs')->get());
}

On my view, I display the data like this:

@foreach($organisations as $organisation)
    <div>
        Name : {{  $organisation->name }}
        <br>
        Owner: {{ $organisation->owner->email }}
        <br>
        Staffs: {{ $organisation->staffs->count() }}
    </div>
@endofreach

When the above executes, I get the following error:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'users.staff_id' in 'where clause' (SQL: select * from users where users.staff_id in (1))

Any idea why I might be doing wrong here? How do you link the relationship with eager loading correctly?

Do I need a separate model for the pivot table for this to work?

Answer 1

Looks to me like staffs is actually a many-to-many relationship. That means you need belongsToMany()

public function staffs()
{
    return $this->belongsToMany('User', 'organisation_user', 'organisation_id', 'staff_id');
}

Answer 2

Many to many relationships use the belongsToMany() method, not the hasMany() method.

Update your code:

class User extends Eloquent
{
    public function staffs()
    {
        return $this->belongsToMany('Organisation', 'organisation_user', 'staff_id','organisation_id');
    }
}

Also in the view, try this Staffs: {{ $organisation->staffs()->count() }}

Note the only change is the added () to staff, I can't test this code myself but from what I remember the ->staffs method would return an Eloquent\Collection of all the related models (Users) and with the () would return the hasMany() object you defined in the relation method in the model which has other functionality compared to the Eloquent\Collection

Double check the Eloquent documentation on many to many relationships.