One-to-one and one-to-many relations to the same entity

[cschmatzler]

Hi! I am trying to use a database with content history. I will just give you how I would like the models to work:

class Organization(database.Entity):
    id = PrimaryKey(UUID, default=uuid4, auto=True)
    content = Optional("OrganizationContent")
    history = Set("OrganizationContent")
    users = Set("User")

class OrganizationContent(database.Entity):
    id = PrimaryKey(UUID, default=uuid4, auto=True)
    organization = Required("Organization")
    name = Required(str)
    active = Required(bool, default=True)
    created_on = Required(datetime, default=datetime.utcnow())
    created_by = Required("User")

class User ...

Now, I want Organization.content to be the current revision, while Organization.history is a set of all old revisions. This works fine with pure SQL, but trying to run generate_mapping() on the above code results in
Reverse attribute for Organization.history not found

Adding reverse="organization" to Organization.history changes the error:
Inconsistent reverse attributes Organization.history and OrganizationContent.organization

Pony doesn't like that two reverse attributes are pointing to the same key.

Is there any way to elegantly solve this? Cheers

[llorencs]

Maybe this fixes your issue

class Organization(db.Entity):
    id = PrimaryKey(int, auto=True)
    history = Set('OrganizationContent', reverse='organization_history')
    organization_content = Required('OrganizationContent', reverse='organization')


class OrganizationContent(db.Entity):
    id = PrimaryKey(int, auto=True)
    organization_history = Required(Organization, reverse='history')
    organization = Optional(Organization, reverse='organization_content')

I don't know if it is what you're looking for, but that would be a possible solution. This declaration is eqiovalent in SQL (SQLLite code) to :

CREATE TABLE "OrganizationContent" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "organization_history" INTEGER NOT NULL REFERENCES "Organization" ("id") ON DELETE CASCADE
);

CREATE INDEX "idx_organizationcontent__organization_history" ON "OrganizationContent" ("organization_history");

CREATE TABLE "Organization" (
  "id" INTEGER PRIMARY KEY AUTOINCREMENT,
  "organization_content" INTEGER NOT NULL REFERENCES "OrganizationContent" ("id")
);

CREATE INDEX "idx_organization__organization_content" ON "Organization" ("organization_content")