Build a Referral System with Laravel
This episode is for members only

Sign up to access "Build a Referral System with Laravel" right now.

Get started
Already a member? Sign in to continue
Playing
21. Grouping payouts

Episodes

0%
Your progress
  • Total: 2h 39m
  • Played: 0m
  • Remaining: 2h 39m
Join or sign in to track your progress

Transcript

00:00
So before we get to sending out an email and generating that spreadsheet with all the payments that we need to make, what we basically need to make sure we're doing is grouping all the payments together. Now at the moment if we were to use the query that we've built up here to generate out the spreadsheet, which we could do, what we're going to end up with are just lots of individual
00:21
rows. So let's just imagine that I have referred three people and I've received $20 for each of them referrals. What our spreadsheet is going to end up with are three rows with $20 all being paid to me. So what that means is when we take the spreadsheet that we generate and we put it into something like PayPal mass payments, that is going to send me $20, then $20 again, then $20 again.
00:44
When in actual fact what we want to do is not do that and we want to send $60 in total. So what we want to do is come up with a way that we can use this query but also down here pass through a grouped together version where we sum up the amount that we need to send. So what we're going to do is head over to the database and make sure we don't mark this paid out. We're going to duplicate this
01:07
record down and we're going to see how this comes together. Okay so let's duplicate this now. So let's just say that I have referred three times and that's going to give me $1,600. Just for the sake of this being easy I'm going to change this to $20 for each referral so we can make sure that this adds up to $60. Okay let's talk about what I mean. So the payouts that we've got here, let's say
01:29
down here we send the email that is going to generate out this spreadsheet. Well let's just die dump on the payouts that we've got here and let's say get because at the moment we're not using that and let's just say to array. So we're going to use an array as a kind of visual representation of what our spreadsheet is eventually going to look like. Okay when I give this a
01:47
refresh, yeah so I included user earlier but we don't actually have that so we need to know who owns this referral. So let's go ahead and just say this belongs to this referral payment, this belongs to user. So we've got that in there and actually we don't think we're going to need that at the moment so let's just kind of leave that out for now. Okay so when I give this a
02:09
refresh now we've got three payments that we can send out. One for $20, another one for $20 and of course another one for $20. That's not what we want, we want a total of $60 to this user and we want to include their PayPal email address. So down here what we're going to do is we're going to create out another sort of leading on from this query because we do need this query to create this
02:33
or update this but we also want to get this information out as well. So I'm going to go ahead and create out a records or whatever we want to call it and I'm going to reference payouts and what we could actually do to make this easier let's go and put this marking up here and then down here we'll send the email last. It doesn't really matter which order we do it in so let's
02:55
go ahead and say payout and then from this from these two conditions we want to build up the records we want to add to this CSV. So what we want to do is again say get and to array so we can keep an eye on that and we'll die dump on their records and just temporarily comment this out. So find out what we need to mark as paid mark it as paid up here and then generate out the data
03:19
that we're going to put into our CSV so give that a refresh we get the same thing. Okay so for these records then let's pull this down what do we need to do well the first thing we want to do is select using a raw statement and we want to sum up the amount and we'll just call that amount so if we just remind ourselves that's this value here that we're paying out so if we just run this now it's
03:44
going to look like this and sure enough we get six six thousand which is sixty dollars but we want to include the user who we're paying that sixty dollars out to so what we can do is left join on so we're going to left join the users table and we're going to say users id where that equals the referral payments user id so we've joined that user onto here now we don't see a difference
04:12
because we're not extracting any other information out remember this data needs to be exactly as we want in the spreadsheet so that's why we're doing it this way okay so we want to grab the user's paypal email address and include that in there and because we're doing this like this by summing this up what we need to do is now group this by that user id because we only want these to be
04:36
sent out uniquely per user so we're going to group it by the user id that we have stored in here so give that a refresh and there we go so now what we've got is a kind of csv line or a row that's sending out sixty dollars to this email address we'll experiment more with some other payments for other users in just a minute as well okay so now that we've done that we are pretty
04:59
good to go we've got everything that we need in here that we can send through to pay out there's a lot more information you could select in here if you wanted to include that in the final spreadsheet but i'll let you add to this as you need all you need to do is if you need something else from a user go ahead and grab them for example you might want to grab the user's name as
05:19
well and that would be included in the spreadsheet as well and then if you need to name these differently for the spreadsheet purpose you can change them as well with the as keyword in sql okay so now that we've got this let's just experiment with adding some more records in here for other users in our app now we don't have many other users we've got mabel so let's use her as an
05:40
example so let's create out another payment in here for user id of two and we'll just keep the referred user the same it doesn't really matter and let's set the payment total to let's just change it around just so it's a little bit easier and that should be good so let's duplicate this row down and we'll set that to 20 so that should be 30 in total for that user mabel now mabel
06:08
probably doesn't have a paypal email address so let's set that in there manually just for testing and let's try this out so let's go over to the browser give that a refresh we've got two rows now so we've got 30 for mabel and 60 for alex great so there we go that is the data grouped in merged so when we eventually pass this down to generate our spreadsheet from this or a csv
06:33
from this they are going to be the rows that are in there and that makes a lot more sense than paying out multiple small amounts to lots of different users so let's go over and look at sending the email first of all and then we will build this spreadsheet up and get that sent along with the email as well

Episode summary

In this episode, we're tackling the important step of grouping payouts before sending payments or generating our payout spreadsheet. Instead of sending multiple small payments for each referral, we want to combine all payouts for each user into a single amount. For example, if someone has referred three users at $20 each, we don't want to pay them $20 three times—we want to pay them $60 all at once.

We'll walk through the process of updating our database queries so that we group the referral payouts by user and sum up the total amount to pay each one. There's a bit of SQL involved here: we use aggregation functions to sum the values, and we join the users table so we can grab the necessary details—like the user's PayPal email—to include in the final spreadsheet.

To make sure everything works, we test it out by adding payments for multiple users (like "Mabel" and "Alex") and check the spreadsheet-like array output to verify that each user only gets one payout row with the correct total. The result is a much cleaner and more logical way to send mass payments, avoiding unnecessary and confusing multiple small transfers.

By the end of this video, you'll have a solid approach for grouping and summarizing payouts, setting things up perfectly for the next step: generating the spreadsheet and sending out the emails to your users!

Episode discussion

No comments, yet. Be the first!