Adding tabs to reflect a hierarchy

[Chris Hastie]

I think I may struggle to explain this, but here goes...

There is a hierarchy to the data in my application, and I'd love to have tabs across the tab that reflected this. It's sort of there down to one level, but doesn't extend beyond that.

So details. The app records details of individual divers times and depths on dives that take place during trips. So the hierarchy is trips can have many dives. The dives can be done by many divers. There is a relationship defined between the table log_trips and log_dives, and another between log_dives and log_diverDetails.

When viewing a trip there is a tab with the trip data, and another that list the dives. Great.

When viewing a dive, there is a tab that lists the dive data, and another that lists the records from log_diverDetails. I would like to add a tab to this view that shows the relevant record from log_trip. Is this possible?

 

[Patrick Reinhard]

If I understand correctly, your tables should be defined as follows as far as keys and foreign keys are concerned:

CREATE TABLE log_trips (
  log_trips_id SERIAL /* primary key */
, payload TEXT /* or other data you save */
, PRIMARY KEY (log_trips_id)
)

CREATE TABLE log_dives (
  log_dives_id SERIAL /* primary key */
, payload TEXT /* or other data you save */
, log_trips_fk BIGINT /* foreign key pointing to log_trips */
, PRIMARY KEY (log_dives_id)
)


CREATE TABLE log_diverDetails (
  log_diverDetails_id SERIAL /* primary key */
, payload TEXT /* or other data you save */
, log_dives_fk BIGINT /* foreign key pointing to log_dives */
, PRIMARY KEY (log_diverDetails_id)

)

Then you should put in the .../tables/log_diverDetails/relationships.ini file something like this:

[trips]
__sql__ = "SELECT T.* FROM log_trips T LEFT JOIN log_dives D ON D.log_trips_fk=T.log_trips_id AND D.log_dives_id='$log_dives_fk'"
actions:addexisting=0
actions:addnew=0
actions:remove=0
action:order=10

[dives]
__sql__ = "SELECT D.* FROM log_dives D WHERE D.log_dives_id='$log_dives_fk'"
action:order=20

The actions:... statements are added basically to avoid messing up your tables, as I guess that the "trips" relationship should be "read only".

I don't know if and how the same result could be obtained using the "implicit" notation for the relationships, i.e. the one that does not define the relationship with the __sql__ clause.

By the way, if in your use-cases the same diver can make mutiple dives and multiple trips, I would suggest you to create a divers table and a one-to-many relationship table linking the divers to their dives.

This way you could insert the divers just once and not again and again at each dive, and the relationships logic could be cleaner.