How to use prefetch_related to retrieve multiple rows similar to SQL result

Using Django Forms & APIs
1

I’ve a question about the usage of prefetch_related. Based on my understanding I need to use prefetch_related for reverse foreign key relationships

As an example I have a User(id, name) model and SchoolHistory(id, start_date, school_name, user_id[FK user.id]) model. A user can have multiple school history records.

If I’m querying the database using the following SQL query:

SELECT
  user.id,
  name,
  start_date,
  school_name
FROM user
INNER JOIN school_history ON school_history.user_id = user.id

the expected result would be:

| User ID      | Name   | Start Date    | School      |
| 1            | Human  | 1/1/2022      | Michigan    |
| 1            | Human  | 1/1/2021      | Wisconsin   |
| 2            | Alien  |               |             |

This is the current result that I’m getting instead with ORM and a serializer:

| User ID      | Name   | school_history                                                                                                                      
| 1            | Human  | [{start_date:1/1/2022 , school:Michigan}, {start_date:1/1/2021 , school:Wisconsin}] |
| 2            | Alien  |  []                           |

This is the ORM query that I’m using:

User.objects.prefetch_related(
  Prefetch(
    ‘school_history’
    query_set=SchoolHistory.objects.order_by(‘start_date’)
  )
)

Is there a way for the ORM query to have a similar result as SQL? I want multiple rows if there are multiple schools associated with that user

I’m aware I could start from the SchoolHistory and use select_related to get the user information, but it would not show users who have no school history

2

That is not correct. Using prefetch_related only serves to reduce the number of queries required when following a reverse relationship for multiple objects.

This is an issue with the serializer and not the ORM.

You can create a custom serializer to produce these results in the format desired.