SQLAlchemy ORM query to return List of Children of a Parent Id if other Parent attributes match user input

40 views Asked by At

I'm using SQLAlchemy Version 2.0

Lets say I have a Parent table - Food_Category and a Child table - Food_Items. One-to-many relationship. Below is the code

class FoodCategoryModel(db.Model):
    __tablename__ = 'food_category'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    foodItems = db.relationship("FoodItemModel", back_populates="foodCategory", lazy="dynamic")


class FoodItemModel(db.Model):
    __tablename__ = 'food_item'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String)
    foodCategoryId = db.Column(db.Integer, db.ForeignKey("food_category.id"), nullable=False)
    foodCategory = db.relationship("FoodCategoryModel", back_populates="foodItems")

Then I add the Food Category and Food items:

db.session.add(FoodCategoryModel(id=1, name='Fruits' ))
db.session.add(FoodCategoryModel(id=2, name='Vegetables' ))
db.session.add(FoodItemModel(name='Banana', foodCategoryId=1 ))
db.session.add(FoodItemModel(name='Apple', foodCategoryId=1 ))
db.session.add(FoodItemModel(name='Orange', foodCategoryId=1 ))
db.session.add(FoodItemModel(name='Tomato', foodCategoryId=2 ))

Now, if a user send a query to the FoodCategoryModel with 'Fruits', I want it to return the list of all the Food Item objects from the FoodItemModel. Like - ['Class object Banana', 'Class object Apple', 'Class object Orange']. I know there are multiple ways to achieve this, but I'm looking for the most elegant way to send a query to the Database.

I tried this,

stmt = db.select(FoodCategoryModel.foodItems).where(FoodCategoryModel.name == 'Fruits')
db.session.scalars(stmt).all()

But strangely this returns a list of Booleans-

[True, True, True]

I want it to return the list of Food item objects. Really appreciate any help and support from the Community.

PS - I'm using SQL Alchemy 2.0

0

There are 0 answers