23andreas's avatar

Calculating income each day for past 7 days

Hello. I'm currently working on displaying the income for an e-commerce site the past 7 days in a chart.

Orders are stored in a table called orders. Orders contains a column called itemID referring to a table called items. Items table contains a price column. Each order can only have one item.

My order model has a function like this

public function item(){
        return $this->belongsTo(Item::class);
}

So to calculate the income per day, would the best solution be retrieving all orders past 7 days from the db, then loop through them and do something like $incomeLastWeek[$order->created_at] += $order->item->price;

What are your opinions? What do you think would be the best solution here? Thank You!

0 likes
3 replies
KenoKokoro's avatar

Maybe in this case it would be better if you use simple join to group by the date and sum the price column.

I don't know with how big data are you working with.

jekinney's avatar

Eloquent has a whereDay().

http://laraveldaily.com/eloquent-date-filtering-wheredate-and-other-methods/

I suggest using a reports or summary table. I fire scheduled commands (commands so I can run them on the fly too) to store reports.

This way you get expected data, quickly and efficiently with out running expensive queries every page load (that ultimately puts out the same data daily).

Matter of fact I have a two year old app that uses lumen just for reports and analytic data in reports tables. Json might be a good fit to save as, allows easy adjustments to code with out messing with table structure later and most charts use json anyways.

2 likes
Swaz's avatar

With the query builder you could do something like this:

$date = Carbon::now()->subDays(7)->startOfDay();

return DB::table('orders')
    ->where('orders.created_at', '>=', $date)
    ->join('items', 'orders.item_id', '=', 'items.id')
    ->groupBy(DB::raw('DATE(orders.created_at)'))
    ->select(
        DB::raw('DATE(orders.created_at) as date'),
        DB::raw('SUM(items.price) as total')
    )->get();

Output:

[
    {
        "date": "2017-01-19",
        "total": "6500"
    },
    {
        "date": "2017-01-20",
        "total": "1000"
    },
    {
        "date": "2017-01-21",
        "total": "4500"
    }
]

Please or to participate in this conversation.