CODEWITHSUNDEEP
pythonflasksqlalchemy
Ben Shekhtman
Ben Shekhtman

Reputation: 625

return populated fields from a join table in SQLalchemy Flask

UserService is a join table connecting Users and Services tables. I have a query that returns all the tables that have a user_id = to the id passed in the route.

@bp.route('/user/<id>/services', methods=['GET'])
def get_services_from_user(id):
    user = db_session.query(User).filter(id == User.id).first()

    if not user:
        return jsonify({'Message': f'User with id: {id} does not exist', 'Status': 404})

    user_services = db_session.query(UserService).filter(user.id == UserService.user_id).all()
    result = user_services_schema.dump(user_services)

    for service in result:
        user_service = db_session.query(Service).filter(service['service_id'] == Service.id).all()
        result = services_schema.dump(user_service)
        return jsonify(result)

result holds a list that looks as such:

[
    {
        "id": 1,
        "service_id": 1,
        "user_id": 1
    },
    {
        "id": 2,
        "service_id": 2,
        "user_id": 1
    }
]

how could I then continue this query or add another query to get all the actual populated services (Service class) instead of just the service_id and return all of them in a list? The for loop is my attempt at that but currently failing. I am only getting back a list with one populated service, and not the second.

Upvotes: 0

Views: 397

Answers (1)

Esteban Lopez Ramirez
Esteban Lopez Ramirez

Reputation: 76

You could try something like this:

userServies = db_session.query(Users, Services).filter(Users.id == Services.id).all()

userServices would be an iterable. You should use: for value, index in userServices: to iterate through it. (Could be index, value I'm not 100% sure of the order)

There is another way using .join() and adding the columns that you need with .add_columns().
There is also another way using

db_session.query(Users.id, Services.id, ... 'all the columns that you need' ...).filter(Users.id == Services.id).all()

Upvotes: 1

Related Questions