I have two tables: FundingSource() and AllocationSummary(). I'm using a foreign key since there's a one to many relationship between the two tables - a funding source can have many allocations, but an allocation can only have one funding source.

These are my two tables:

class FundingSource(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    complete = db.Column(db.String(10), default=False, nullable=False)
    department = db.Column(db.String(100), nullable=False)
    agency = db.Column(db.String(150), nullable=False)
    funding_source = db.Column(db.String(200), nullable=False)
    bill = db.Column(db.String(10), nullable=False)
    allocations = db.relationship('AllocationSummary', backref='allocation', lazy=True) 


class AllocationSummary(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    state = db.Column(db.String(100), nullable=False)
    eligible_applicant = db.Column(db.String(100), nullable=False)
    recipient = db.Column(db.String(200), nullable=False)
    amount = db.Column(db.Float(), nullable=False)
    funding_source_id = db.Column(db.Integer, db.ForeignKey('funding_source.id'), nullable=False)

I have a webpage Allocation Summary with a <table> as so:

My problem is, how do I call the id, agency, and funding_source attributes from the FundingSource() table using the foreign key? I need to get/query the values for the "Agency" and "Fund" column, as shown in the picture above, using those attributes from that table, and get the rest of the column values using the attributes from the AllocationSummary() table.

Here is my .py file:

@main.route("/allocationSummary")
def alloc_summ():
    all_data2 = AllocationSummary.query.filter(AllocationSummary.funding_source_id == FundingSource.id).all()
    return render_template('allocationSummary.html', title='Allocation Summary', allocs=all_data2)

Here is my HTML file:

                    ....
                    <tbody>

                    {% for row in allocs%}
                    <tr>
                        <td>{{ row.id }}</td>
                        <td>{{ row.complete }}</td>
                        <td>{{ row.agency }}</td>
                        <td>{{ row.funding_source }}</td>
                        <td>{{ row.bill }}</td>
                        <td>{{ row.state }}</td>
                        <td>{{ row.eligible_applicant }}</td>
                        <td>{{ row.recipient }}</td>
                        <td>{{ row.amount }}</td>
                    </tr>

                    {% endfor %}

                    </tbody>
                    ....

I just can't seem to find a way to query using attributes from 2 different tables at once to display in my table. Any help would be greatly appreciated!

CodePudding user response：

You have defined a backref in the db.relationship in the FundingSource but instead of calling it allocation i would call it foundingsource because this will be the member on the Allocation. With that member you can access the Data of the associate FoundingSource like this:

 ....
                    <tbody>

                    {% for row in allocs%}
                    <tr>
                        <td>{{ row.id }}</td>
                        <td>{{ row.foundingsource.complete }}</td>
                        <td>{{ row.foundingsource.agency }}</td>
                        <td>{{ row.foundingsource.funding_source }}</td>
                        <td>{{ row.foundingsource.bill }}</td>
                        <td>{{ row.state }}</td>
                        <td>{{ row.eligible_applicant }}</td>
                        <td>{{ row.recipient }}</td>
                        <td>{{ row.amount }}</td>
                    </tr>

                    {% endfor %}

                    </tbody>
                    ....

This would be the definition of the relationship:

    allocations = db.relationship('AllocationSummary', backref='foundingsource', lazy=True) 

Because the backref is the name of the Member which will be added to the corresponding class of the relationship.

One To Many SQLAlchemy