Iterating data from Joined Tables using Jinja2 Nested For Loops

user_9170226 0
Viewed 1565

For the purpose of this example consider the following table which has a parent row iterating a list of "orders" and each "order" has several child rows iterating a list of "items" for a given "order".

The list of "orders" and "items" are contained in separate tables joined together when we render our template with a query:

def order_items_template():
    return render_template('order_items_template.html', 
      order_items_query = db.session.query(orders)
      .join(items, orders.id == items.order_id)
      .all())

Note below that we are also using a bootstrap collapsible accordion to display parent/child rows.

{% for order in orders %} 

    <tr data-toggle="collapse" data-target="#{{ order.number }}" class="clickable">
        <td>{{ order.number }}</td>        
    </tr>

        {% for item in order %}
            <tr class="no-border collapse" id="{{ order.number }}">
                <td>{{ item.name }}</td>                                          
            </tr>
        {% endfor %} 

{% endfor %}

Additionally we are joining the two tables with an sqlalchemy ForeignKey relationship:

class items (db.Model):
   id = db.Column('id', db.Integer, primary_key = True)
   item = db.Column(db.String(100)) 
   order_id = db.Column(db.Integer)

class orders (db.Model):
   id = db.Column('id', db.Integer, primary_key = True)
   orders = db.Column(db.String(100)) 
   item_id = db.Column(db.Integer, db.ForeignKey('items.order_id'), nullable=False)
   item_relashionship = relationship("items")

Although the parent rows are displayed the data in the rows is not. Does anyone see where the problem lies?

edit: by changing the "child" jinja2 loop from {{ % for item in order % }} to {{ % for item in order.items % }} as suggested below no data is returned and the bootstrap accordion is "broken" and no longer collapses.

For testing purposes, adding {{ order.item_relashionship.name }} placeholder to the "parent loop" returns the name of an item matching an order_id which is obviously wrong.

What we want is the corresponding list of items for each order. This must have something to do with the relationship between the two tables but I can't figure out what is missing.

flask-sqlalchemy flask jinja2 python-3.x python
2 Answers

When you query the result and passing the data to html try adding a temporary column with the order number details so that you can iterate thru order and show it in the screen. I had faced a similar requirement before and achieved it by below method.

queryset1= table1.objects.all().annotate(order=Value('value1', CharField()))
queryset2= table2.objects.all().annotate(order=Value('value2', CharField()))
all_items = list(queryset1) + list(queryset2) 
all_items_feed = sorted(all_items, key=lambda obj: obj.type)
return render(request, 'page.html', {'all_items_feed' : all_items_feed})

and in the html page iterate thru the all_items_feed by checking the condition as below

{% if all_items_feed %}
    {% for items in all_items_feed %}
        {% if items.order == 'value1' %}
        populate the html collapse content for every order here
user_81349360

A simple guess, but I'm thinking you need to something along the lines of this:

{% for item in order.items %} <-- Note the change in order to order.items
    <tr class="no-border collapse" id="{{ order.number }}">
        <td>{{ item.name}}</td>                                          
    </tr>
{% endfor %}

You need to iterate through the objects items for your sub data

user_3373150
Related