Pivot Table Backend Relationship Setup

I am so close to having this work minus one problem!

I have the following tables and setup (only the relevant fields included for simplicity here):

ryansandnes_strata_units: id, unit_number
ryansandnes_strata_fees: id, title, amount
ryansandnes_strata_unit_fees: id (I believe very important for what I need to do), unit_id, fee_id, balance, due_date

The unit table represents a townhouse/apartment unit.
The fee table represents the fee that unit must pay (monthly strata fee of amount $455 for example)
The unit_fees table is to keep track of the balance paid towards each fee that will get updated when a payment is made via another payments table not included in this example.

There may be multiple fees associated with a unit that don’t have a due date or even have the same due date, so a scenario like this is absolutely possible:

id | fee_id | unit_id | due_date | balance

1 | 1 | 1 | null | 455 ← Full amount owing for this fee
2 | 1 | 1 | null | 400 ← They paid $55 towards this fee
3 | 1 | 1 | 2023/10/04 | 425 ← They paid $30 towards this fee
4 | 1 | 2 | 2023/05/01 | 430 ← They paid $25 towards this fee

I edited my database manually as shown above just to get it setup the way I feel it should be and have been able to get the list to show up perfectly in my units config_relation.yaml (clip below):

view:
list:
columns:
title:
label: Title
searchable: true
amount:
label: Base Amount
searchable: true

          pivot[balance]:
              label: Balance
          pivot[due_date]:
              label: Due Date
              searchable: true
  toolbarButtons: create|add|remove

If I click “add” however, it will not allow me to select a fee that is already associated with that particular unit in the unit_fees table, it just shows up empty (because it has already been associated with the unit even though it should be able to be associated an unlimited number of times). When I click one of the fees in the list, even though it shows them all the way I’d expect, it always goes to the “first” fee it finds in the table.

For example, if I were to select:

2 | 1 | 1 | null | 400,
the popup window uses the pivot table
1 | 1 | 1 | null | 455

I figure a pivot model is going to be necessary on this one, I just don’t know how to force it to use the incremental ID field for editing rather than it expecting unit_id and fee_id to be unique.

I hope all of this made sense! I feel I’m extremely close. Also, if this is terrible design I would love to know that as well because I’m trying to do things the proper way these days=)

Thanks!

Hey @theshado40

Just as sort of a general idea, if you find yourself needing to add a primary key to a pivot table. You should shift the design to use an intermediary model.

So instead of

Model -> Pivot <- Model

Go with

Model <-> Model <-> Model

It solves a lot of issues especially when pivot data becomes more complex