Thanks, I have seen that and plan to use it, but for this particular feature I need something more tailored.
-Samuel On Monday, November 6, 2017 at 8:56:29 PM UTC+1, Matthew Pava wrote: > > Though it doesn’t directly answer your query, you might be interested in > this package: > > https://github.com/burke-software/django-report-builder > > > > > > *From:* django...@googlegroups.com <javascript:> [mailto: > django...@googlegroups.com <javascript:>] *On Behalf Of *Samuel Abels > *Sent:* Monday, November 6, 2017 1:33 PM > *To:* Django users > *Subject:* Equivalent of multi-table JOIN (another post on reverse > select_related) > > > > I am working on a reporting feature that allows users for querying > arbitrary models and fields, and present the result as a table. For > example, consider the following object model: > > > > Package > > | > > v > > Device <- Component > > ^ ^ > > | | > > |---- Interface <---2--- Connection > > ^^^ > > / | \ > > / | \ > > / | \ > > Sampling IP Policy > > > > (The dash is the direction of a ForeignKey.) > > To produce a report, I chose a three-step process: > > > > 1. The user interfaces returns a list of fields to be included in the > report, such as > > > > args = dict('Device.hostname__contains': 'localhost', > 'Package.name__icontains': 'unix', 'IP.address__contains': '192') > > > > 2. Given the list of args, find the shortest path that connects all > required models. For the example above, the result is a tuple: > > > > path = Device, Package, Interface, IP > > > > 3. In theory, I could now perform the following SQL request: > > > > SELECT * FROM myapp_device d > > LEFT JOIN myapp_package pa ON pa.device_id=d.id > > LEFT JOIN myapp_interface ifc ON ifc.device_id=d.id > > LEFT JOIN myapp_ip ip ON ip.interface_id=ifc.id; > > > > But of course, I want to avoid the raw SQL. I considered the following > options: > > > > - Using Device.objects.select_related() does not work, because Device has > a 1:n relation to Package (and also to Unit), which Django's > select_related() does not support. > > > > - Using prefetch_related() does not work, because it prefetches > everything, which is too much in our case (>100 million rows if a user > queries on all tables), and it does not provide us with a total of the > number of rows selected. In practice, I want to count(*) everything for > displaying a total, and fetch only a subset, using LIMIT. > > > > Our tests showed that the raw SQL query with LEFT JOIN is fast enough for > production, regardless of what fields and objects are being queried. The > craziest query I built took about 20 seconds, which is ok for what we are > trying to do. > > > > Any other options? > > > > -Samuel > > -- > You received this message because you are subscribed to the Google Groups > "Django users" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to django-users...@googlegroups.com <javascript:>. > To post to this group, send email to djang...@googlegroups.com > <javascript:>. > Visit this group at https://groups.google.com/group/django-users. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-users/ec0e2d37-9b0f-4623-8f60-c6feeef0eb9e%40googlegroups.com > > <https://groups.google.com/d/msgid/django-users/ec0e2d37-9b0f-4623-8f60-c6feeef0eb9e%40googlegroups.com?utm_medium=email&utm_source=footer> > . > For more options, visit https://groups.google.com/d/optout. > -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/f692aa3a-cc23-4d78-8e3f-16ed30097038%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
