Eager load column on pivot of relation
I'm currently working on an app with this set up in terms of tables/models:
- Wallets (hasMany Accounts). Columns: id
- Accounts (belongsTo Wallet, hasMany Balances). Columns: id, wallet_id
- Balances (belongsTo Account, hasMany Transactions). Columns: id, account_id
- Transactions (belongsTo Balance, belongsTo Category). Columns: id, balance_id, category_id
- Categories (belongsToMany Transactions, belongsToMany Wallets)
- Tags (belongsToMany Transactions)
- transaction_tag (pivot). Columns: id, transaction_id, tag_id
- wallet_tag (pivot). Columns: id, wallet_id, tag_id, color
- wallet_category (pivot). Columns: id, wallet_id, category_id, color
I'm allowing users to pick a color for their tags/categories themselves, and am connecting tags/categories to their wallets. The color is saved on this pivot table so that different people can have different colors for the same tag or category. I've currently opted for this set up so that, when people have multiple wallets, they can still compare transactions across wallets because they will have the same tag_id or category_id.
Currently I'm stuck on eager loading this color from the pivot table in a piece of code where I want to get the transactions for a balance:
$transactions = $balance->transactions()->with(['category', 'tags'])->get();
I've looked at the documentation and I can't use nested eager loading because the pivot table because the pivot table where the color is saved is not a relationship defined on the model.
I could of course define the color on the tags/categories tables themselves, but this would lose the re-usability of the tags/categories and this would lose the functionality of comparing transactions across wallets for users with multiple wallets, so I would prefer not to go down this route if I don't have to.
Could anyone help out with this?
Please or to participate in this conversation.