41838

Django query for large number of relationships

I have Django models setup in the following manner:

model A has a one-to-many relationship to model B

each record in A has between 3,000 to 15,000 records in B

What is the best way to construct a query that will retrieve the newest (greatest pk) record in B that corresponds to a record in A for each record in A? Is this something that I must use SQL for in lieu of the Django ORM?

Answer1:

Create a helper function for safely extracting the 'top' item from any queryset. I use this all over the place in my own Django apps.

def top_or_none(queryset): """Safely pulls off the top element in a queryset""" # Extracts a single element collection w/ top item result = queryset[0:1] # Return that element or None if there weren't any matches return result[0] if result else None

This uses a bit of a trick w/ the slice operator to add a limit clause onto your SQL.

Now use this function anywhere you need to get the 'top' item of a query set. In this case, you want to get the top B item for a given A where the B's are sorted by descending pk, as such:

latest = top_or_none(B.objects.filter(a=my_a).order_by('-pk'))

There's also the recently added 'Max' function in Django Aggregation which could help you get the max pk, but I don't like that solution in this case since it adds complexity.

P.S. I don't really like relying on the 'pk' field for this type of query as some RDBMSs don't guarantee that sequential pks is the same as logical creation order. If I have a table that I know I will need to query in this fashion, I usually have my own 'creation' datetime column that I can use to order by instead of pk.

<strong>Edit based on comment:</strong>

If you'd rather use queryset[0], you can modify the 'top_or_none' function thusly:

def top_or_none(queryset): """Safely pulls off the top element in a queryset""" try: return queryset[0] except IndexError: return None

I didn't propose this initially because I was under the impression that queryset[0] would pull back the entire result set, then take the 0th item. Apparently Django adds a 'LIMIT 1' in this scenario too, so it's a safe alternative to my slicing version.

<strong>Edit 2</strong>

Of course you can also take advantage of Django's related manager construct here and build the queryset through your 'A' object, depending on your preference:

latest = top_or_none(my_a.b_set.order_by('-pk'))

Answer2:

I don't think Django ORM can do this (but I've been pleasantly surprised before...). If there's a reasonable number of A record (or if you're paging), I'd just add a method to A model that would return this 'newest' B record. If you want to get a lot of A records, each with it's own newest B, I'd drop to SQL.

remeber that no matter which route you take, you'll need a suitable <strong>composite</strong> index on B table, maybe adding an order_by=('a_fk','-id') to the Meta subclass

Recommend

  • Terminate the Thread by using button in Tkinter
  • Android App Perfomance Issues on JellyBean
  • Linq to entities use `Func` to create property in a select statement that produces an anonymous obje
  • regex: matching 3 consecutive words
  • mysql user row level access
  • multiple options for a community connector configuration in Google Data Studio
  • pandas mix position and label indexing without chaining
  • Left and right button misbehaving when trying to add an empty span to contenteditable div
  • AngularJS : transclude ng-repeat inside directive
  • Can I use jquery to blank textarea fields or ajax like input boxes?
  • how to sort an arraylist that contains string arrays?
  • Is it possible to specialize on a static lifetime?
  • Quick Question About Get and Set
  • Count query results on multi-join statements
  • Criterion causing memory consumption to explode, no CAFs in sight
  • redirect_to root_url and return unless @user.activated
  • Google Places API - Find a company's CID and LRD
  • Hide HTML elements without javascript, only CSS
  • Python pickle not one-to-one: different pickles give same object
  • Debugging VB6 Code From Visual Studio 2010
  • RxJava debounce by arbitrary value
  • C: Incompatible pointer type initializing
  • why xml file does not aligned properly after append the string in beginning and end of the file usin
  • How to have background script and something similar to a default popup?
  • Allowing both email and username for authentication
  • dc-js disable selecting slices on click for pie chart
  • Get one-time binding to work for ng-if
  • Regex thinks I'm nesting, but I'm not
  • How to model a transition system with SPIN
  • Weird JavaScript statement, what does it mean?
  • How do you troubleshoot character encoding problems?
  • Python: how to group similar lists together in a list of lists?
  • Java static initializers and reflection
  • Exception on Android 4.0 `android.os.StrictMode$AndroidBlockGuardPolicy.onNetwork(StrictMode)`
  • Change div Background jquery
  • Bitwise OR returns boolean when one of operands is nil
  • Is there any way to bind data to data.frame by some index?
  • Why is Django giving me: 'first_name' is an invalid keyword argument for this function?
  • How can I use `wmic` in a Windows PE script?
  • How to push additional view controllers onto NavigationController but keep the TabBar?