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.