SQLAlchemy: How can I combine two different tables and sort them as a single object by dates?

Viewed 30

I have 2 different tables which are named Income and Expense. In an endpoint I have created, I want to sort the income and expenses of a user according to the payment_date.

Tool Kits: FastAPI, Pydantic, SQLAlchemy

ENDPOINT:

@router.post("/recent", status_code=status.HTTP_200_OK)
def weekly(the_date: filter_dto.DayFilter,
           db: Session = Depends(get_db),
           current_user: users_dto.UserResponse = Depends(oauth2.get_current_user)):

    all_query = db.query(incomes.Income, expenses.Expense).filter(incomes.Income.user_id == current_user.id,
                                                                  expenses.Expense.user_id == current_user.id)

    return all_query.all()

I know there is no query for ordering the result. But the problem is bellow.

MODELS:

class Expense(Base):
    __tablename__ = "expenses"

    id = Column(Integer, primary_key=True, nullable=False)
    amount = Column(Float, nullable=False)
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)
    payment_date = Column(Date)

class Income(Base):
    __tablename__ = "incomes"

    id = Column(Integer, primary_key=True, nullable=False)
    amount = Column(Float)
    payment_date = Column(Date)    
    user_id = Column(Integer, ForeignKey("users.id", ondelete="CASCADE"), nullable=False)

QUERY RESULT:

[
    {
        "Income": {
            "id": 2,
            "amount": 100,
            "user_id": 1,
            "payment_date": "2022-09-09"
        },

        "Expense": {
            "id": 1,
            "amount": 100,
            "user_id": 1,
            "payment_date": "2022-09-12"

        },
    
    
        "Income": {
            "id": 2,
            "amount": 100,
            "user_id": 1,
            "payment_date": "2022-09-09"
        },

        "Expense": {
            "id": 7,
            "amount": 1500,
            "user_id": 1,
            "payment_date": "2022-09-12"

        },
    }
   .....

PROBLEM:

As you can see above, the requests I send are returned as separate objects. They come as separate objects, one income and one expense.

WHAT I WANT TO DO IS:

What I want to do is exactly this: I want to fetch the results of two different tables in order of payment_day. I want the returned result to be Income or Expense sorted by date. As a result of the query thrown in the QUERY RESULT you see above, there are Income and Expense in a single object.

I wanna see the result as here:

[
    {
        "Income": {
            "id": 2,
            "amount": 100,
            "user_id": 1,
            "payment_date": "2022-09-22"
        },

        "Expense": {

            "id": 1,
            "amount": 100,
            "user_id": 1,
            "payment_date": "2022-09-21"
        },

        "Expense": {

            "id": 7,
            "amount": 1500,
            "user_id": 1,
            "payment_date": "2022-09-19"
        },
        .....
    },
   .....

NOTE: Please look at the payment_dates and brackets.

0 Answers
Related