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

Using Django Using the ORM
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   |

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}] |

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

2

This appears to be a copy of How to use prefetch_related to retrieve multiple rows similar to SQL result

Please see FAQ - Django Forum. Specifically:

If you have a question, please post it once and don’t make multiple posts in multiple categories to try and get attention. Sometimes it takes a while for the right person to be able to see your post and reply to it!

If you have additional information or clarification to make on the original post, please do so by editing that post or adding a comment.