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:* [email protected] <javascript:> [mailto: > [email protected] <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 [email protected] <javascript:>. > To post to this group, send email to [email protected] > <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 [email protected]. To post to this group, send email to [email protected]. 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.
