php - get total and rank results of query in a table

I am trying to generate a rank report in tabular form, I have three columns Rank, Distributor name, Total sales, the rank column is for obviously the rank in integer which the person ranks on the query but if two people have the same amount of sales they should have the same rank (int value), thirdly and most importantly sales column, what I am trying to achieve here is get the total sales for each of the distributors(names) .

Purchaser: Purchaser of the order. Distributor: The person who referred the Purchaser. Sales are the total amount of orders that have been purchased by the customers and distributors they have referred.

so for each order I have to check for the purchaser then get his/her referrer which is called the Distributor then get the distributors total sales which is the total in price x quantity of product purchased by all his referred purchasers(people referred by the distributors)

I have 6 tables in my database orders order_items products users user_category

below are pictures of the database structure

categories [1]: https://i.stack.imgur.com/SgzoU.png users [2]: https://i.stack.imgur.com/wkdN0.png orders [3]: https://i.stack.imgur.com/jlFRN.png products [4]: https://i.stack.imgur.com/Fvnds.png order_items [5]: https://i.stack.imgur.com/YWoGS.png user_category [6]: https://i.stack.imgur.com/9pk1m.png

so this is what i have tried on my blade file but doesn't return any results just times out

<table class="table table-striped custom-table" id="myTable">
                        <thead>
                            <tr>
                                <th scope="col">Rank</th>
                                <th scope="col">Distributor Name</th>
                                <th scope="col">Sales</th>

                            </tr>
                        </thead>
                        <tbody>
                            @foreach ($distributors as $dist)
                                @php
                                    
                                    $user = \App\Models\User::where('id', $dist->user_id)->first();
                                    foreach ($distributors as $dist) {
                                        $user = \App\Models\User::where('id', $dist->user_id)->first();
                                        $referrals = \App\Models\User::where('referred_by', $dist->user_id)->get();
                                    
                                        foreach ($referrals as $ref) {
                                            $quer = \App\Models\Order::where('purchaser_id', $ref->id);
                                        }
                                        $order_ids = $quer->get();
                                    
                                        if (count($order_ids) > 0) {
                                            foreach ($order_ids as $id) {
                                                $items = \App\Models\OrderItem::where('order_id', $id->id);
                                            }
                                            $order_items = $items->get();
                                            $quantities = $items->value('qantity');
                                    
                                            foreach ($order_items as $orditem) {
                                                $price = \App\Models\Product::where('id', $orditem->product_id)->value('price');
                                                $total = $price * $orditem->qantity;
                                            }
                                    
                                            $sales += $total;
                                        }
                                    }
                                @endphp

                                <tr scope="row">
                                    <td>
                                        {{ $loop->iteration }}
                                    </td>
                                    <td>
                                        {{ $dist->firstname }} {{ $dist->lastname }}
                                    </td>
                                    <td>
                                        {{ number_format($sales) }}
                                    </td>

                                </tr>
                            @endforeach
                        </tbody>

                    </table>
Copy code

and in my controller returning the page

public function rank_report () {


    
    $distributors = UserCategory::where('category_id', 2)->get();
    $sales = 0;
  
                                    
    return view('rank_report',compact('distributors', 'sales'));
}
Copy code

also I would like to rank the result from highest to lowest first 100 of the results and display them on the table . Any other suggestion of a better way to do this will also be appreciated

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.