CODEWITHSUNDEEP
python-3.xflaskflask-sqlalchemy
user3324136
user3324136

Reputation: 417

SQLAlchemy query parent to find all children

I have a database where I have a one-to-many relationship from the parent(company) to the children(buyers). I would like to iterate through each company to list all of the buyers in that company and list them in my html page.

My initial try was to try companies = Company.query.all() and then access this using something like `companies.buyer.firstname, but this give an error

'list' object has no attribute 'buyer'

Any help is greatly appreciate. Thank you!

view.py

class ListCompanies(MethodView):
    decorators = [login_required]
    template_file = 'dashboard.html'

    def get(self):
        companies = Company.query.all()
        print(f'buyer is {companies.buyer.firstname}')
        return render_template(self.template_file, companies=companies)

Models.py

class Company(db.Model, UserMixin):

    __tablename__ = 'company'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True, nullable=False)
    company = db.Column(db.String(64), index=True, nullable=False)
    company_url = db.Column(db.String(64), index=True, nullable=False)
    # Child relationship to the Event
    event_id = db.Column(db.Integer, db.ForeignKey('events.id'))
    password_hash = db.Column(db.String(128))
    # Company is parent to the buyer
    buyer = db.relationship('Buyers', backref='buyer', lazy='dynamic')

    def __init__(
        self, email, company, company_url, event_id, password
    ):
        self.email = email
        self.company = company
        self.company_url = company_url
        self.event_id = event_id

        self.password_hash = generate_password_hash(password)
        # self.user_type = user_type

    def check_password(self, password):
        return check_password_hash(self.password_hash, password)

    def __repr__(self):
        return f"{self.company}"


class Buyers(db.Model, UserMixin):
    __tablename__ = 'buyers'

    id = db.Column(db.Integer, primary_key=True)
    email = db.Column(db.String(64), unique=True, index=True, nullable=False)
    firstname = db.Column(db.String(64), index=True, nullable=False)
    lastname = db.Column(db.String(64), index=True, nullable=False)
    # The buyer is the child to the company
    company = db.Column(db.Integer, db.ForeignKey('company.id'))
    # The buyer is the parent to the eventdetails
    details = db.relationship('Eventdetails', backref='buyer', lazy='dynamic')

    def __init__(
        self, email, firstname, lastname, company
    ):
        self.email = email
        self.firstname = firstname
        self.lastname = lastname
        self.company = company

    def __repr__(self):
        return f"Welcome, {self.firstname} {self.lastname}"

Upvotes: 1

Views: 2085

Answers (1)

Detlef
Detlef

Reputation: 8552

Company.query.all() returns a list of companies. As you said, you have to iterate through these. The same applies to the buyers if you want to get the first name.

I've reduced your example to the bare minimum to make it more understandable.

class Company(db.Model):
    __tablename__ = 'companies'

    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(64), index=True, nullable=False)

    buyers = db.relationship('Buyer', backref='company', lazy='dynamic')

class Buyer(db.Model):
    __tablename__ = 'buyers'

    id = db.Column(db.Integer, primary_key=True)
    firstname = db.Column(db.String(64), index=True, nullable=False)
    lastname = db.Column(db.String(64), index=True, nullable=False)

    company_id = db.Column(db.Integer, db.ForeignKey('companies.id'))

class ListCompanies(MethodView):
    template_file = 'dashboard.html'
    def get(self):
        companies = Company.query.all()
        return render_template(self.template_file, companies=companies)

<ul>
{% for company in companies %}
    <li>
      <span>{{ company.name }}</span>
      <ul>
      {% for buyer in company.buyers %}
        <li>{{ buyer.lastname }}, {{ buyer.firstname }}</li>
      {% endfor %}
      </ul>
    </li>
{% endfor %}
</ul>

Upvotes: 1

Related Questions