# Custom Query Expression when Prefetching

**URL:** <https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183>\
**Category:** Using the ORM\
**Created:** [April 3, 2025, 5:38pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183 "2025-04-03T17:38:41Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![xmatos-so](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/xmatos-so/32/16349_2.png) [@xmatos-so](https://forum.djangoproject.com/u/xmatos-so)\
**Post date:** [April 3, 2025, 5:38pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/1 "2025-04-03T17:38:41Z")

</div>

Greeting! Let’s say I have the following code:

```auto
class ModelA(models.Model):
    parent = models.ForeignKey("ModelA")

class ModelB(models.Model):
    model_a = models.ForeignKey(ModelA)

qs_b = ModelB.objects.all()
qs_a = ModelA.objects.prefetch_related(Prefetch("modelb_set", queryset=qs_b))

```

But I’d like to prefetch the related queryset based on both the `pk` and the `parent` fields. That’s not possible, right? Is there a way to customize how the relationship between objects are built besides `ForeignKey.to_field`, which doesn’t seem to support a list of multiple fields (`["pk", "parent"]`), or even better, a queryset expression like `Q("pk") | Q("parent")`?

Thank you!

---

<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:** [April 4, 2025, 12:13pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/2 "2025-04-04T12:13:46Z")

</div>

First, to be honest, I’m not entirely sure I understand what your underlying objective is here.

> [@xmatos-so](#):
>
> Is there a way to customize how the relationship between objects are built besides `ForeignKey.to_field`, … or even better, a queryset expression

At the database level, no. At this time, Django does not support composite primary keys.

> [@xmatos-so](#):
>
> `qs_a = ModelA.objects.prefetch_related(Prefetch("modelb_set", queryset=qs_b))`

For the “base case”, there’s no need to use a Prefetch object here. The clause `prefetch_related('modelb_set')` has the same functionality.

You can use a filtered expression as `qs_b` to further restrict what related objects are prefetched.

But remember that `prefetch_related` does _not_ define a relationship, it is a performance enhancement that uses an existing relationship to “pre-retrieve” related objects to avoid an `N + 1` query situation.

---

<div class="post-metadata">

**Author:** ![xmatos-so](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/xmatos-so/32/16349_2.png) [@xmatos-so](https://forum.djangoproject.com/u/xmatos-so)\
**Post date:** [April 4, 2025, 1:19pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/3 "2025-04-04T13:19:32Z")

</div>

Thank you for your response. What I need to do is customize how the related objects are fetched, which by default is done by the pk, but can be customized by setting `ForeignKey.to_field`. In my case, I wanted to filter by 2 different fields, which doesn’t necessarily mean a composite primary key. It would be equivalent to the following SQL query:

```auto

select * from model_b b inner join model_a a on (a.id = b.model_a_id) 
where a.id = 3 or a.parent_id = 1;

```

---

<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:** [April 4, 2025, 1:31pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/4 "2025-04-04T13:31:17Z")

</div>

Ok, so you’re trying to write a query on ModelB

That would be something like:  
`ModelB.objects.filter(Q(model_a_pk = 3) | Q(model_a__parent_pk=1))`

---

<div class="post-metadata">

**Author:** ![jacobtylerwalls](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/jacobtylerwalls/32/30603_2.png) [@jacobtylerwalls](https://forum.djangoproject.com/u/jacobtylerwalls)\
**Post date:** [April 4, 2025, 1:31pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/5 "2025-04-04T13:31:50Z")

</div>

Right, you should pass a more specific queryset to the `queryset=` arg than just `ModelB.objects.all()`, more like the queryset Ken just posted.

---

<div class="post-metadata">

**Author:** ![jacobtylerwalls](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/jacobtylerwalls/32/30603_2.png) [@jacobtylerwalls](https://forum.djangoproject.com/u/jacobtylerwalls)\
**Post date:** [April 4, 2025, 1:34pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/6 "2025-04-04T13:34:33Z")

</div>

And then it becomes especially important to use the `to_attr` argument to clarify that you have fetched only a subset and don’t mistake it for the entire set later.

---

<div class="post-metadata">

**Author:** ![xmatos-so](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/xmatos-so/32/16349_2.png) [@xmatos-so](https://forum.djangoproject.com/u/xmatos-so)\
**Post date:** [April 4, 2025, 2:35pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/7 "2025-04-04T14:35:00Z")

</div>

Those IDs are not known ahead of time. They are passed from the main queryset manager to the related manager. And that’s the thing. I was wondering if there was a way to customize how the relationship is made, besides the pk or `ForeignKey.to_field`. In this case, I don’t think this condition should be defined in the `ForeignKey`, that’s why I gave an example using a `Prefetch` object, as it could have an extra parameter for declaring a custom attribute to be used for that relationship, which could be a single value, a list, or a queryset expression.

---

<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:** [April 4, 2025, 2:46pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/8 "2025-04-04T14:46:51Z")

</div>

> [@xmatos-so](#):
>
> Those IDs are not known ahead of time.

But they need to be known at the time the query is issued - so where are they coming from?

> [@xmatos-so](#):
>
> that’s why I gave an example using a `Prefetch` object, as it could have an extra parameter for declaring a custom attribute to be used for that relationship, which could be a single value, a list, or a queryset expression.

But that’s not the purpose or functionality of the `Prefetch` object within the `prefetch_related` clause.

I think it would be helpful if you provided a more complete description of the situation, including the actual models being used, the view that you’re working in, and the results you’re trying to achieve.

---

<div class="post-metadata">

**Author:** ![xmatos-so](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/xmatos-so/32/16349_2.png) [@xmatos-so](https://forum.djangoproject.com/u/xmatos-so)\
**Post date:** [April 11, 2025, 3:59pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/9 "2025-04-11T15:59:29Z")

</div>

Sorry for the late response. This a simple example:

```auto
class Report(models.Model):
    parent_report = models.ForeignKey("Report")

class ReportLog(models.Model):
    report = models.ForeignKey(Report)

report_id = 1
report_logs = ReportLog.objects.filter(Q(report_id=report_id) | Q(report__parent_report=report_id))
reports = Report.objects.prefetch_related(Prefetch("reportlog_set", report_logs))

```

There’s a `Report` model with a `parent_report` foreign-key field to itself, and a `ReportLog` model with a `report` foreign-key field to `Report`.

I’d like to fetch the related log objects based on both the report\_id and the parent\_report\_id. Doing the above queries won’t work, as the SQL for the related objects will have an extra where clause for the primary key, like this:

```auto
SELECT *
FROM "reportlog"
INNER JOIN "report" ON ("reportlog"."report_id" = "report"."id")
WHERE ("reportlog"."report_id" = 1 OR "report"."parent_report_id" = 1)
AND "reportlog"."report_id" IN (...)

```

The issue is the `AND` clause.

Back to my original question, is there a way to customize how the relationship between objects are built besides `ForeignKey.to_field`, which doesn’t seem to support a list of multiple fields (`["pk", "parent"]` ), or even better, a queryset expression like `Q("pk") | Q("parent")`?

At this point, I’m sure there isn’t a way of doing it and that’s why I suggested that maybe the `Prefetch` object could have an extra param to override the default field being used to build the relationship between the models.

---

<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:** [April 11, 2025, 4:33pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/10 "2025-04-11T16:33:46Z")

</div>

First, to repeat an ealier point, the Prefetch does not establish a relationship between two models. It is used to refine or enhance an existing relationship, to further restrict what elements are retrieved.

Unfortunately, this current description doesn’t help me understand the situation any more than what you’ve posted previously.

You’re posting what isn’t working, but still haven’t posted a complete sample of what it is your final objectives are.

I’m getting the impression (admittedly possibly mistaken) that you’re looking at this solely from a “database query” perspective, and not from an “ORM” perspective - where the query is just a _part_ of the overall goal.

I think you should take a step back and look at this from the more holistic perspective. What is the input to this view? What is the output?

For me to be able to provide you with any tangible suggestions, I’m going to need to see the full context of what you’re trying to achieve, and not a limited subset of a view’s functionality described by a single query.

---

<div class="post-metadata">

**Author:** ![jacobtylerwalls](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/jacobtylerwalls/32/30603_2.png) [@jacobtylerwalls](https://forum.djangoproject.com/u/jacobtylerwalls)\
**Post date:** [April 12, 2025, 1:35pm UTC](https://forum.djangoproject.com/t/custom-query-expression-when-prefetching/40183/11 "2025-04-12T13:35:32Z")

</div>

> Back to my original question, is there a way to customize how the relationship between objects are built besides `ForeignKey.to_field`

The query represented by `prefetch_related()` is issued _after_ one side has already been fetched from the database. Django already has all the instances; the primary keys are known; so there’s no reason to reference them by anything else.

> the SQL for the related objects will have an extra where clause for the primary key… The issue is the `AND` clause.

Assuming `prefetch_related` is even the tool to use here, versus just writing a query that begins from ReportLog directly, it sounds like you want to filter the `Report` instances further _before_ you attempt to `prefetch_related` anything for them:

```auto
Report
.objects
.filter(Q(id=report_id) | Q(parent_report=report_id))
.prefetch_related("reportlog_set')

```