Date ranges prefetch gives multiple entries instead of 1

Question

The following returns 3 objects but this should be only 1. Since there is only 1 InsiderTrading object that has these filters, but there are 3 owners.

quarter_trading_2018q1 = InsiderTrading.objects.filter(
    issuer=company_issuer.pk,
    owners__company=company.pk,
    transaction_date__range=["2018-01-01", "2018-03-30"]
).prefetch_related('owners')

If I however remove the owner_company filter it return 1 (correct behaviour)

quarter_trading_2018q1 = InsiderTrading.objects.filter(
    issuer=company_issuer.pk,
    transaction_date__range=["2018-01-01", "2018-03-30"]
).prefetch_related('owners')

But I still want to filter on owners_company, how do I get 1 returned then?

Can you post the model structure? – Ahmed Hosny Dec 20 '18 at 11:08 — Ahmed Hosny, Dec 20 '18 at 11:08

score 1 · Accepted Answer · edited Dec 20 '18 at 11:22

1

You should add a distinct().

InsiderTrading.objects.filter(
    issuer=company_issuer.pk,
    owners__company=company.pk,
    transaction_date__range=["2018-01-01", "2018-03-30"]
).distinct().prefetch_related('owners')

edited Dec 20 '18 at 11:22

Sharpless512

3,062
5
35
60

answered Dec 20 '18 at 11:04

Daniel Roseman

588,541
66
880
895

score 0 · Answer 2 · answered Dec 20 '18 at 11:16

If distinct works: this means that this query results with

InsiderTrading.objects.filter(
    issuer=company_issuer.pk,
    owners__company=company.pk,
    transaction_date__range=["2018-01-01", "2018-03-30"]
)

with multiple results not one.

Django docs states

select_related() "follows" foreign-key relationships, selecting additional related-object data when it executes its query.

prefetch_related() does a separate lookup for each relationship, and does the "joining" in Python.

I'd say use select_related instead. check https://stackoverflow.com/a/31237071/4117381

Another solution is to use group_by owner_id.

Date ranges prefetch gives multiple entries instead of 1

2 Answers2