SQLAlchemy ForeignKey relation via an intermediate table

Question

Suppose that I have a table Articles, which has fields article_id, content and it contains one article with id 1.

I also have a table Categories, which has fields category_id (primary key), category_name, and it contains one category with id 10.

Now suppose that I have a table ArticleProperties, that adds properties to Articles. This table has fields article_id, property_name, property_value.

Suppose that I want to create a mapping from Categories to Articles via ArticleProperties table.

I do this by inserting the following values in the ArticleProperties table: (article_id=1, property_name="category", property_value=10).

Is there any way in SQLAlchemy to express that rows in table ArticleProperties with property_name "category" are actually FOREIGN KEYS of table Articles to table Categories?

This is a complicated problem and I haven't found an answer myself.

Any help appreciated!

Thanks, Boda Cydo.

george, i had data inserted in it. after your edit, no one knows that article_id=1 is the only article i had in the table, and property_value=10 means that i had only one category with category_id=10. — bodacydo
– bodacydo, Commented Feb 12, 2010 at 0:07
Why do you so badly want to put the Categories into the big (and generic) list of ArticleProperties? — van
– van, Commented Feb 19, 2010 at 13:36
Because Category is not much different from any other property of the article... I am just trying to logically isolate an Article, because Article is by itself, just a piece of text. — bodacydo
– bodacydo, Commented Feb 21, 2010 at 9:41

Mitch Wheat · Accepted Answer · 2010-02-12 00:23:18Z

1

Assuming I understand you question correctly, then No, you can't model that relationship as you have suggested. (It would help if you described your desired result, rather than your perceived solution)

What I think you may want is a many-to-many mapping table called ArticleCategories, consisting of 2 int columns, ArticleID and CategoryID (with respective FKs)

answered Feb 12, 2010 at 0:23

Mitch Wheat

302k44 gold badges482 silver badges552 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

bodacydo Over a year ago

An article can have many properties - category, who was the last to edit the article, phase of moon the article was created, or anything you can imagine. I can't model it as ArticleCategories, or ArticleMoonPhase, as there are infinite number of properties (which names I sometimes don't even know - users who create articles add properties to it).

bodacydo Over a year ago

The end result is that when I do something like Article.property_name and have sqlalchemy automatically figure out the value. In my simple example .property_name would be .category, and sqlalchemy would know that .category is a foreign key on Categories table, and instead of returning id=10, it would know that .category is a foreign key on Category and return a full Category object.

Collectives™ on Stack Overflow

SQLAlchemy ForeignKey relation via an intermediate table

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related