Relationships: Self-referential foreign keys

[oekeur]

Hi!

First of all: loving the product!
Not sure if this is a bug, edge-case or just forgotten :)

I'm currently working on something where I'd need to use self-referential foreign keys. (At least; I think that's the best option, open for other ideas of course.)
One of the tables in my postgres holds the data of people another holds the relationship between these people.
There can be any number(0-N) of relationships (parent||child||spouse) between these people.
Also making it one step simpler (even though I don't see how that would change the situation) by removing the 'type' of relationship, so just a 'spouseOf' I can't seem to get Budibase to work for this.

I've got my foreign keys set up as this, which works fine:

Source	Target
Source	Target
personId	people(id)
relativeID	people(id)

Whenever you select the people table as from, relationships as through and then people table again as to you get stuck on:
From/to/through tables must be different
That the through table must be different is logical, but by limiting the from/to table to be different self-referential foreign keys are not possible.

#3498 Seems to be about the same

[mjashanks]

Hi @oekeur - thanks!

This is definitely an enhancement that we would like to do - not sure when it will be done.

The only way around it at the moment is to use custom queries.

[otichy]

Foreign key on itself is really important for hierarchies, I would really love that.