Complex SQL through the Django ORM

Sorry I have been trying to make things more readable since posting initial information.

I edited my previous post.

year, brand, url, desc, colour, size, mpn are all from the class i

mpn and eta are the only fields in find_eta

Ok, so in the model you posted, you don’t show a foreign key field to any other models. There’s no link I can see that you could transit to another field.

You wrote: {{ k.mpn.eta }}, but mpn here is a CharField, not a foreign key - it’s not a reference to another model.

It’s clear that I am stuck in my SQL ways of thinking and it is tripping me up.

So, I would need to change the mpn in my class i to be a foreign key, and that will have to point to the find_eta class. However, if I do that then whichever class is defined first will have an undefined issue with its foreign key.

I haven’t been following this topic all that closely, so I’m playing a little catch up here. I don’t understand the relationship between these two models.

You do have an FK in find_eta class - that puts the many side of the many-to-one relationship on the side of find_eta.

This means that there can be multiple instances of find_eta for each i. It doesn’t make sense to try to refer to “the” eta of a find_eta associated with an i because there can be more than one.

You can get the set of find_eta for an i by following the relationship backwards - see Related objects reference | Django documentation | Django. The expression i.find_eta_set.all gives you an iterable allowing you to access every find_eta that refers back to the i.

I guess I never posted the explicit rationale for the two models. I will do so and then check out your documentation and try to wrap my head around it.

The Class i is a set of items that are available for interested people, which a whack of attributes.

The Class Find_Eta is a database view which takes into account 2 other database tables (items on order, and items on reserve). The view runs some SQL logic to calculate if there are any available items still left to reserve. So, if there are items left to reserve, it will use the appropriate date they are expected to arrive. If not, it returns null. It was necessary to do this because there will be certain items that have multiple shipments, so it has to calculate the next available shipment with free space, if available.

Thanks to all for your help, now its time for some reading!

So, after doing some reading, I tried the following:

I tweaked the model name for i_eta by adding a related_name to make output more meaningful to me as well as actually renaming the class itself.

class Eta(models.Model):

    mpn = models.ForeignKey(i, on_delete=models.CASCADE, primary_key = True, db_column='mpn', related_name='etas')
    eta = models.DateField()

    class Meta:
        managed = False
        db_table = 'i_eta'

Then I went back into my template to try to get both object outputs. Now I use ‘etas’ instead of class_set and the syntax I iterate through becomes k.etas.all (k from previous iteration, etas from the class_set, and all to grab them all)


{% extends 'base.html' %}

{% block content %}
    {% if kidsquery %}
    <div class="table-responsive">
        <table class="table table-striped table-hover table-bordered table-sm">
            <thead class="table-dark">
                <tr>
                    <th scope="col">Year</th>
                    <th scope="col">Brand</th>
                    <th scope="col">Model</th>
                    <th scope="col">Colour</th>
                    <th scope="col">Size</th>
                    <th scope="col">Part#</th>
                    <th scope="col">ETA</th>
                </tr>
            </thead>   

            {% for k in kidsquery %}
            <tr>
                <td>{{ k.bikeyear }}</td>
                <td>{{ k.bikebrand }}</td>
                {% if k.bikeurl %}
                <td><a href="{{ k.bikeurl }}" target="_blank">{{ k.bikedesc }}</a></td>
                {% else %}
                <td>{{ k.bikedesc }}</td>
                {% endif %}
                <td>{{ k.bikecolour }}</td>
                <td>{{ k.bikesize }}</td>
                <td>{{ k.bikempn }}</td>
                {% for arrival in k.etas.all %}
                {% if arrival is null %}
                <td>Contact Us</td>
                {% else %}
                <td>{{ arrival|date:"F Y" }}</td>
                {% endif %}
                {% endfor %}
            </tr>
            {% endfor %}
          </table>
    </div>
    {% endif %}
{% endblock %}

I used {% for arrival in k.etas.all %} but in my output I get nothing for that table column. I don’t get the null display or any applicable date values.

That means that k.etas.all is returning an empty queryset. You should consider using something closer to:

                {% if k.etas.first %}
                <td>{{ k.etas.first|date:"F Y" }}</td>
                {% else %}
                <td>Contact Us</td>
                {% endif %}

Otherwise if that queryset has multiple values, it would render that same number of td elements breaking the expected table structure. Similarly, when there are none, none of that looping code is run. The problem with using .first is that it generates an additional query for each element k. That’s where using prefetch_related or a Subquery instance to get the relevant date field would come into play and be more efficient.

I was able to get the dates to display by using {{ arrival.eta|date:“F Y” }} so the date now displays when there is one.

However as you mentioned if it returns an empty queryset the if statement won’t be of any use.

The end to the saga:


{% extends 'base.html' %}
{% block content %}
    {% if kidsquery %}
    <div class="table-responsive">
        <table class="table table-striped table-hover table-bordered table-sm">
            <thead class="table-dark">
                <tr>
                    <th scope="col">Year</th>
                    <th scope="col">Brand</th>
                    <th scope="col">Model</th>
                    <th scope="col">Colour</th>
                    <th scope="col">Size</th>
                    <th scope="col">Part#</th>
                    <th scope="col">ETA</th>
                </tr>
            </thead>
            {% for info in kidsquery %}
            <tr>
                <td>{{ k.bikeyear }}</td>
                <td>{{ k.bikebrand }}</td>
                {% if k.bikeurl %}
                <td><a href="{{ k.bikeurl }}" target="_blank">{{ k.bikedesc }}</a></td>
                {% else %}
                <td>{{ k.bikedesc }}</td>
                {% endif %}
                <td>{{ k.bikecolour }}</td>
                <td>{{ k.bikesize }}</td>
                <td>{{ k.bikempn }}</td>
                {% for arrival in k.etas.all %}
                {% if arrival.eta %}
                <td>{{ arrival.eta|date:"F Y" }} </td>
                {% else %}
                <td>Contact Us</td>
                {% endif %}
                {% endfor %}
            </tr>
            {% endfor %}
        </table>
    </div>
    {% endif %}
{% endblock %}

Thanks again to everyone’s help!

1 Like

I mentioned it earlier and you acknowledged it so I’m not sure if you’ve handled this elsewhere, but if k.etas.all returns more or less than exactly 1 instance, this is likely going to render html that you don’t want.