Elixir (SqlAlchemy): relationships between 3 tables with composite primary keys
I have 3 tables:
- Companies table with
(company_id)
primary key - Table of pages with
(company_id, url)
primary key and foreign key to the company - Attr table with primary key
(company_id, attr_key)
and foreign key to the company.
My question is how to build a ManyToOne relationship from Attr back to the 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 have commented on some of the failed attempts.
After all, Attr.company_id must be a foreign key for both pages and company (and also for the primary key in Attr).
Is it possible?
a source to share
Yes, you can do it. Elixir doesn't have a built-in way to do this, but since it's a thin wrapper around SQLAlchemy, you can convince it to do it. Since Elixir has no concept of many-to-one relationship that reuses existing columns, you need to use GenericProperty with the SQLAlchemy relationship property and add the foreign key using table parameters. 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))
a source to share