I have three tables:

Farmer = Table(
    "farmer",
    metadata,
    Column("farmer_id", UUID(), primary_key=True, default=uuid.uuid4, unique=True, nullable=False)
)

Fruit = Table(
    "fruit",
    metadata,
    Column("fruit_id", UUID(), primary_key=True, default=uuid.uuid4, unique=True, nullable=False),
    Column("farmer_id",  UUID(), ForeignKey("farmer.farmer_id"), nullable=False)
)

Vegetable = Table(
    "vegetable",
    Column("vegetable_id", UUID(), primary_key=True, default=uuid.uuid4, unique=True, nullable=False),
    Column("farmer_id",  UUID(), ForeignKey("farmer.farmer_id"), nullable=False)
)

A Farmer can have 0-n Fruits and/or 0-n Vegetables.

Given a farmer_id I want to get the details of the farmer (there are other columns left out of the above for brevity) plus all the details of all of the fruits/vegetables they might grow, the SQL would be:

SELECT *
FROM farmer LEFT OUTER JOIN fruit ON farmer.farmer_id = fruit.farmer_id LEFT OUTER JOIN vegetable ON farmer.farmer_id = vegetable.farmer_id WHERE farmer_id = ?;

And I can replicate that with a SQLAlchemy core query like so:

query = select(
    [Farmer, Fruit, Vegetable]
).select_from(
  Farmer.join(
      Fruit, Farmer.c.farmer_id == Fruit.c.farmer_id,
      isouter=True
  ).join(
      Vegetable, Farmer.c.farmer.id == Vegetable.c.farmer_id,
      isouter=True
  )
).where(
    Farmer.c.farmer_id == ?
)

What I am unsure about is how to map the result of this query into objects. I seem to need to use a mapper and some classes but I am not sure how to do this with multiple joins.

How do I write a mapper to take the result of the above query and generate an object graph along the lines of:

class FarmerObject:
    fruits = [...]
    vegetables = [...]