Laravel how to correctly compose a database query?

Question

Laravel

I have two tables.
One is the history of purchases, the second is the history of payments.

The payment history table has a buy_id column - this is the id from the purchase history table.
I need to make sure that I can take data from the purchase history and immediately with one request and payment history.

It will become clearer when you see:

BuyHistory::whereDate('buy_history.created_at', date('Y-m-d', strtotime($day)))
            ->where('status', 'success')
            ->join('history_payments', 'buy_history.id', '=', 'history_payments.buy_id')
            ->sum('amount');

But it does not work, for some reason it captures many different extra days at once (if you remove ->join, then everything is fine, but then there is no amount column). The amount column is in the history_payments table.

Answer 1

HistoryPayments whereHas BuyHistory (hasOne relation) where (status date queries) sum amount

Answer 2

If you use the sum aggregate function, then you are expected to group records by some groupBy field