sqlalchemy orm selectinload not working at the get-all level

Question

This is driving me crazy. As an example, I have a many-to-many relationship between tables called labels and tasks. Take this code excerpt for a filtered record:

      statement = select(Label).filter(Label.id == label_id).options(selectinload(Label.tasks))
      result = await async_session.execute(statement)

      label = result.scalars().first()
      return label

This works great and I get a result that includes tasks like:

{
  "name": "string",
  "tasks": [
    {
      "id": "1ed56356-3f50-4630-be01-7aae9abc9f8f",
      "title": "string"
    }
  ]
}

But take this example:

      statement = 
      select(Label).order_by(Label.id).options(selectinload(Label.tasks))
      result = await async_session.execute(statement)
      labels = result.scalars()

      return labels

This gives me an output that is missing "tasks":

[
  {
    "name": "string",
  }
]

Why are there no "tasks" provided in this output? Shouldn't there be a "tasks: []" parameter like in the first output? I'm missing something dumb here I think :) Any idea what's going on?

Thanks, Jon

Answer 1

Many thanks to @GordThompson for leading me to the answer on this. The response_model for my router get-all endpoint was:

response_model=List[LabelModel],

This model didn't have a tasks parameter. Because of that it wasn't adding the tasks in the response even though my label service was returning the tasks. So I created a new model that included the tasks model and changed the response model to:

response_model=List[LabelModelWithTask],

Now the response gives me all the labels back and in each label, I have a list of tasks being returned.

Hope this can help anyone else out with this issue.