Error create schema of two tables, each of which has foreign key to another

[serg-yalosovetsky]

I have existing databases in Django, and i need to just reflex this db in Piccolo.
So i created a file tables, and added this tables here:

class User(Table, tablename="zeem_auth_app_user"):
    id = Integer(primary_key=True)

    birth_date = Date()
    is_guest = Boolean()
    nickname = Varchar(length=36, null=True)
    is_changed_nickname = Boolean()
    fullname = Varchar(length=36)
    email = Varchar(unique=True)
    is_online = Boolean()

    password_raw = Varchar(length=100)
    country = Varchar(length=50, null=True)
    device = Varchar(length=50, null=True)
    phones = Varchar(length=100, null=True)
    image = Varchar()

    spotify_tokens = JSONB(null=True)
    firebase_tokens = Text()

    app_session_full = Integer()
    app_session_count = Integer()
    app_session_average = Float()
    room_session_full = Integer()
    room_session_count = Integer()
    room_session_average = Float()

    all_notifications = Boolean()
    chat_notifications = Boolean()
    follow_notifications = Boolean()

    new_followings_count = Integer()
    fyuzing_id = ForeignKey(references=LazyTableReference("Room", module_path=__name__), on_delete=OnDelete.set_null, null=True)
    subscribe_map = JSONB()
    
    friends = M2M(LazyTableReference("Friends", module_path=__name__))
    following = M2M(LazyTableReference("Fuhrership", module_path=__name__))
    blocked_users = M2M(LazyTableReference("BlockedUsers", module_path=__name__))
    users_to_notify = M2M(LazyTableReference("UsersToNotify", module_path=__name__))

    categories = M2M(LazyTableReference("UserCategories", module_path=__name__))
    liked_rooms = M2M(LazyTableReference("LikedRooms", module_path=__name__))
    liked_playlists = M2M(LazyTableReference("LikedPlaylists", module_path=__name__))
    liked_songs = M2M(LazyTableReference("FavoritesSong", module_path=__name__))

    invitations = M2M(LazyTableReference("Invitations", module_path=__name__))
    invited_to_rooms = M2M(LazyTableReference("InvitedUsers", module_path=__name__))
    members_of_room = M2M(LazyTableReference("Members", module_path=__name__))
    groups = M2M(LazyTableReference("FGroupUsers", module_path=__name__))


class Room(Table, tablename="zeem_auth_app_room"):
    name = Varchar(length=150, index=True, required=True)
    private = Boolean(default=False)
    owner_id = ForeignKey(references=User, on_delete=OnDelete.cascade)
    password = Varchar(length=4)
    created = Timestamptz()
    updated = Timestamptz()
    image = Varchar(length=255)
    location = Varchar(length=255, null=True)
    longitude = Float(null=True)
    latitude = Float(null=True)
    info = Text(null=True)


    control = Varchar(length=11)
    is_started = Boolean(default=False)
    is_stopped = Boolean(default=False)
    is_private_playist = Boolean(default=False)
    order = Text()

    categories = M2M(LazyTableReference("RoomCategories", module_path=__name__))
    members = M2M(LazyTableReference("Members", module_path=__name__))
    invited_users = M2M(LazyTableReference("InvitedUsers", module_path=__name__))
    invited_groups = M2M(LazyTableReference("InvitedFGroups", module_path=__name__))

    liked = M2M(LazyTableReference("LikedRooms", module_path=__name__))
    invitations = M2M(LazyTableReference("Invitations", module_path=__name__))

(there are also all small tables for M2M, and some other tables like Song, Playlist and Categories, but they are nor important to this question)

But Piccolo send an error
ValueError: Can't find a Table subclass called Room in zeem_publish_worker.tables
until i comment line

fyuzing_id = ForeignKey(references=LazyTableReference("Room", module_path=__name__), on_delete=OnDelete.set_null, null=True)

if i change table User with table Room ( so i don't need use lazy table reference in table User), i have the same problem with table User.
Is there solution to create exact reflex schema in Piccolo?

[dantownsend]

Sometimes LazyTableReference will still cause circular import errors for complex schemas. If you can use a direct reference instead, that's preferable.

My strategy is usually:

• Rearrange the tables within the file
• Split the tables across multiple files (this usually helps quite a bit)

But as these tables reference each other, you might be able to do something like this:

class User(Table):
    ...

class Room(Table):
    user = ForeignKey(User)

# Add the FK from User to Room afterwards
User.room = ForeignKey(Room)
User.room._setup()
User._meta.columns.append(User.room)
User._meta.foreign_key_columns.append(User.room)

I haven't had a chance to try this out, but on theory it should work 🤞

If you're able to modify the schema, you could remove the foreign key off one of the tables, and have a joining table instead, like:

class RoomOwner(Table):
    user = ForeignKey(User)
    room = ForeignKey(Room)

Which should also solve the problem.

[serg-yalosovetsky]

I cannot modify a schema, because i use this database also in another application, in Django
Django read and writes to the db, and Piccolo just read from db on event
So the only option is to only working with existing schema

I do as you recommend, but there is still error:

User.room= ForeignKey(references=Room, on_delete=OnDelete.set_null, null=True)
User.room._setup()
User._meta.columns.append(User.room)
User._meta.foreign_key_columns.append(User.room)

error is

File "C:\Users\sergy\job\zeem-publish-worker\zeem_publish_worker\tables.py", line 132, in <module>
    User.room._setup()
TypeError: ForeignKey._setup() missing 1 required positional argument: 'table_class'

If i add required positional argument (i tried either User or Table from piccolo.table):

User.room = ForeignKey(references=Room, on_delete=OnDelete.set_null, null=True)
User.room._setup(table_class=Table)
User._meta.columns.append(User.room)
User._meta.foreign_key_columns.append(User.room)

i receive another error:

  File "C:\Users\sergy\job\zeem-publish-worker\zeem_publish_worker\tables.py", line 136, in <module>
    class Song(Table, tablename="zeem_auth_app_song"):
  File "C:\Users\sergy\job\zeem-publish-worker\venv\Lib\site-packages\piccolo\table.py", line 356, in __init_subclass__
    foreign_key_setup_response = foreign_key_column._setup(
                                 ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\sergy\job\zeem-publish-worker\venv\Lib\site-packages\piccolo\columns\column_types.py", line 1995, in _setup
    self.set_proxy_columns()
  File "C:\Users\sergy\job\zeem-publish-worker\venv\Lib\site-packages\piccolo\columns\column_types.py", line 2122, in set_proxy_columns
    setattr(self, _column._meta.name, _column)
                  ^^^^^^^^^^^^^^^^^^
  File "C:\Users\sergy\job\zeem-publish-worker\venv\Lib\site-packages\piccolo\columns\base.py", line 187, in name
    raise ValueError(
ValueError: `_name` isn't defined - the Table Metaclass should set it.

[sinisaos]

@serg-yalosovetsky I'm sorry if this doesn't help, but maybe you can try adding the _name and _table to FK ColumnMeta yourself to prevent this error. Something like this

User.room = ForeignKey(references=Room, on_delete=OnDelete.set_null, null=True)
User.room._setup(table_class=Table)
# adding the `_name` and `_table`
User.room._meta._name = "room"
User.room._meta._table = User
User._meta.columns.append(User.room)
User._meta.foreign_key_columns.append(User.room)

Again, sorry if this doesn't help.

[serg-yalosovetsky]

Thank you!
it works! 🎆