How to access all the child table entries based on parent table entry in Flask-SQLAlchemy-CodePudding

I am developing a flask-based website, I have two tables in the database, Customer(PK=sno, ...) and Item(PK=iid, ..., FK=customer.sno). I want to display all the Items corresponding to any customer at a time when I click Bill button corresponding to the customer.

File app.py contains code

@app.route("/show_bill/<int:sno>")
def show_bill(sno):
    customer = Customer.query.filter_by(sno=sno).first()
    show_bill = Item.query.filter_by(cid=sno)
    return render_template('show_bill.html', customer=customer, show_bill=show_bill)

And, show_bill.html contains

<div >
    <h2>Bill for customer {{ customer.cname }}</h2>
        {% if show_bill|length == 0 %}
            <div  role="alert">
                No Item found. Add first Item now to appear for this customesr!
            </div>
        {% else %} 
            <table >
                <thead>
                    <tr>
                    <th scope="col">SNo</th>
                    <th scope="col">Customer ID</th>
                    <th scope="col">Name of Item</th>
                    <th scope="col">No. of Items</th>
                    <th scope="col">Rate</th>
                    <th scope="col">Discount(%)</th>
                    <th scope="col">Time</th>
                    <th scope="col">Actions(Edit)</th>
                    </tr>
                </thead>
                        
                <tbody>
                {% for item in show_bill %}
                <tr>
                    <th scope="row">{{loop.index}}</th>
                    <td>{{item.cid}}</td>
                    <td>{{item.iname}}</td>
                    <td>{{item.icount}}</td>
                    <td>{{item.irate}}</td>
                    <td>{{item.idiscount}}</td>
                    <td>{{item.date_created}}</td>
                    <td>
                        <a href="/update_item/{{item.iid}}" type="button" >Update</button>
                        <a href="/delete_item/{{item.iid}}" type="button" >Delete</button>
                    </td>
                </tr>
                {% endfor %}
                </tbody>
            </table>
        {% endif %}
</div>

But, on clicking Bill button, I get an error: TypeError: object of type 'BaseQuery' has no len() Also if I remove show_bill=show_bill, It renders to show_bill.html page but do not show data as we are not fetching any (Probably the problem is here).
Can someone help to resolve this? Or any alternative way to get the same thing.

Thanks in Advance.

CodePudding user response：

You need to add the .all() query to return a list of all the desired items in your table, like this:

show_bill = Item.query.filter_by(cid=sno).all()

Without this part, SQL returns your query as an object instead of an iterable list.