Elixir (SqlAlchemy): relations between 3 tables with composite primary keys

Question

I've 3 tables:

• A Company table with (company_id) primary key
• A Page table with (company_id, url) primary key & a foreign key back to Company
• An Attr table with (company_id, attr_key) primary key & a foreign key back to Company.

My question is how to construct the ManyToOne relation from Attr back to Page using the existing columns in Attr, i.e. company_id and url?

from elixir import Entity, has_field, setup_all, ManyToOne, OneToMany, Field, Unicode, using_options
from sqlalchemy.orm import relation

class Company(Entity):
    using_options(tablename='company')
    company_id = Field(Unicode(32), primary_key=True)
    has_field('display_name', Unicode(255))
    pages = OneToMany('Page')

class Page(Entity):
    using_options(tablename='page')
    company = ManyToOne('Company', colname='company_id', primary_key=True)
    url = Field(Unicode(255), primary_key=True)

class Attr(Entity):
    using_options(tablename='attr')
    company = ManyToOne('Company', colname='company_id', primary_key=True)
    attr_key = Field(Unicode(255), primary_key=True)
    url = Field(Unicode(255)) #, ForeignKey('page.url'))
    # page = ManyToOne('Page', colname=["company_id", "url"])
    # page = relation(Page, backref='attrs', foreign_keys=["company_id", "url"], primaryjoin=and_(url==Page.url_part, company_id==Page.company_id))

I've commented out some failed attempts.

In the end, Attr.company_id will need to be a foreignkey to both Page and Company (as well as a primary key in Attr).

Is this possible?

Answer 1

Yes you can do this. Elixir doesn't have a built in way to do this, but because it's a thin wrapper on SQLAlchemy you can convince it to do this. Because Elixir doesn't have a concept of a many-to-one relation that reuses existing columns you need to use the GenericProperty with the SQLAlchemy relation property and add the foreign key using table options. The following code should do what you want:

from elixir import Entity, has_field, setup_all, ManyToOne, OneToMany, Field, Unicode, using_options, using_table_options, GenericProperty
from sqlalchemy.orm import relation
from sqlalchemy import ForeignKeyConstraint

class Company(Entity):
    using_options(tablename='company')

    company_id = Field(Unicode(32), primary_key=True)
    display_name = Field(Unicode(255))
    pages = OneToMany('Page')

class Page(Entity):
    using_options(tablename='page')

    company = ManyToOne('Company', colname='company_id', primary_key=True)
    url = Field(Unicode(255), primary_key=True)
    attrs = OneToMany('Attr')

class Attr(Entity):
    using_options(tablename='attr')

    page = ManyToOne('Page', colname=['company_id', 'url'], primary_key=True)
    attr_key = Field(Unicode(255), primary_key=True)

    using_table_options(ForeignKeyConstraint(['company_id'], ['company.company_id']))
    company = GenericProperty(relation(Company))