0

I am using SQLAlchemy with FastAPI to build an API. So my current problem is, querying the data from the database. I have multiple tables e.g. Table Header, Table Foo and Table Bar.

class Header(Base):
idno = Column(NUMBER, primary_key=True)
id_db = Column(NUMBER, primary_key=True)
authorization = Column(NUMBER)

class Foo(Base):
idno = Column(NUMBER, primary_key=True)
id_db = Column(NUMBER, primary_key=True)
idno_header = Column(NUMBER, Foreign_key(Header.idno))
id_db_header = Column(NUMBER, Foreign_key(Header.id_db))
nr = Column(NUMBER)
header = relationship('Header')


class Bar(Base):
idno = Column(NUMBER, primary_key=True)
id_db = Column(NUMBER, primary_key=True)
idno_foo = Column(NUMBER, Foreign_key(Bar.idno)
id_db_foo = Column(NUMBER, Foreign_key(Bar.id_db)
data = Column(NUMBER)
foo = relationship('Foo')

The result should contain all data from the tables. I tried to solve this with the joinedload() method but could not achieve any results.

I tried something like this:

query = db.query(Header).join(Foo).join(Bar).options(joinedload(Foo), joinedload(Bar)).filter(Bar.data == 2)

I tried replacing joinedload() with contains_eager() but could not achieve any results too.

How should i write my query to get the results ?

Thanks for any help :)

Christian Haag
  • 11
  • 1
  • The issue is probably that you are defining each `ForeignKey` separately. Looking at [this question and its answer](https://stackoverflow.com/questions/7504753/relations-on-composite-keys-using-sqlalchemy), you should define it using `__table_args__` and `ForeignKeyConstraint` to get the joins working in SQLAlchemy. I would ask **why** all your tables have composite primary keys, do you need it for some reason? In general they can be a pain to work with. – M.O. Jul 28 '22 at 09:20
  • Thanks for your answer @M.O. I removed the ForeignKey and added ForeignKeyConstraint. But by querying `query = db.query(Header).join(Foo).join(Bar).options(contains_eager(Foo), contains_eager(Bar)).filter(Bar.data == 2)` I get an error message ` sqlalchemy.exc.ArgumentError: mapper option expects string key or list of attributes` – Christian Haag Jul 28 '22 at 09:47

0 Answers0