# what is the best way of querying 300,000 members and each member with different purchase periods?

**URL:** <https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946>\
**Category:** Using the ORM\
**Created:** [July 13, 2024, 8:59am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946 "2024-07-13T08:59:27Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![kelvinho8](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kelvinho8/32/2853_2.png) [@kelvinho8](https://forum.djangoproject.com/u/kelvinho8)\
**Post date:** [July 13, 2024, 8:59am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/1 "2024-07-13T08:59:27Z")

</div>

Hi everyone,

I have a Sale model table (a legacy table that I only have read permission) that keeps all the sale records. If I need to make a query that aggregate 300,000 members their last 6 months purchase total amount (last 6 months meaning that “purchasedate\_\_range=(max(purchase\_date) - 6 \* 30, max(purchase\_date))”, so each member has their own purchase period. The queryset in dataframe look like the following:

member | start\_date | end\_date | total\_amount  
1111111| 2021-01-03 | 2021-06-02 | 1234  
1111112| 2021-02-03 | 2021-07-02 | 2345  
1111113| 2021-03-03 | 2021-08-02 | 3456  
.  
.  
.  
1234565| 2022-03-03 | 2022-08-02 | 9999

It would be quite easy to accomplish this using left join by directly accessing database using DB editor. e.g. SSMS

=========================================  
select MemberID  
, max(PurchaseDate) as MaxPurchaseDate  
, dateadd(month, -6, max(PurchaseDate)) as StartDate  
, max(PurchaseDate) as EndDate  
into #Last6Month  
from dbo.SalesTable  
group by MemberID

select a.MemberID  
, sum(Amount) as TotalAmount  
from dbo.SaleTable as a  
left join #Last6Month as b  
on a.MemberID = b.MemberID and b.PurchaseDate between a.StartDate and a.EndDate  
where b.MemberID is not null  
group by a.MemberID

=========================================

But i found it difficult in doing this in django orm. The main reason is that I found it difficult to have join operations for unrelated tables (in my case a temp table with the Sale table itself). I have tried to use chain Q() to build q |= Q(\*\*{‘memberid’: row[‘memberid’], ‘purchasedate\_\_range’:[row[‘start\_dfate’],row[‘end\_date’]]}), but it failed because of retrieving 300,000 members in this way is too big for django orm, plus it’s basically used WHERE clause with 300,000 members with their specific periods that is very inefficient. It worked for 25,000 members but not for 300,000 members.

Is there anyone has a better idea for me would be very appreciated, thanks.

Kelvin

---

<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:** [July 13, 2024, 11:33am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/2 "2024-07-13T11:33:35Z")

</div>

If I’m understanding the situation correctly, you’re not really joining two tables here. You’re aggregating data within a subset of that table. (Think “Subquery” instead of “join”)

So I did some playing around, and I think this might be on the right track.

First, assume the model:

```auto
class Sale(models.Model):
    member = models.CharField(max_length=10)
    purchase_date = models.DateField()
    amount = models.IntegerField(default=0)

```

First, we’ll define a subquery to get the last purchase date for each member.

```auto
subq = Subquery(
  Sale.objects.values('member'
    ).annotate(max_date=Max('purchase_date')
    ).filter(member=OuterRef('member')
    ).values('max_date')
)

```

Now, we can write a query using that information to get the Sale instances within the proper range:

```auto
queryset = Sale.objects.annotate(mdt = subq
    ).filter(purchase_date__lte=F('mdt'), 
             purchase_date__gte=F('mdt')-timedelta(days=180)
    ).values('member'
    ).annotate(total=Sum('amount')
    ).order_by('member')

```

Given the following data:

```auto
Member | Purchase date | Amount
1111 | 2023-06-12 | 30
1111 | 2024-06-12 | 20
1111 | 2024-07-12 | 10
1112 | 2024-01-12 | 50
1112 | 2024-05-12 | 40
1112 | 2024-07-15 | 80
1112 | 2024-08-12 | 70
1112 | 2024-12-12 | 60

```

That query produces:  
`<QuerySet [{'member': '1111', 'total': 30}, {'member': '1112', 'total': 210}]>`

Now, I have absolutely _no_ idea how performant this would be on a table containing 300,000 rows. At a minimum I’m going to guess you’re going to want indexes on both the `member` and `purchase_date` columns.  
But hopefully this gives you another direction in which to work.

---

<div class="post-metadata">

**Author:** ![kelvinho8](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kelvinho8/32/2853_2.png) [@kelvinho8](https://forum.djangoproject.com/u/kelvinho8)\
**Post date:** [July 16, 2024, 11:52am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/3 "2024-07-16T11:52:09Z")

</div>

Hi Ken,

Thank you for your response.

But when I applied the same logic using subquery, I was unable to get the results as you did (not sure if I did it correctly or not 😅) and the following error occurred:

Error: “DB::Exception: Scalar subquery returned more than one row: While processing …”.

I was wondering if “mdt” in “queryset = Sale.objects.annotate(mdt = subq …” needs a scalar value instead but “subq” returns a list of max\_date causing the error.

Really appreciated your answer.

Kelvin

---

<div class="post-metadata">

**Author:** ![kelvinho8](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kelvinho8/32/2853_2.png) [@kelvinho8](https://forum.djangoproject.com/u/kelvinho8)\
**Post date:** [July 16, 2024, 11:56am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/4 "2024-07-16T11:56:37Z")

</div>

Besides, I was wondering if subquery supports a table instead of a column? Such as the following?

subq = Subquery(  
Sale.objects.values(‘member’  
).annotate(max\_date=Max(‘purchase\_date’)  
).filter(member=OuterRef(‘member’)  
).values( **‘member’** ,‘max\_date’)  
)

Then subq can be used in Sale.objects.annotate() or Sale.objects.filter()?

Kelvin

---

<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:** [July 16, 2024, 12:50pm UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/5 "2024-07-16T12:50:54Z")

</div>

To answer your second question first, no - at least not directly. A subquery used in an annotation must return 1 value. See the docs for [Subquery](https://docs.djangoproject.com/en/5.0/ref/models/expressions/#subquery-expressions) for more details.

> [@kelvinho8](#):
>
> Error: “DB::Exception: Scalar subquery returned more than one row: While processing …”.

Do you have the two values clauses in your subquery?  
Can you post the actual query you tried? I might be able to help if I can see it. Also note that if your model isn’t structurally similar to my assumed model, there are other changes that may need to be made, so seeing the model(s) involved would help.

---

<div class="post-metadata">

**Author:** ![kelvinho8](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kelvinho8/32/2853_2.png) [@kelvinho8](https://forum.djangoproject.com/u/kelvinho8)\
**Post date:** [July 17, 2024, 7:49am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/6 "2024-07-17T07:49:38Z")

</div>

Hi Ken,

I don’t have Sale model at hand, but I have another model table ‘Price’ that can be used to mimic the sale case and demostrate the problem I am facing.

* * *

## Price model

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/0/f/0ffc8e7952d8d1adc0495a397a5b615bc04185c3.png)

* * *

## The Price model has data as follows:

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/e/a/eab9e6925e0fab3d1bc926cc78f944e02a4e2726.png)

* * *

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/c/8/c8bed7c1bd0a99a255e9c8c1a1fd8d01532ff9b2.png)

* * *

## I am using 3 tickers as an example, if I aggregate these 3 tickers, we can find their minimum and maximum trading date, and the sum of close price that corresponds to these min max trading periods (it’s just an example for demo only, usually we don’t sum close price 🙂 ). But what I really need is to calculate the sum of close price based on their individual last 6 months periods (see selected\_start\_date and selected\_end\_date) and last 6 months periods of each stocks are not the same.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/9/a/9ad751601d30acf172d3403ff219565d0a593743.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/7/b/7bda91a45b03e65438b3ae7cee11a9cb4b2f5c79.png)

* * *

## If I implemented the requirement logic in orm, it will look like the following.

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/c/e/ce759599a4a77a865dfb5f8cc7a3fb32921addbd.png)

 ![image](https://us1.discourse-cdn.com/flex026/uploads/djangoproject/original/3X/7/2/72a9825327e1152d7f34c8d06a9562c09f654870.png)

* * *

After filtering based on the stocks and their corresponding selected periods using chain Q(), the min\_trading\_date and max\_trading\_date will be the same as selected\_start\_date and selected\_end\_date. The ‘sum\_selected\_adj\_close’ turns out that is the aggregated values I eventually need. However, the situation is getting difficult when the number of stocks say increasing to 300,000.

So I was wondering if you know any way that can solve this situation?

Thank you for your help in advance.

Kelvin

---

<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:** [July 17, 2024, 7:54pm UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/7 "2024-07-17T19:54:19Z")

</div>

So one of the potential factors here is that you are using a custom manager.

Does this manager alter the standard queryset in _any_ way? If so, that could be interfering with what you’re trying to do here. (If not, if the only definitions within the manager are extra query functions, then that shouldn’t be an issue.)

Again, my gut hunch is that a subquery is your best chance of a solution here, with the caveat that if you’re unable to add indexes to the tables, I don’t think anything you do within an ORM query is going to do you much good because you will end up needing to do table scans regardless of the query being written.

You could take a look at the [django-cte](https://github.com/dimagi/django-cte) package to see if a solution using it is more effective.

Side note: Please don’t post images of code or other textual data here. Copy/paste the text into the body of your post, surrounded between lines of three backtick - ` characters. This means you’ll have a line of ```, then the code (or other text), then another line of ```. This forces the forum software to keep the existing text formatting. (That’s how I did the sample table above.)

---

<div class="post-metadata">

**Author:** ![kelvinho8](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kelvinho8/32/2853_2.png) [@kelvinho8](https://forum.djangoproject.com/u/kelvinho8)\
**Post date:** [July 18, 2024, 11:45am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/8 "2024-07-18T11:45:41Z")

</div>

Hi Ken,

Thank you for your hints. I managed to solve it by using ‘django-cte’ 🙂

You save me day, thanks.

Kelvin

---

<div class="post-metadata">

**Author:** ![white-seolpyo](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/white-seolpyo/32/22386_2.png) [@white-seolpyo](https://forum.djangoproject.com/u/white-seolpyo)\
**Post date:** [July 25, 2024, 12:42am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/9 "2024-07-25T00:42:08Z")

</div>

it’s too late, but i think it’s possible without django-cte.  
solution is just add filtering pk option.  
because it worked in less than 300,000 rows.

```auto
l = []
for i in range(300000)[::20000]: 
  l += list(Price.objects.filter(pk __gte=i, pk__ lte=i+20000, {your filter})
for i in l: print(i. __dict__ )

```

---

<div class="post-metadata">

**Author:** ![kelvinho8](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kelvinho8/32/2853_2.png) [@kelvinho8](https://forum.djangoproject.com/u/kelvinho8)\
**Post date:** [July 25, 2024, 1:31am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/10 "2024-07-25T01:31:56Z")

</div>

Hi Seolpyo,

It’s never too late and I really appreciate you keep trying to find a better solution for my case.

Unfortunately, my question also has to fulfill one more condition which is kept the data type as queryset so that the query results can be used by others and let the others to decide what datatype they like to convert to by queryset.

By using for loop with list can be a solution if without the condition I mentioned above.

Anyway, very appreciate to have your input on it.

Kelvin

---

<div class="post-metadata">

**Author:** ![white-seolpyo](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/white-seolpyo/32/22386_2.png) [@white-seolpyo](https://forum.djangoproject.com/u/white-seolpyo)\
**Post date:** [July 25, 2024, 1:35am UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/11 "2024-07-25T01:35:46Z")

</div>

if you need queryset, it’s simple.  
just 1 more request to database.

for loop is just check data. not any means.

```auto
l = []
for i in range(300000)[::20000]: 
  l += list(Price.objects.filter(pk __gte=i, pk__ lte=i+20000, {your filter}).value_list('pk', flat=True)
p = Price.objects.filter(pk__in=i)
print(len(l))
print(p.count())

```

---

<div class="post-metadata">

**Author:** ![kelvinho8](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kelvinho8/32/2853_2.png) [@kelvinho8](https://forum.djangoproject.com/u/kelvinho8)\
**Post date:** [July 25, 2024, 3:20pm UTC](https://forum.djangoproject.com/t/what-is-the-best-way-of-querying-300-000-members-and-each-member-with-different-purchase-periods/32946/12 "2024-07-25T15:20:02Z")

</div>

let me try it tomorrow and give you feedback 🙂