Linking two tables where the same value exists, without Primary key – SQLAlchemy

I have the following tables defined (very simplified version):

class Orders(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer,nullable=False)
    date_created = db.Column(db.DateTime, nullable=False)

class ProductOrders(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    order_id = db.Column(db.Integer, nullable=False)
    product_id = db.Column(db.Integer, nullable=False)
    base_price = db.Column(db.Float, nullable=False)

I am using BigCommerce API and have multiple order_ids in both tables. The order_id is not unique globally but is unique per store. I am trying to work out how to link the two tables. I do have a Store table (shown below) that holds the store.id for each store, but I just cannot work out how to join the Orders and ProductOrders tables together so I can access both tables data where the store.id is the same. I just want to query, for example, a set of Orders.order_id or Orders.date_created and get ProductOrders.base_price as well.

class Store(db.Model):
    id = db.Column(db.Integer, primary_key=True)

Any ideas?

Answer

Assuming id in both queries is the store_id and order_id is unique per store, you will have to apply join with AND statement.

For example: (in SQL) Orders join ProductOrders on Orders.id = ProductOrders.id and Orders.order_id = ProductOrders.order_id

Answer is based on what I have understood from your question, sorry if that’s not your required answer.

Edit: In sqlalchemy it would be something like below:

from sqlalchemy import and_

session.query(Orders, ProductOrders).filter(and_(Orders.id == ProductOrders.id, Orders.order_id == ProductOrders.order_id)).all()

References:

https://www.tutorialspoint.com/sqlalchemy/sqlalchemy_orm_working_with_joins.htm

Using OR in SQLAlchemy