CODEWITHSUNDEEP
phpmysqllaravellaravel-5eloquent
Anshad Vattapoyil
Anshad Vattapoyil

Reputation: 23483

Handling mapping table in laravel eloquent

In my application, there will be multiple investors tagged for single purchase entry. So on loading a purchase entry, I should get all the investors associated.

In my controller,

return response()->json(GoldPurchase::with('investors')->get());

Mapping table schema,

Schema::create('gold_purchase_investor', function (Blueprint $table) {
   $table->increments('id');
   $table->integer('investor_id')->unsigned();
   $table->integer('purchase_id')->unsigned();
   $table->timestamps();

   $table->foreign('investor_id')
        ->references('id')
        ->on('investors')
        ->onDelete('cascade');

   $table->foreign('purchase_id')
        ->references('id')
        ->on('gold_purchases')
        ->onDelete('cascade');
});

Purchase model,

class GoldPurchase extends Model
{
    public function investors() {
        return $this->hasMany('App\GoldPurchaseInvestor');
    }
}

Investor model,

class Investor extends Model
{
    protected $fillable = ['name', 'address', 'mobile', 'email'];

    public function purchases() {
        return $this->hasMany('App\GoldPurchase');
    }
}

PurchaseInvestor model,

class GoldPurchaseInvestor extends Model
{
    protected $table = 'gold_purchase_investor';

    public function purchase() {
        return $this->belongsTo('App\GoldPurchase');
    }

    public function investor() {
        return $this->belongsTo('App\Investor');
    }
}

With this, I am getting error,

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

Upvotes: 0

Views: 4754

Answers (3)

nice_dev
nice_dev

Reputation: 17815

Use belongsToMany for many-to-many relationships.

GoldPurchase.php

class GoldPurchase extends Model{
    public function investors() {
        return $this->belongsToMany('App\Investor','gold_purchase_investor','purchase_id','investor_id');
    }
}

Investor.php

class Investor extends Model{
    protected $fillable = ['name', 'address', 'mobile', 'email'];

    public function purchases() {
        return $this->belongsToMany('App\GoldPurchase','gold_purchase_investor','investor_id','purchase_id');
    }
}

You don't need a third model for pivot table at all. GoldPurchaseInvestor is not at all needed.

Upvotes: 0

Jonas Staudenmeir
Jonas Staudenmeir

Reputation: 25926

You have to specify the custom foreign key:

public function investors() {
    return $this->hasMany('App\GoldPurchaseInvestor', 'purchase_id');
}

But this is actually a case for a BelongsToMany relationship:

public function investors() {
    return $this->belongsToMany('App\Investor', 'gold_purchase_investor', 'purchase_id');
}

Upvotes: 3

KusokBanana
KusokBanana

Reputation: 345

From the Eloquent Relationship:

Remember, Eloquent will automatically determine the proper foreign key column on the Comment model. By convention, Eloquent will take the "snake case" name of the owning model and suffix it with _id. So, for this example, Eloquent will assume the foreign key on the Comment model is post_id.

$this->hasMany('App\Comment', 'foreign_key', 'local_key');

So try to write your foreign key and local key in the relationships

In your case I think it would be something like that:

class GoldPurchase extends Model`
{
    public function investors() {
        return $this->hasMany('App\GoldPurchaseInvestor', 'investor_id', 'id');
    }
}

Upvotes: 0

Related Questions