CODEWITHSUNDEEP
mysqlpython-3.xflasksqlalchemyflask-sqlalchemy
Himadri Ganguly
Himadri Ganguly

Reputation: 743

How to do FLASK SQLALCHEMY query for Association Table

I have two models in MANY TO MANY Relationship and I want to get all the column values in related models in one query. But cannot get that using Flask SQLAlchemy.

But I able to get the required data using the SQL query in phpmyadmin.

Here is the query

SELECT ply_positions.plyPositionId, ply_positions.plyPositionName, ply_positions.createdAt, ply_positions.updatedAt, product_categories.productCatName FROM ply_positions JOIN product_category_ply_position AS product_category_ply_position_1 ON ply_positions.plyPositionId = product_category_ply_position_1.plyPositionId JOIN product_categories ON product_categories.productCatId = product_category_ply_position_1.productCatId

Here are the code for models and Resources on Gist :- https://gist.github.com/himadriganguly/c0c9d7e247c286e497998dbd33ce7e84

OUTPUT:

[ { "product_category": [ 1 ], "plyPositionId": 2, "updatedAt": null, "product_category_associations": [ { "productCatId": 1, "plyPositionId": 2 } ], "createdAt": 1234, "plyPositionName": "Flute" }, { "product_category": [ 1 ], "plyPositionId": 1, "updatedAt": null, "product_category_associations": [ { "productCatId": 1, "plyPositionId": 1 } ], "createdAt": 123, "plyPositionName": "Top" } ]

Getting the output of the association table but not the column values of from product_categories table.

Also while creating a relationship we use lazy two times but why we are doing this is not clear to me.

ply_position = db.relationship('PlyPositionModel', secondary="product_category_ply_position", lazy=True, backref=db.backref('product_category', lazy=True))

Thank you all in advance.

Upvotes: 0

Views: 580

Answers (1)

Himadri Ganguly
Himadri Ganguly

Reputation: 743

I think I have figured it out it is not the issue of SqlAlchemy it is about how you serialize the data. In this case, I am using flask-marshmallow.

So in plyPositionResource.py I have to change the schema a bit

ProductCategorySchema

class ProductCategorySchema(ma.ModelSchema):
    class Meta:
        model = ProductCategoryModel

PlyPositionSchema

class PlyPositionSchema(ma.ModelSchema):
    productcategories = fields.List(fields.Nested(ProductCategorySchema))
    class Meta:
        model = PlyPositionModel

And after that in get function you can do the following

def get(self):
        plyPositionResource = PlyPositionModel.query.order_by(\
            PlyPositionModel.plyPositionId.desc())\
            .all()

        plyPositions = plypositions_schema.dump(plyPositionResource).data

        return({"data": plyPositions}, 200)

Hope this will help others.

If there is another way to solve the problem please feel free to comment.

Upvotes: 0

Related Questions