Having issues with optimizing query on foreign key
<p>I have tried different ways of querying my data, but still results in a large amount of pings to the DB. </p>

<p>I have tried using <code>select_related</code>.</p>

<p>Here are my models:</p>

<pre><code>class Order(models.Model):
    num = models.CharField(max_length=50, unique=True)

class OrderInfo(models.Model):
    info = models.CharField(max_length=100, unique=True)
    datetime = models.DateTimeField(auto_now_add=True, blank=True)
    order_fk = models.ForeignKey(Order)
</code></pre>

<p><strong>What I am trying to achieve:</strong></p>

<p><code>OrderInfo</code> has a bunch of information pertaining to the <code>Order</code>.</p>

<p>What I want is to be able to get the most recent <code>OrderInfo</code> from the DB, but I want it unique to <code>Order</code>. The unique part is where I am struggling to minimize my query amount.</p>

<pre><code>ois = OrderInfo.objects.order_by('-datetime').select_related('order_fk')
</code></pre>

<p>When I try to filter it is doing a query on each Order to check for uniqueness the queries ramp up.</p>

<pre><code>For instances:
_ = [oi.order_fk for oi in ois] # queries reach to 20k, takes too long.
</code></pre>

<p>Also, then I just need to limit how many responses I get, but I need to know how many unique <code>Orders</code> there are first in order to limit it. </p>

<p>Anyone, know a proper approach to minimize these queries or possibly I may need to restructure my models.</p>

<p>Notes:</p>

<ul>
<li>Django 1.7</li>
<li>Python 2.7</li>
<li>SQLite</li>
</ul>

<p>How many <code>OrderInfo</code> objects are there per order? If it's small, it is probably easiest to use <code>prefetch_related</code> and just do the filtering in python:</p>

<pre><code>class Order(models.Model):
    num = models.CharField(max_length=50, unique=True)
    @property
    def latest_order_info(self):
        return max(self.orderinfo_set.all(), key=attrgetter('datetime')
</code></pre>

<p>Then in your application code, you can do:</p>

<pre><code>orders = Order.objects.filter(...).prefetch_related('orderinfo_set')
</code></pre>

<p>It's a little wasteful, but in my experience is usually not a bottleneck unless the parent model has a very large number of children.</p>

python django sqlite3