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>Based on <a href="http://stackoverflow.com/a/14293530/4978266">this stack overflow answer to a similar question</a>, I think you'd just need to split this up into two queries to hopefully reduce how much you have to peg the database.</p>

<p>Since you're using SQLite, you do not have access to <code>DISTINCT ON</code>, so perhaps try the following:</p>

<pre><code>from django.db.models import Q, Max
import operator

ois = OrderInfo.objects.values('order_id').annotate(max_datetime=Max('datetime'))
filters = reduce(operator.or_, [(Q(order_id=oi['order_id']) &amp;
    Q(datetime=oi['max_datetime'])) for oi in ois])
filtered_ois = OrderInfo.objects.filter(filters)
</code></pre>

<p>To explain what this is doing (again, this is based off of the linked stack overflow answer):</p>

<ul>
<li>Get the OrderInfo objects grouped by their <code>order_id</code> and annotate those with the maximum datetime among all of the <code>OrderInfo</code> objects that have that <code>order_id</code>.</li>
<li>Use these values to create a new filter on the <code>OrderInfo</code> objects.</li>
<li>Use the filter on <code>OrderInfo</code> objects and return them.</li>
</ul>

python django sqlite3