0

We have a referral program. When a referred user subscribes, the referrer should get a percentage of the amount due on the first invoice.

referral_ids = (an array of a bunch of user_ids)

I can get a single invoice per referred user like this:

invoices = Invoice.where(user_id: referred_user_ids, renewal: false).group(:user_id).pluck(:amount_due)

However, because the amount_due may vary from invoice to invoice, I'd like to know how I can ensure that the record returned from the GROUP BY is the first /oldest record within that group. Thanks in advance.

toddmetheny
  • 4,405
  • 1
  • 22
  • 39
  • order by creation-date ? – Taryn East Feb 06 '17 at 20:43
  • 1
    order will only order the group. I need the first of each of the `grouped` items. calling order with group will just give you the order of the result. Does that make sense? – toddmetheny Feb 06 '17 at 20:44
  • similar to what's happening here but was hoping to simplify / use active record: http://stackoverflow.com/questions/5140785/mysql-order-before-group-by – toddmetheny Feb 06 '17 at 20:46

1 Answers1

0

This probably isn't the solution you're hoping for, but perhaps (for business reasons) you should consider adding a new field to your invoices table such as

  • referral_fee:boolean (defaults to false)
  • referral_fee_percent (defaults to 0)

You could then facilitate multiple commissionable invoices per affiliate, and / support sliding scale commissions

  • invoice 1 - 100%
  • invoice 2 - 50%
  • etc.

It might be worth asking your product manager / CEO if they're interested in this type of functionality before dismissing this idea.

This would make finding commissionable invoices very simple.

Dan Laffan
  • 1,544
  • 12
  • 12