CODEWITHSUNDEEP
pythonflasksqlalchemyjinja2
Andrés Garita
Andrés Garita

Reputation: 17

How to show a db.session.query result in jinja2 in Flask

I have this:

class User(db.Model):
    __tablename__ = 'User'
    user_id = db.Column(db.Integer, primary_key=True)
    user_name = db.Column(db.String(100), unique=True)
    user_password = db.Column(db.String(100), nullable=False)
    companies = db.relationship('Company', backref="User", lazy='dynamic')

class Company(db.Model):
    __tablename__ = 'Company'
    company_id = db.Column(db.Integer, primary_key=True)
    user_id = db.Column(db.Integer, db.ForeignKey('User.user_id'))
    company_name = db.Column(db.String(100), unique=True)
    branches = db.relationship('Branch', backref="Company", lazy='dynamic')

class Branch(db.Model):
    __tablename__ = 'Branch'
    branch_id = db.Column(db.Integer, primary_key=True)
    company_id = db.Column(db.Integer, db.ForeignKey('Company.company_id'))
    branch_name = db.Column(db.String(100), unique=True)
    secondary_users = db.relationship('Secondary_User', backref="Branch", lazy='dynamic')

Now, I want a list of branches of a specific user

@app.route('/admin', methods=['GET', 'POST'])
@login_required
def admin():
    current_user_id = request.args.get('user_id')
    current_user_branches = db.session.query(User, Company, Branch).filter(Branch.company_id == Company.company_id).filter(Company.user_id == User.user_id).filter(User.user_id == current_user_id).all()
    return render_template('admin_branch.html', branches_list=current_user_branches)

My problem is that I can't show that result in admin_branch.html:

<thead>
<tr>
    <th>Branch Name</th>
    <th>Branch ID</th>
</tr>


<tbody>
  {% if branches_list is not none %}
      {% for b in branches_list %}
          <tr><td>{{ b.branch_name }}</td><td>{{ b.branch_id }}</td></tr>
      {% endfor %}
   {% else %}
      <tr><td>No hay sucursales registradas</td></tr>
   {% endif %}

But this is the content in the two columns:

(<Company u'MyCompanyTest'>, <User u'admin'>, <models.Branch object at 0x7f8fe5901390>)

I don't know how to process that content. Please helpe me and thanks.

Upvotes: 0

Views: 1888

Answers (1)

Joran Beasley
Joran Beasley

Reputation: 113998

{% for company,user,branch in branches_list %}
      <tr><td>{{ branch.branch_name }}</td><td>{{ branch.branch_id }}</td></tr>
{% endfor %}

I think is what you want ... or if you dont want to change your template then change it so you are just passing the branches

current_user_branches = [item[-1] for item in db.session.query(User, Company, Branch).filter(Branch.company_id == Company.company_id).filter(Company.user_id == User.user_id).filter(User.user_id == current_user_id).all()]

or better yet make a proper query

my_company_ids = [company.id for company in current_user.companies.all()]
db.session.query(Branch).where(Branch.company_id._in(my_company_ids))

I think thats right at least...

you could also build it from the current user

user_branches = set([branch for company in current_user.companies.all() for branch in company.Branches.all()])

I would also encourage you to choose a naming convention and stick with it (maybe use a service like codacy that will nag you about poorly named variables)

Upvotes: 2

Related Questions