I have two tables:
I run a job every night that will create entries in transactions table that is followed by job to aggregate these new transactions stored in the transactions table by day and user and put them in aggregated_transactions table.
There is a hasMany relationship between aggregated_transactions and transactions table.
Thus after a aggregated transaction is created I need to update the transactions table with the newly created aggregated transaction ID.
Issues in this approach is
- I have to create entries in aggregated transaction one by one, even thou I have aggregated array that I could have bulk inserted in a go.
- After every aggregated transaction is created I need to update transactions table and need to also keep track of the transaction IDs I need to update.
Thus running so many DB queries (using SQL it is one query task, but I need to use Eloquent for consistency)
Is there a better way to do this using eloquent.
I was thinking if I could instead of hasMany have a ManyToMany relationship and keep track of the connection between two tables in a third pivot table.