Custom Query Expression when Prefetching

Using Django Using the ORM
1

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

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!

2

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

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

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.

3

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:


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;
4

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))

5

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

6

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.

1 Like
7

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.

8

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

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.

9

Sorry for the late response. This a simple example:

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:

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.

10

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.

11

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:

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