# How to use prefetch\_related to retrieve multiple rows similar to SQL result

**URL:** <https://forum.djangoproject.com/t/how-to-use-prefetch-related-to-retrieve-multiple-rows-similar-to-sql-result/16130>\
**Category:** Forms & APIs\
**Created:** [September 23, 2022, 11:02am UTC](https://forum.djangoproject.com/t/how-to-use-prefetch-related-to-retrieve-multiple-rows-similar-to-sql-result/16130 "2022-09-23T11:02:45Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![min-soh](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/min-soh/32/10165_2.png) [@min-soh](https://forum.djangoproject.com/u/min-soh)\
**Post date:** [September 23, 2022, 11:02am UTC](https://forum.djangoproject.com/t/how-to-use-prefetch-related-to-retrieve-multiple-rows-similar-to-sql-result/16130/1 "2022-09-23T11:02:45Z")

</div>

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:

```auto
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:

```auto
| 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:

```auto
| 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:

```auto
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

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [September 23, 2022, 12:52pm UTC](https://forum.djangoproject.com/t/how-to-use-prefetch-related-to-retrieve-multiple-rows-similar-to-sql-result/16130/2 "2022-09-23T12:52:04Z")

</div>

> [@min-soh](#):
>
> Based on my understanding I need to use prefetch\_related for reverse foreign key relationships

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

> [@min-soh](#):
>
> This is the current result that I’m getting instead with ORM and a serializer:

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.

---

<div class="post-metadata">

**Author:** ![akhilgargjpr](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/akhilgargjpr/32/27400_2.png) [@akhilgargjpr](https://forum.djangoproject.com/u/akhilgargjpr)\
**Post date:** [March 10, 2025, 4:01pm UTC](https://forum.djangoproject.com/t/how-to-use-prefetch-related-to-retrieve-multiple-rows-similar-to-sql-result/16130/3 "2025-03-10T16:01:46Z")

</div>

```auto
from django.db.models import Value, CharField
from itertools import chain

# Get users with school histories
users_with_schools = SchoolHistory.objects.select_related('user').annotate(
    name=F('user__name')
).values('user_id', 'name', 'start_date', 'school_name')

# Get users without school histories
users_without_schools = User.objects.exclude(
    id__in=SchoolHistory.objects.values_list('user_id', flat=True)
).annotate(
    user_id=F('id'),
    start_date=Value(None, output_field=CharField()),
    school_name=Value(None, output_field=CharField())
).values('user_id', 'name', 'start_date', 'school_name')

# Combine the results
flat_results = list(chain(users_with_schools, users_without_schools))

```