Flask - How to query a one-to-many relationship to display on webpage

Question

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 query the FundingSource table to get the complete, agency, funding_source, bill attributes AND query the AllocationSummary table to get the state, eligible_applicant, recipient, amount attributes using my foreign key?

In essence, I need to join the two tables using the id and foreign key to then populate my Allocation Summary table on the webpage.

So, the first 5 columns need to be read in from FundingSource and the last 4 columns need to he read in from AllocationSummary.

Here is my .py file:

@main.route("/")
@main.route("/guidance_master")
def g_master():
    f_sources = FundingSource.query.all()
    return render_template('guidance_master.html', title='Guidance Master', fsources=f_sources)

@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. These are the 2 queries I have tried:

all_data2 = AllocationSummary.query.filter(AllocationSummary.funding_source_id == FundingSource.id).all()

all_data2 = db.session.query(FundingSource, AllocationSummary).join(AllocationSummary)\
         .filter(AllocationSummary.funding_source_id == FundingSource.id)

Any help would be greatly appreciated!

Answer 1

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