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.

Answer 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)