How to add a sum of amount constraint in Laravel?

Question

I have a database with the following Relations:

There are projects - something people can purchase, also has user_id,

There are backers - the people that purchase,

There are orders - each order is a purchase of a Project by Backer (Backer can only purchase each Project once). THere is an amount - showing how much a Backer has paid to purchase the Project

What I need is to write the following query - given the $user_id, $count and $sum find all Backers that have invested at least in $count number of projects with user_id equal to $user_id and having the sum of their purchases for those projects higher then $sum.

  Backer::whereHas('projects', function ($q) use ($sum, $userId){

        $q->where('user_id', $userId);

    }, '>=', $leastProjectCount)->get();

but it does not constraint the $sum of purchases

Update: So I came up with this query:

select  * from `backers` \r\n
                    WHERE id > 0 \r\n
                    AND (select count(*)\r\n
                           FROM `projects` \r\n
                           inner join `orders` on `projects`.`id` = `orders`.`project_id` \r\n
                           where `backers`.`id` = `orders`.`backer_id` \r\n
                           and `user_id` = 6 \r\n
                           and exists (\r\n
                               select * \r\n
                               from `tags` \r\n
                               inner join `project_tag` on `tags`.`id` = `project_tag`.`tag_id` \r\n
                               where `projects`.`id` = `project_tag`.`project_id` \r\n
                               and `id` in (27)\r\n
                           ) \r\n
                          ) >= 1 \r\n
                    AND (select sum(orders.amount)\r\n
                           FROM `projects` \r\n
                           inner join `orders` on `projects`.`id` = `orders`.`project_id` \r\n
                           where `backers`.`id` = `orders`.`backer_id` \r\n
                           and `user_id` = 6 \r\n
                           and exists (\r\n
                               select * \r\n
                               from `tags` \r\n
                               inner join `project_tag` on `tags`.`id` = `project_tag`.`tag_id` \r\n
                               where `projects`.`id` = `project_tag`.`project_id` \r\n
                               and `id` in (27)\r\n
                           ) \r\n
                          ) >= 12

note that the subqueries are almost the same, except that the first uses COunt(*) and the second uses sum(orders.amount). And also note that this query runs if all the parameters were set, but it can be shorter, when some parameters are not set. The problem I am getting is that I don't have pagination with such a query.

Can we forget about all the laravel/eloquent stuff for now, and just focus on the raw sql? — Strawberry, May 08 '18 at 07:17
Possibly related https://stackoverflow.com/questions/28267550/how-to-group-and-sum-a-pivot-table-column-in-eloquent-relationship — apokryfos, May 08 '18 at 07:41

score 0 · Answer 1 · answered May 08 '18 at 21:39

0

Try this:

Backer::whereHas('projects', function ($q) use ($sum, $userId){
    $q->where('user_id', $userId)
        ->havingRaw('SUM(amount) > ? ', [$sum]);

}, '>=', $leastProjectCount)->get();

answered May 08 '18 at 21:39

Jonas Staudenmeir

24,815
6
63
109

This query does not group the orders of the project. It just checks if the sum of each project is more than some amount, not the aggregate of orders for that backer. I have tried this already. – naneri May 09 '18 at 08:52
You want the *total amount* of all the backer's orders (with `$userId`) to be higher than `$sum`, right? The query does that for me. Can you post some sample data where it doesn't work? – Jonas Staudenmeir May 09 '18 at 11:38
You can examine the query - https://pastebin.com/JXA9xdqL, you see, it does not have the grouping of the projects (orders) and that way, there is nothing to sum, it just takes the same amount. – naneri May 09 '18 at 11:43
It takes the amount of all the backer's orders and calculates their sum. Isn't that what you want? Did you try this exact query? – Jonas Staudenmeir May 09 '18 at 11:52
Can you please give feedback on the answer? – Jonas Staudenmeir Jun 08 '18 at 00:32

How to add a sum of amount constraint in Laravel?

1 Answers1