Add pivot record and related child records using Laravel

Question

I need help adding a pivot record and related child records in Laravel. I would like to do it using ORM if possible. The concept here is potential donors attend fundraisers and make donations, which will be comprised of one or more designations. My tables are like so (simplified):

donor:
  id
  name

fund_raiser:
  id
  date

donation:
  id
  donor_id
  fund_raiser_id
  monetary_type

designation:
  id
  donation_id
  category
  amount

I can insert the pivot record manually as follows:

// assume both donor and rundraiser records already exist
$donor = Donor::find($donorID);
$fundraiser = FundRaiser::find($fundraiserID);

// create the pivot record
$donation = Donation::create(['donor_id' => $donor->id, 'fundraiser_id' => $fundraiser->id, 'date' => 'somedate']);

// now add a designation
$designation = new Designation(['Category' => 'General', 'Amount' => '100']);
$donation->designations()->save($designation);

Or I can create the pivot record in a more ORM kind of way:

$fundraiser->donors()->save($donor)

But now I don't know how to insert the designation, since I don't know the id of the pivot record that was just added.

check this http://stackoverflow.com/questions/24857522/laravel-custom-pivot-table-relationship-and-eager-loading — Jarek Tkaczyk, Jul 31 '14 at 17:26
@deczo. hmmm. So it looks like I might have to define a custom pivot? I'll have to go do some research on that. The Laravel Docs are pretty sparse. — Russel Dirks, Jul 31 '14 at 21:49
@deczo. No, I don't need eager loading now. Maybe later when I start pulling data out. Right now I'm trying to figure out how to get data in. How do I get the id of the pivot table record that is created with the save method, so I can then add the child records under it? I can insert the pivot record easy enough using standard Query Builder calls, but I was wondering if their is some way to do it using ORM. — Russel Dirks, Jul 31 '14 at 22:47

Jarek Tkaczyk · Accepted Answer · 2014-08-01T06:47:12.317

You need withPivot on the relation definitions to get the pivot id, then you can access pivot model, thus its id, in the context of the relation:

// Donor model
public function fundRaisers()
{
  return $this->belongsToMany('FundRaiser', 'donation')->withPivot('monetary_type', 'id');
}

The same goes for the other part of that relation on the FundRaiser model.

Then you can do this:

// let's grab single donor
$donor = Donor::first();

// then load fund raisers and get one of them
$fundRaiser = $donor->fundRaisers->find($someId);

// now we're in the context of the relation, so we can access pivot model
$donationId = $fundRaiser->pivot->id;

// use it to save designation
$designation = new Designation([ SOME VALUES]);
$designation->donation_id = $donationId;
$designation->save();

Now, in order to access related Designation models on the pivot, you need custom Pivot model (extending Pivot) with hasMany relation:

use Illuminate\Database\Eloquent\Relations\Pivot;

class DonationPivot extends Pivot {

  protected $table = 'donations';

  public function designations()
  {
    return $this->hasMany('Designation');
  }
}

Now you can use it:

$fundRaiser->pivot->designations;

Mind that you won't be able to call

$designation->donation;

with that setup, because Pivot requires dependencies passed upon construction, however you can do this for example:

$donor = Donor::join('donations', 'donors.id', '=', 'donations.donor_id')->where('donations.id', '=', $designation->donation_id')->first();

To get pivot id the easy way you can use lastInsertId on the PDO of current connection:

$fundraiser->donors()->save($donor);
$fundraiser->getConnection()->getPdo()->lastInsertId();

Thanks for all your help so far. I think we're getting close. You have presented a solution where the pivot record already exists, but I am looking for a solution that involves creating both the pivot record and the related child records. I have presented my own answer that involves manually inserting the necessary id values in the pivot record, but I am curious if it can be done using a more ORM style. — Russel Dirks, Aug 01 '14 at 02:38
Without creating a new model (extending Eloquent, not Pivot) for the pivot table (which can be done but is not very convenient in use) you can't do it 'more ORM' way. Check my edited examples - last 2 lines show, how to get that last id. — Jarek Tkaczyk, Aug 01 '14 at 06:48

Russel Dirks · Answer 2 · 2014-08-01T02:44:10.197

I have found that I can insert the pivot record manually as follows:

// assume both donor and rundraiser records already exist
$donor = Donor::find($donorID);
$fundraiser = FundRaiser::find($fundraiserID);

// create the pivot record, plugging in the necessary id values manually
$donation = Donation::create(['donor_id' => $donor->id, 'fundraiser_id' => $fundraiser->id, 'date' => 'somedate']);

// now add a designation
$designation = new Designation(['Category' => 'General', 'Amount' => '100']);
$donation->designations()->save($designation);

There is another way to create the pivot record that I know of, which is more ORM style:

// assume both donor and rundraiser records already exist
$donor = Donor::find($donorID);
$fundraiser = FundRaiser::find($fundraiserID);

// create the pivot record
$fundraiser->donors()->save($donor)

But the problem with this method is I don't get a handle to the pivot record, so I have no way to add related records (designations). Anybody who can show me how to complete the second method will get the checkmark.

Add pivot record and related child records using Laravel

2 Answers2