sqlalchemy orm | fastAPI querying + joining three tables to get parents and all children

Question

I'm trying to make have a route in my fastAPI that gives back a list of all parents.portfolios and all the children or stocks that are associated with each of them PLUS the extra data that is in the association table (for that relationship).

The response is suppose to look somewhat like this

[ { "parrent1_attr1": bla,
    "parrent1_attr2": bla,
    "children": [ {
        "child1_attr1": bla,
        "child1_attr2": bla},
        {"child2_attr1": bla,
         "child2_attr2": bla}]
},
etc...]

Right now the route that produces this looks like this:

@router.get("/")
def get_all_portfolios(db: Session = Depends(get_db), current_user: int = Depends(oauth2.get_current_user)):

    results = db.query(models.Portfolio).options(joinedload(models.Portfolio.stocks)).all()
    return results

But this gives me the wrong result.

This results in this.

[ { "parrent1_attr1": bla,
    "parrent1_attr2": bla,
    "children": [ {
          "association_table_attr1": bla
           "association_table_attr2": bla},]

So I get data from the association table back instead of from the children.

The models I have are here.

class Portfolio(Base):
    __tablename__ = "portfolios"

    id = Column(Integer, primary_key=True, nullable=False)
    ...
    stocks = relationship("PortfolioStock", back_populates="portfolio")

class Stock(Base):
    __tablename__ = "stocks"

    id = Column(Integer, primary_key=True, nullable=False)
    ...
    portfolios = relationship("PortfolioStock", back_populates="stock")

class PortfolioStock(Base):
    __tablename__ = "portfolio_stocks"
    id = Column(Integer, primary_key=True)
    stock_id = Column(Integer, ForeignKey("stocks.id", ondelete="CASCADE"))
    portfolio_id = Column(Integer, ForeignKey("portfolios.id", ondelete="CASCADE"))
    count = Column(Integer, nullable=True)
    buy_in = Column(Float, nullable=True)
    stock = relationship("Stock", back_populates="portfolios")
    portfolio = relationship("Portfolio", back_populates="stocks")

Let me know if you need more information. I appreciate your help.

Answer 1

For anybody that is looking for an answer, here is how I fixed it.

I used the query from Ian that he mentioned above, (thank you a ton for that). And then I just manually declared the structure I wanted to have.

The whole code looks like this

    results = (
        db.query(models.Portfolio)
        .options(joinedload(models.Portfolio.stocks).joinedload(models.PortfolioStock.stock))
        .all()
    )
    result_list = []
    for portfolio in results:
        result_dict = portfolio.__dict__
        stock_list = []
        for sto in result_dict["stocks"]:
            sto_dict = sto.__dict__
            temp_sto = {}
            temp_sto = sto_dict["stock"]
            setattr(temp_sto, "buy_in", sto_dict["buy_in"])
            setattr(temp_sto, "count", sto_dict["count"])
            stock_list.append(temp_sto)
        result_dict["stocks"] = stock_list
        result_list.append(result_dict)
    return result_list

What I'm doing here is firstly declare an empty list where our final results will be stored and which will be returned. Then we iterate over the query (because the query gives us a list back). So we have each "SQL alchemy Model" now as portfolio in there.

Then we can convert this into a dictionary and assign it a new variable with result_dict = portfolio.__dict__ The __dict__ method converts the model into a Python dictionary that you can work with easily.

Since that result_dict contains a list of PortfolioStock models which is the association table model in. These are stored in the stocks key we have to iterate over them to get those values as well. And here we just repeat the process.

We convert the model into a dictionary with __dict__ then make a new empty dictionary temp_sto={} and set it equal to the stock key which is the key that is linking the child to our association table. So now we have the child or stock we want to access. We can simply set our new empty dicitonary equal to that so we inherit all the information contained within. And then we just have to add all other information from the association table that we might want which can be accessed via the dictionary we defined at the beginning of the for loop sto_dict.

Once we have this we append it to an empty list we have defined outside of this for loop but inside the portfolio loop. Set the result_dict["stocks"] key (so basically the key where you want all children to be contained in) equal to that list we just appended all the dictionaries to and then append that dictionary to our result_list. And last thing to do is just to return that list, and we're done.

I have provided an agnostic approach hopefully down below

query = db.query(Parent).options(joinedload(Parent.relationship).joinedload(AssociationTable.child).all()
result_list = []
for parent in query:
    parent_dict = parent.__dict__
    child_list = []
    for association in parent_dict["relationship_name"]:
        association_dict = association.__dict__
        temp_child_dict = {}
        temp_child_dict = association_dict["child"]
        setattr(temp_child_dict, "name_you_want", association_dict["key_of_value_you_want"])
        # repeat as many times as you need
        child_list.append(temp_child_dict)
    parent_dict["children"] = child_list
    result_list.append(parent_dict)
return result_list

I hope this helps you if you are in a similar situation.