I'm currently building an API (using SQLAlchemy, FastAPI and Pydantic) that stores information about apartments and it's residents. An apartment can only be taken by one resident at a time, but we store the history. The data model is as follows:
class Resident(Base):
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True, nullable=False)
name = Column(String(200))
class Apartment(Base):
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True, nullable=False)
address = Column(String(200))
residents = relationship("Occupation")
class Occupation(Base):
id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4, unique=True, nullable=False)
start = Column(Date)
end = Column(Date)
resident_id = Column(UUID(as_uuid=True), ForeignKey("residents.id"))
resident = relationship("Resident")
apartment_id = Column(UUID(as_uuid=True), ForeignKey("apartments.id"))
apartment = relationship("Apartment")
The 'residents' relation on apartment will give me a 1-N relation to occupation. But what I would like to have, is a relation that gives me the current resident for a given apartment in a 1-1 way.
The following query is used to get all apartments and owners:
db.query(models.Apartment).options(
joinedload(models.Apartment.residents).joinedload(models.Occupation.resident),
).all()
Using a Pydantic schema, this translates into the following JSON:
[
{
"id": "4c260b11-0b93-4e68-a931-5facfb6bac52",
"address": "X",
"residents": [
{
"start": "2021-01-01",
"end": "2021-12-31",
"resident": {
"id": "982f6398-5213-4b07-8187-2d5606ee4142",
"name": "Steve Jobs"
}
}
]
}
]
But what I'm looking for, is:
[
{
"id": "4c260b11-0b93-4e68-a931-5facfb6bac52",
"address": "X",
"current_resident": {
"id": "982f6398-5213-4b07-8187-2d5606ee4142",
"name": "Steve Jobs"
}
}
]
Is there a SQLAlchemy way to achieve this?
from SQLAlchemy - Latest of many to many relationship
No comments:
Post a Comment