get rows by date where "index" stops on current ID

1- I am sorry for the title, I couldn't describe my complex situation better.

2- I have a table for a Double Accounting System where I am trying to calculate the balance at a specific date and until a specific transaction, and due to specific situations in the frond-end i need to get the result in a single query.

Table example is like that:

| id  | date       | amount |
| --- | ---------- | ------ |
| 93  | 2018-03-02 | -200   |
| 94  | 2018-01-23 | 250    |
| 108 | 2018-03-05 | 400    |
| 120 | 2018-01-23 | 720    |
| 155 | 2018-03-02 | -500   |
| 170 | 2018-03-02 | 100    |

And here is my simple query that I am using inside a loop of every transaction, because I want to show the new BALANCE after every transaction is made:

... for ...

Transactions::where('date', '<=', $item->date)->get()

... end ...

That query is returning the balance at the END of the day, means until the last transaction made that day, and I don't want this result.

Desired result is achieved by something like:

 ... for ...

Transactions::where('date', '<=', $item->date)
-> and transaction is < index of current $item
->get()

... end ...

Of course I can't use the ID because the ID is not related in this situation, as the whole ordering and calculation operations are date related.

So basically what i want is a query to get all the transactions from the age of stone until a specific date BUT exclude all the transactions made after the CURRENT one (in the loop).

For example, in the above table situation the query for:

Transaction ID # 93 should return: 93
Transaction ID # 94 should return: 93,94
Transaction ID # 108 should return: 94,120,93,155,170,108
Transaction ID # 120 should return: 94,120
Transaction ID # 155 should return: 94,120,155
..
...
....

The last transaction to get should be the current transaction.

I hope I could clear it well, I spend 3 days searching for a solution and I came up with this slow method:

$currentBalance = Transaction::where('date', '<=', $item->date)->get(['id']);

    $array = array();
    foreach ($currentBalance as $a) {
        $array[] = $a->id;
    }
    $newBalanceA = array_slice($array, 0, array_search($item->id, $array) + 1);

    $currentBalance = Transaction::whereIn('id', $newBalanceA)->sum('amount');

    return $currentBalance;

It is slow and dirty, I appreciate saving me with a simple solution in 1 query if this is possible.

via Chebli Mohamed