Model many-to-many over three tables in SQLAlchemy
I have difficulty to model these relations.
There is products, each one belonging to a product_type. Product_types have a list of different features eg. color, size, weight, volume, in a relation of many-to-many. In their turn, each features have differents feature_options eg. red, blue, green, black for colors. Finally products have some of the feature_options. One example should be the product BIC2006 which have the product_type pen, whose features are color and weight, and which has in these features the feature_options red and blue for the color and 1g and 2g for the weight.
At the end I would like to store the feature_options by products and query by products the list of feature_options by feature :
BIC2006 - Pen
Color : Red, Blue
Weight : 1g, 2g
What should be the best DB model to use with SQLAlchemy?
For now, as I need to store the feature_options by products I tried with this, but I'm not sure about how to retrieve the sort by features :
class ProductTypes_Features(Base):
__tablename__ = 'product_types_features'
product_type_id = Column(Integer, ForeignKey('product_types.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
class Feature_FeatureOptions(Base):
__tablename__ = 'feature_feature_options'
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
class Product_FeatureOptions(Base):
__tablename__ = 'product_feature_options'
product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id')) #??
class Products(Base):
__tablename__ = 'product'
id = Column(Integer, primary_key=True)
product_type_id = Column(Integer, ForeignKey('product_types.id'))
feature_options = relationship("Product_FeatureOptions")
class FeatureOptions(Base):
__tablename__ = 'feature_options'
id = Column(Integer, primary_key=True)
class Features(Base):
__tablename__ = 'features'
id = Column(Integer, primary_key=True)
feature_options = relationship("Feature_FeatureOptions")
class ProductTypes(Base):
__tablename__ = 'product_types'
id = Column(Integer, primary_key=True)
features = relationship("ProductType_Features")
How can I query?
session.query(Products.id,
Products.feature_options.feature_id,
Products.feature_options.feature_option_id).
order_by(...).all()
Thanks a lot for your best practices advices!
python sql sqlalchemy
add a comment |
I have difficulty to model these relations.
There is products, each one belonging to a product_type. Product_types have a list of different features eg. color, size, weight, volume, in a relation of many-to-many. In their turn, each features have differents feature_options eg. red, blue, green, black for colors. Finally products have some of the feature_options. One example should be the product BIC2006 which have the product_type pen, whose features are color and weight, and which has in these features the feature_options red and blue for the color and 1g and 2g for the weight.
At the end I would like to store the feature_options by products and query by products the list of feature_options by feature :
BIC2006 - Pen
Color : Red, Blue
Weight : 1g, 2g
What should be the best DB model to use with SQLAlchemy?
For now, as I need to store the feature_options by products I tried with this, but I'm not sure about how to retrieve the sort by features :
class ProductTypes_Features(Base):
__tablename__ = 'product_types_features'
product_type_id = Column(Integer, ForeignKey('product_types.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
class Feature_FeatureOptions(Base):
__tablename__ = 'feature_feature_options'
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
class Product_FeatureOptions(Base):
__tablename__ = 'product_feature_options'
product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id')) #??
class Products(Base):
__tablename__ = 'product'
id = Column(Integer, primary_key=True)
product_type_id = Column(Integer, ForeignKey('product_types.id'))
feature_options = relationship("Product_FeatureOptions")
class FeatureOptions(Base):
__tablename__ = 'feature_options'
id = Column(Integer, primary_key=True)
class Features(Base):
__tablename__ = 'features'
id = Column(Integer, primary_key=True)
feature_options = relationship("Feature_FeatureOptions")
class ProductTypes(Base):
__tablename__ = 'product_types'
id = Column(Integer, primary_key=True)
features = relationship("ProductType_Features")
How can I query?
session.query(Products.id,
Products.feature_options.feature_id,
Products.feature_options.feature_option_id).
order_by(...).all()
Thanks a lot for your best practices advices!
python sql sqlalchemy
add a comment |
I have difficulty to model these relations.
There is products, each one belonging to a product_type. Product_types have a list of different features eg. color, size, weight, volume, in a relation of many-to-many. In their turn, each features have differents feature_options eg. red, blue, green, black for colors. Finally products have some of the feature_options. One example should be the product BIC2006 which have the product_type pen, whose features are color and weight, and which has in these features the feature_options red and blue for the color and 1g and 2g for the weight.
At the end I would like to store the feature_options by products and query by products the list of feature_options by feature :
BIC2006 - Pen
Color : Red, Blue
Weight : 1g, 2g
What should be the best DB model to use with SQLAlchemy?
For now, as I need to store the feature_options by products I tried with this, but I'm not sure about how to retrieve the sort by features :
class ProductTypes_Features(Base):
__tablename__ = 'product_types_features'
product_type_id = Column(Integer, ForeignKey('product_types.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
class Feature_FeatureOptions(Base):
__tablename__ = 'feature_feature_options'
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
class Product_FeatureOptions(Base):
__tablename__ = 'product_feature_options'
product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id')) #??
class Products(Base):
__tablename__ = 'product'
id = Column(Integer, primary_key=True)
product_type_id = Column(Integer, ForeignKey('product_types.id'))
feature_options = relationship("Product_FeatureOptions")
class FeatureOptions(Base):
__tablename__ = 'feature_options'
id = Column(Integer, primary_key=True)
class Features(Base):
__tablename__ = 'features'
id = Column(Integer, primary_key=True)
feature_options = relationship("Feature_FeatureOptions")
class ProductTypes(Base):
__tablename__ = 'product_types'
id = Column(Integer, primary_key=True)
features = relationship("ProductType_Features")
How can I query?
session.query(Products.id,
Products.feature_options.feature_id,
Products.feature_options.feature_option_id).
order_by(...).all()
Thanks a lot for your best practices advices!
python sql sqlalchemy
I have difficulty to model these relations.
There is products, each one belonging to a product_type. Product_types have a list of different features eg. color, size, weight, volume, in a relation of many-to-many. In their turn, each features have differents feature_options eg. red, blue, green, black for colors. Finally products have some of the feature_options. One example should be the product BIC2006 which have the product_type pen, whose features are color and weight, and which has in these features the feature_options red and blue for the color and 1g and 2g for the weight.
At the end I would like to store the feature_options by products and query by products the list of feature_options by feature :
BIC2006 - Pen
Color : Red, Blue
Weight : 1g, 2g
What should be the best DB model to use with SQLAlchemy?
For now, as I need to store the feature_options by products I tried with this, but I'm not sure about how to retrieve the sort by features :
class ProductTypes_Features(Base):
__tablename__ = 'product_types_features'
product_type_id = Column(Integer, ForeignKey('product_types.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
class Feature_FeatureOptions(Base):
__tablename__ = 'feature_feature_options'
feature_id = Column(Integer, ForeignKey('features.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
class Product_FeatureOptions(Base):
__tablename__ = 'product_feature_options'
product_id = Column(Integer, ForeignKey('products.id'), primary_key=True)
feature_options_id = Column(Integer, ForeignKey('feature_options.id'), primary_key=True)
feature_id = Column(Integer, ForeignKey('features.id')) #??
class Products(Base):
__tablename__ = 'product'
id = Column(Integer, primary_key=True)
product_type_id = Column(Integer, ForeignKey('product_types.id'))
feature_options = relationship("Product_FeatureOptions")
class FeatureOptions(Base):
__tablename__ = 'feature_options'
id = Column(Integer, primary_key=True)
class Features(Base):
__tablename__ = 'features'
id = Column(Integer, primary_key=True)
feature_options = relationship("Feature_FeatureOptions")
class ProductTypes(Base):
__tablename__ = 'product_types'
id = Column(Integer, primary_key=True)
features = relationship("ProductType_Features")
How can I query?
session.query(Products.id,
Products.feature_options.feature_id,
Products.feature_options.feature_option_id).
order_by(...).all()
Thanks a lot for your best practices advices!
python sql sqlalchemy
python sql sqlalchemy
asked Nov 10 at 20:00
Romain
263
263
add a comment |
add a comment |
active
oldest
votes
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53242899%2fmodel-many-to-many-over-three-tables-in-sqlalchemy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53242899%2fmodel-many-to-many-over-three-tables-in-sqlalchemy%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown