Reflection on a table with self-referencing ForeignKey does not complete

[knguyen5]

When running a reflection on a table that includes a self-referencing ForeignKey, such as the example below, the process hangs or never completes:

class Musician(Table):
    name = Varchar(length=100)
    instructor = ForeignKey(references='self')

After removing the foreign key or replacing with another table reference, the reflection completes as expected.

[AliSayyah]

I will fix this recursive problem but i wonder if this is a valid scenario.

[knguyen5]

Thanks @AliSayyah! The scenario that I'm trying to capture in this data modeling is when there's an unknown number of parent entities and child entities. To keep the schema flexible, it's kept in a long table format.

For example, this could be useful in a growing organization tree and mapping out which employee reports to another employee (whom might report to someone else).

I'm no data modeling expert, so I'm very open to other models that might cover this type of scenario.

[AliSayyah]

@knguyen5 that makes sense to me. I don't have any alternative to suggest but i think @dantownsend can help more in this matter.

[dantownsend]

I've used recursive foreign keys a few times in the past.

Things like this are quite common:

class Employee(Table):
    name = Varchar(length=100)
    manager = ForeignKey(references='self')

It should be possible to support this in piccolo schema generate. It's just an edge case we overlooked.

[wmshort]

I think this should be a pretty straightforward fix. Postgres doesn't have any special syntax for self-referencing tables, so far as I can tell, literally just the table name itself, like any other. So if when parsing the table meta we find that a FK column refers to the same table it is defined on (a simple equality check), this should be reflected by Piccolo's "self" convention rather than the table name

[AliSayyah]

@wmshort yes that should work.

[wmshort]

I'll make a PR as soon as I can get to this, sorry for delay

[dantownsend]

Fixed by #305

[wmshort]

👍