Add some additional lender stats to user view

[rrickgauer]

• product count
• agreement count
• earnings
• balance

[rrickgauer]

select 
u.id as id,
u.email as email,
u.name_first as name_first,
u.name_last as name_last,
u.birth_date as birth_date,
u.created_on as created_on,
count(p.id) as count_products,
count(pay.id) as count_agreements,
-- (select sum(Calculate_Lender_Payout(pay.price_full, pay.fee_lender, pay.starts_on, pay.ends_on))) as sum_earnings
sum(Calculate_Lender_Payout(pay.price_full, pay.fee_lender, pay.starts_on, pay.ends_on)) as sum_earnings
from Users u 
left join Products p on p.user_id = u.id
left join Payments pay on pay.product_id = p.id
left outer join Product_Requests pr on pr.payment_id = pay.id and pr.status = 'accepted'
group by u.id;

[rrickgauer]

https://learnsql.com/blog/illustrated-guide-multiple-join/

[rrickgauer]

Created MySQL Stuff

• function Calculate_Lender_Balance
• view View_Users_Lender_Stats

[rrickgauer]

another option:

select 
u.id as id,
u.email as email,
u.name_first as name_first,
u.name_last as name_last,
u.birth_date as birth_date,
u.created_on as created_on,
count(p.id) as count_products,
count(pay.id) as count_agreements,
sum(Calculate_Lender_Payout(pay.price_full, pay.fee_lender, pay.starts_on, pay.ends_on)) as lender_balance
from Users u 
left join Products p on p.user_id = u.id

left join (
    select pays.id, pays.price_full, pays.fee_lender, pays.starts_on, pays.ends_on, pays.product_id
    from Payments pays
    inner join Product_Requests pr on pr.payment_id = pays.id and pr.status = 'accepted'
) pay on pay.product_id = p.id

group by u.id;

[rrickgauer]

For the api route, include it in the /users/:user_id resource