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

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
24 episodes2 hrs 39 mins

Overview

Let's build a feature complete Laravel referral system, completely from scratch.

We’ll cover generating referral codes, setting a cookie to track the referral process, hooking up referrals to subscriptions and displaying detailed referral stats in a dashboard, so your users can see how they're doing.

On the admin side, we'll set up a job to automatically generate a CSV with all the amounts you need to pay out each month, automatically mark referrals as paid, and display historical referral payments for users.

Alex Garrett-Smith
Alex Garrett-Smith
Hey, I'm the founder of Codecourse!

Episode discussion

No comments, yet. Be the first!