Efficient Pagination from Join in Django

Question

Hi. I've got a problem here with a join and pagination. I have 3 Models:

• Pad
• Tag
• TagRelation

I use Tag relation to manage a ManyToMany relationship betweent Pads and Tags (with the through attribute of te ManyToMany field). Now when i do a join on the Pad and Tag tables it gives me something like this...

pad.name   tag.name     etc
---------------------------
pad1       tag1         ...
pad1       tag2         ...
pad2       tag3         ...

Now when i create a Paginator() object from these results it's obviously the wrong item count per page. I would have to iterate throught ALL the results and generate a dictionary like this...

[{name:'pad1', tags:['tag1', 'tag2']}, {name:'pad2' ....]

... and use the Paginator on this to get the correct pages.

What's the best approach for this Problem? I could do a DB query for each Pad object on the current page to get it's Tags but I guess that would kill the server sooner or later (should be performant).

The content's of the results can also be quite long and iterating through the whole set would cost a lot of system memory (or would it? enlighten me :) ).

Answer 1

If I'm understanding correctly, you should be Paginating on Pad.objects.all(), then use a select_related on the TagRelation objects both ways to grab the appropriate tags in just one (additional) query, and using that data in your view/template. Something like:

thispagepadids = [o.id for o in mypageobject.object_list]
tagrels = TagRelation.objects.filter(pad__id__in=thispagetagids).select_related('tag','pad'))

(assuming you have your page object in mypageobject). Then you can get the pad and tag for any given tagrel in code (using the regroup template tag is probably the easiest way to do this) but the DB only performs one (giant) query, and your pagination count is still correct.

Note that we had to do the 2 queries because you can't just use select_related directly on the many-to-many field (see this ticket), but you can use it to follow the FKs both ways from the intermediate m2m table.