How can I join two tables with different number of rows in Flask SQLAlchemy?

I have two tables I want to join. They possess a one-to-many relationship. One is called FundingSource, and the other is called AllocationSummary. They have a different number of columns and a different number of rows (FundingSource has more of both).

I need to query the FundingSource table (Table 1) to get all the rows from the database, and then add the rows from AllocationSummary (Table 2) to it, joining each corresponding row together and leaving the remaining AllocationSummary (Table 2) column values null since there are less rows.

The resulting table needs to have 5 of the columns from my FundingSource table (including id), 4 of the columns from my AllocationSummary table, and however many rows my FundingSource table has.

For example, this is my FundingSource table with 5 rows:

+-----------+-----------+-----------+------------+-----------+-----------+
|     id    |  complete | department|  agency    | bill      | cfda_no   |
+-----------+-----------+-----------+------------+-----------+-----------+
| 1         | Y         | Dep A     | NIC        |  HR 3684  |  32.224   |
| 2         | Y         | Dep B     | AOA        |  HR 266m  |  84.225   |
| 4         | Y         | Dep C     | NSK        |  HR 3892  |  71.20    |
| 5         | N         | Dep D     | NIH        |  HR 748   |  22.865   |
| 6         | N         | Dep E     | DNS        |  HR 1319  |  93.224   |

This is my AllocationSummary table with 3 rows:

+-----------+-----------+-----------+------------+-----------+-----------+
|     id    |  state    | eligible_applicant |  recipient    | amount    | 
+-----------+-----------+-----------+------------+-----------+-----------+
| 1         | NY        | State              | NIC           |  3065.0   |  
| 2         | CA        | Locality           | AOA           |  1000.0   |  
| 3         | VA        | Institution        | NSK           |  2400.0   | 

And this is the RESULT table I want AFTER joining: (format was getting ruined so I attached a picture) Resulting Table

Also note, I want the id column to come from the FundingSource table, so joining the two tables should not create a new table. It should query from the FundingSource table and just add on the state, eligible_applicant, recipient, and amount columns to it from the AllocationSummary table.

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)
    cfda_no = db.Column(db.Float(), nullable=True)
    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)

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.join(FundingSource).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 some of the queries I have tried:

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

all_data2 = db.session.query(FundingSource).outerjoin(AllocationSummary).all()

Any help would be greatly appreciated!

Answer

You want a left outer join between FundingSource and AllocationSummary, which you can do like this:

rows = (
    db.session.query(
        FundingSource.id,
        FundingSource.complete,
        FundingSource.department,
        FundingSource.agency,
        FundingSource.bill,
        FundingSource.cfda_no,
        AllocationSummary.state,
        AllocationSummary.eligible_applicant,
        AllocationSummary.recipient,
        AllocationSummary.amount,
    )
    .join(
        AllocationSummary,
        FundingSource.id == AllocationSummary.funding_source_id,
        isouter=True,
    )
    .all()
)

The above is a bit cumbersome, but allows the HTML template to be a simple loop. The converse – simple Python but complicated template – could be achieved by passing FundingSource.query.all() to the template and handle multiple or missing allocations there.