CODEWITHSUNDEEP
pythonflasksqlalchemyflask-sqlalchemy
Elizabeth
Elizabeth

Reputation: 15

querying the foreign key of a sqlalchemy model

I have ben trying to figure out how to run queries based on the foreign key of a SQLAlchemy model for a while and I've tried checking the documentation and looking here on stackoverflow but I can't find anything that addresses this problem I'm having. I have 2 SQLAlchemy models: User and Account defined as below:

class User(db.Model):
  __tablename__ = 'user'

  id = db.Column(db.Integer, primary_key=True)
  fName = db.Column(db.String(255))
  accounts = db.relationship("Account")

class Account(db.Model):
  __tablename__ = 'account'

  id = db.Column(db.Integer, primary_key=True)
  account_number = db.Column(db.Integer, unique=True, nullable=False)
  lName = db.Column(db.String(255), nullable=False)
  client_id = db.Column(db.ForeignKey("user.id"))

What I want to do is query all of the accounts in the Account model and for each one, if it has a corresponding value in in the User model column that the client_id foreign key references, then add the value of fName from the User model in the final result. So it will look something like this:

{
  "accounts": [
    {
      "fName": "Abbie",
      "id": 1,
      "account_number": 1234,
      "lName": "Mills"
    },
    {
      "fName": "Katrina",
      "id": 2,
      "account_number": 3848,
      "lName": "Crane"
    },
    {
      "fName": "Sheldon",
      "id": 3,
      "account_number": 384,
      "lName": "Cooper"
    },
    {
      "fName": "Chandler",
      "id": 4,
      "account_number": 1288,
      "lName": "Bing"
    },
    {
      "id": 5,
      "account_number": 1283,
      "lName": "Smith"
    },
    {
      "id": 6,
      "account_number": 3847,
      "lName": "Gary"
    },
    {
      "id": 7,
      "account_number": 2038,
      "lName": "Ryan"
    }
  ]
}

I know this is the query I want:

class AccountList(Resource):
    @account_ns.doc('get account data')
    def get(self):
        query = account_schema.dump(Account.query.all())
        accountDict = {}
        accountDict['accounts'] = []
        for item in query:
            itemDict = {}
            itemDict = item
            linkQuery = user_schema.dump(db.session.query(User.fName).filter(User.id == Account.client_id).first())
            # print(linkQuery)
            itemDict.update(linkQuery)
            accountDict['accounts'].append(itemDict)
        print(accountDict)
     return accountDict, 200

but everytime the query db.session.query(User.fName).filter(User.id == Account.client_id).first() is run, the id of User doesn't seem to be incrementing and always prints the same value everytime, even for account objects that don't have a client_id. the result turns out to be this:

{
  "accounts": [
    {
      "customer_id": 1,
      "id": 1,
      "lName": "Mills",
      "account_number": 1234,
      "fName": "Abbie"
    },
    {
      "customer_id": null,
      "id": 2,
      "lName": "Smith",
      "account_number": 1283,
      "fName": "Abbie"
    },
    {
      "customer_id": null,
      "id": 3,
      "lName": "Gary",
      "account_number": 484,
      "fName": "Abbie"
    },
    {
      "customer_id": 2,
      "id": 4,
      "lName": "Crane",
      "account_number": 3848,
      "fName": "Abbie"
    },
    {
      "customer_id": 3,
      "id": 5,
      "lName": "Cooper",
      "account_number": 384,
      "fName": "Abbie"
    },
    {
      "customer_id": null,
      "id": 6,
      "lName": "Ryan",
      "account_number": 223,
      "fName": "Abbie"
    },
    {
      "customer_id": 1,
      "id": 7,
      "lName": "Mills",
      "account_number": 48494,
      "fName": "Abbie"
    },
    {
      "customer_id": 4,
      "id": 9,
      "lName": "Bing",
      "account_number": 1288,
      "fName": "Abbie"
    }
  ]
}

which is not what I want. Is there anything I'm doing wrong here or someway I can tweak my query? The only other way I've thought of doing is creating another column in Account that has the same values as client_id but is not set as the foreign key so that I can actually reference the value for the row in that column.

I've been stuck on this for a while so any help will be greatly appreciated. Thank you!

Upvotes: 1

Views: 3848

Answers (1)

Hashir Irfan
Hashir Irfan

Reputation: 334

Sqlalchemy supports backref in model. When applying relationship you can also use pass backref parameter, it will help you to access user object directly from account object.

class User(db.Model):
  __tablename__ = 'user'

  id = db.Column(db.Integer, primary_key=True)
  fName = db.Column(db.String(255))
  accounts = db.relationship("Account", backref="user")

class Account(db.Model):
  __tablename__ = 'account'

  id = db.Column(db.Integer, primary_key=True)
  account_number = db.Column(db.Integer, unique=True, nullable=False)
  lName = db.Column(db.String(255), nullable=False)
  client_id = db.Column(db.ForeignKey("user.id"))

class AccountList(Resource):
    @account_ns.doc('get account data')
    def get(self):
        query = Account.query.all()
        account_list = []
        for item in query:
            account_dict = item.__dict__
            if item.client_id:
                accout_dict["fName"] = item.user.fName
            account_list.append(account_dict)
     return account_list, 200

Upvotes: 2

Related Questions