How do I update value of subsequent parent records

posted 11 years ago

I have two tables: members and contributions. The members table has a recursive relationship, that is: a member can be a "parent" to other members (children) or no member connected to him/her. I thus have "id" as the primary key and "parent_id" as the foreign key. All these members make a contribution of let's say $100 each.

Now let's take an example where we have 3 members: member1, member2 and member3 whereby member1 is a parent to member2 (child) and a "grandparent" to member3 (grandchild). Example 1: When member2 makes a contribution (recorded in the contributions table), $5 is deducted from him and added to member1's contribution. Example2: when member3 (the "grandchild") makes a contribution , $10 should be deducted from the $100 and of the $10 deducted, $5 is to be added to member2's contribution and the other $5 added to member1 (the "grandparent").

I managed to implement example1 in the MemberController.php in this way:

public function putContribution($id)
{
...
if(!is_null($user->parent->id) && $user->contribution()->save($data))
{
	$parentUser = DB::table('contributions')
		->where('member_id', '=', $member->parent->id)
		->increment('amount', 5);

	$currentUser = DB::table('contributions')
		->where('member_id', '=', $id)
		->decrement('amount', 5);

	return Redirect::action('MemberController@getShow', $id)
		->with('success', 'Contribution was successfully made.');
};
...
}

With the above code, I was only able to increment the value of the nearest parent but not the subsequent parents as is the case with Example 2.

I have failed to even figure out how I can implement Example2 or even a situation (Example3) where there is member4 whose parent is member3. Thus if member4 makes a contribution, $15 to be deducted of which $5 is added to member3, member2 and member1 given that these member are on the same chain.

Last updated 3 years ago.

lookitsatravis

replied 11 years ago

You could maybe use something like this. Keep in mind I haven't tested it, but it should be close. Essentially, you need to find all parents of a record, then update each parent by the specified amount, and then decrement the child by the amount * number of parents. At least, that's how I understood what you're trying to do.

public function updateAmount($amount)
{
	$allParents = array();
	$currentParent = $this->parent();

	while($currentParent)
	{
		$newParent = $currentParent->parent();
		if($newParent)
		{
			array_push($allParents, $newParent);
			$currentParent = $newParent;
		}
		else
		{
			$currentParent = null;
		}
	}

	if(count($allParents) > 0)
	{
		$amountPerParent = 5;
		foreach ($parent in $allParents)
		{
			$parent->increment('amount', $amountPerParent);
		}

		$this->increment('amount', $amount - count($allParents)*$amountPerParent);
	}
	else
	{
		$this->increment('amount', $amount);
	}
}

Edit: You would put this in your Contribution model. Then from your controller, you would simply call something like:

public function controllerAction($id)
{
	$contribution = App::make('Contribution')->findOrFail($id);

	$contribution->updateAmount(Input::get('amount'));

	return Response::make('success', 200);
}

Last updated 3 years ago.

jmatembu

replied 11 years ago

Thank you lookitsatravis, I am going to try it out and see. I appreciate the effort

Last updated 3 years ago.

jmatembu

replied 11 years ago

lookitsatravis said:

public function updateAmount($amount)
{
  $allParents = array();
  $currentParent = $this->parent();

  while($currentParent)
  {
  	$newParent = $currentParent->parent();
  	if($newParent)
  	{
  		array_push($allParents, $newParent);
  		$currentParent = $newParent;
  	}
  	else
  	{
  		$currentParent = null;
  	}
  }

  if(count($allParents) > 0)
  {
  	$amountPerParent = 5;
  	foreach ($parent in $allParents)
  	{
  		$parent->increment('amount', $amountPerParent);
  	}

  	$this->increment('amount', $amount - count($allParents)*$amountPerParent);
  }
  else
  {
  	$this->increment('amount', $amount);
  }
}

Edit: You would put this in your Contribution model. Then from your controller, you would simply call something like:

public function controllerAction($id)
{
  $contribution = App::make('Contribution')->findOrFail($id);

  $contribution->updateAmount(Input::get('amount'));

  return Response::make('success', 200);
}

I implemented your suggested solution and from the look of the code, you understood my issue. I however received an error

Call to undefined method Illuminate\Database\Query\Builder::parent()

The above error pointed to the line below:

$newParent = $currentParent->parent();

I also think that the following lines should have decrement instead of increment:


        $this->increment('amount', $amount - count($allParents)*$amountPerParent);
    }
    else
    {
        $this->increment('amount', $amount);
    }

Last updated 3 years ago.

lookitsatravis

replied 11 years ago

I'm sure you've figured this out by now, but you probably need to change the lines to look like this:

line 4:

$currentParent = $this->parent()->first();

and then in the while loop

$newParent = $currentParent->parent()->first();

Basically, the example I wrote wasn't actually getting the record from the DB. Only building the query. ->first() solves that. As for the increments needing to be decrement...it shouldn't be. Take this example:

Say we have $20 and 3 parents to add $5 to. We don't want to do increment(20), then figure out the parent thing, and then do decrement(15). We figure out the parent thing first, then we just increment the difference. So, we wind up doing increment(20 - (3*5)) which gives us an increment of 5. In only one DB update. Make sense? Plus, if it wound up being a negative number, it would still work. 100 + -5 is 95 even if increment was called.

Last updated 3 years ago.

Moderators