CODEWITHSUNDEEP
pythonsqlalchemy
James Huang
James Huang

Reputation: 886

How do I generate a reference to a user name given the id with sql alchemy Flask?

I have a post model like so:

class Post(db.Model):
    id = db.Column("id", db.Integer, primary_key=True)
    title = db.Column(db.String(100))
    contents = db.Column(db.String(700))
    author_id = db.Column(db.Integer)
    author_name = db.Column(db.String(100))
    time = db.Column(db.DateTime)

    def __init__(self, title, contents, author_id):
        self.title = title
        self.contents = contents
        self.author_id = author_id
        self.time = datetime.now()

My users can change their name whenever they like. I don't want to have to keep updating the author_name variable because that would be redundant. When I already have the id. How do I get it so that when I reference author_name, it just fetches me the User.query.get(author_id).name?

I don't want to simply use User.query.get(author_id).name because I have to use it in a jinja2 template. I cannot fetch things from User so I'd like it to be kept with each post.

Upvotes: 0

Views: 716

Answers (1)

steve
steve

Reputation: 2638

You probably want to start by defining a relationship between your Post class and your User class. I'm guessing a 'one-to-many' relationship where a User can have many Posts, but a post can only have 1 User (or Author). To do this redefine the author_id as a Foreign Key referencing the User class (or Author class if that's what you have), instead as just an Integer (like you currently have).

Then you can define an author attribute (not a column) to the Post class that holds a reference to the Post's author. This will allow you to get the author's name from a Post via post.author.name.

class Post(db.Model):
  author_id = db.Column(db.Integer, db.ForeignKey('User.id'), nullable=False)
  author = db.relationship(lambda: User, uselist=False)
  .
  .
  .

Now you can get the author's name with:

post.author.name

If instead you wanted to reference the author's name like this instead post.author_name you could leverage an association proxy

class Post(db.Model):
  author_id = db.Column(db.Integer, db.ForeignKey('User.id'), nullable=False)
  author = db.relationship(lambda: User, uselist=False)
  author_name = association_proxy('author', 'name')
  .
  .
  .

Now you can get the author's name with

post.author_name

Upvotes: 1

Related Questions