Complex SQL through the Django ORM

Using Django
1

Hey there, I am attempting to move over a very basic PHP site that runs SELECT SQL queries based on user input to display some data from a MySQL database. Things started out well, but unfortunately I don’t know how to format the SQL to be able to be run within Django, as it runs queries combining from a database with 3 tables and a database view as well.

Here is an example of one of the database queries that is being run. Is it even possible to do such things in Django?

SELECT i.year, i.brand, i.desc, i.colour, i.size, i.mpn, i.url, 
COALESCE(DATE_FORMAT(i_eta.eta, '%M %Y'),'Unknown') 
as eta 
FROM i 
JOIN i_eta ON i_eta.mpn = i.mpn 
WHERE category LIKE 'kids' 
ORDER BY i.brand, i.desc, i.colour, FIELD(size, 'xxl','xl','l','ml','m','s','xs','xxs') DESC, size+0, size

What I have so far (not much unfortunately) would be:
query = i.objects.filter(category="kids").order_by('brand','desc','colour')

the i_eta is a view, and I don’t know how to combine that with the other query info. I am guessing it would roughly translate to:
coalesce(i_eta,'Contact Us')

I am hoping to avoid directly running SQL but am lost.

2
  1. Use values to select only certain columns
  2. Use database functions for coalesce and date formatting
  3. Use the contains lookup for a like statement
  4. For the join to the view, is there any way to duplicate the eta calculation as an annotation and not use the view?
3

The view essentially is the brains behind the current implementation. It compares the values from 2 other tables (bo for backordered items and res for reserved items) to determine if any items are still available. That way if I have 5 items arriving in January, and 3 are already reserved, it will display the date of January as being available. If I have 3 more coming in March, and 6 are spoken for, it calculates the date of March to be displayed.

I would guess that the view could be replaced with Django/Python, I don’t know how it would compare for speed.

Here is the view:

CREATE VIEW i_eta AS WITH RECURSIVE cte_count(n) AS (
  SELECT
    1
  UNION ALL
  SELECT
    n + 1
  FROM
    cte_count
  WHERE
    n < 100
)
SELECT
  i.mpn,
  (
    SELECT
      MIN(orders.eta)
    FROM
      (
        SELECT
          ROW_NUMBER() OVER(
            ORDER BY
              bo.eta
          ) row_num,
          bo.mpn,
          bo.eeta

        FROM
          bo bo
          JOIN cte_count c ON c.n <= bo.qty
        WHERE
          bo.mpn = i.mpn
      ) orders

      LEFT JOIN(
        SELECT
          ROW_NUMBER() OVER() row_num
        FROM
          res r

          JOIN cte_count c ON c.n <= r.qty

        WHERE
          r.mpn = b.mpn
      ) reservations ON orders.row_num = reservations.row_num
    WHERE
      reservations.row_num IS NULL
  ) AS ETA
FROM
  inventory i
4

So from what I gather, the only thing I really have to do to use the view is to treat it as another database table, so it is just a matter of setting up the syntax properly.

Query 1: (pulls the information without the related view successfully)
query = i.objects.filter(category='kids').values('year', 'brand', 'desc', 'colour', 'size', 'mpn', 'url').order_by('brand','desc','colour')

Query 2: (pulls the unlinked views successfully)
query = i_Eta.objects.values('eta')

I tried to combine them and I get nothing from the ETA column

query = i.objects.filter(category='kids').select_related('i_Eta', 'eta').values('year', 'brand', 'desc', 'colour', 'size', 'mpn', 'url').order_by('brand','desc','colour')

5

I believe you an create an unmanaged model that points to the view. This post goes a bit more into it.

It sounds like you may have already figured this out. Can you share the model you created for it?

6

The model that I created is:

class i_Eta(models.Model):
mpn = models.ForeignKey(i, models.DO_NOTHING, db_column=‘mpn’, max_length=50)
eta = models.DateField(blank=True, null=True)

class Meta:
    managed = False
    db_table = 'i_eta'
7

So I think what you want is something like:

from django.db.models import Value
from django.db.models.functions import Coalesce
query = i.objects.filter(category="kids").annotate(
    eta=Coalesce("i_eta_set__eta", Value("Unknown"))
).order_by('brand','desc','colour')

Check out the docs for the Coalesce function in Django. I’m not quite sure what I have is syntactically correct.

Edit:
I’m also not sure I have the i_eta_set correct. That may also need to change.

8

Thanks for that - after looking into it I am not worried about the coalesce aspect as I can probably just use Django to output something specific if the value is null. The coalesce was more of an easy output method to the more basic php page I had made.

My main issue is figuring out how to pair those two queries together and actually get a proper nonempty result.

[Edit: Interestingly enough, when viewing the source of the page, the loop is running through all of the entries and creating blank table cells for every entry in the database. So, at least there is that!]

9

I don’t know if this is a step forward or not, but after reading some questions by others I noticed that the standard way of using .select_related is to use the foreignkey table first, whereas I was trying to use it second.

So, considering my models are:


class i(models.Model):
    mpn = models.CharField(primary_key=True, max_length=50)
    category = models.CharField(max_length=50)
    year = models.CharField(max_length=4)
    brand = models.CharField(max_length=50)
    desc = models.CharField(max_length=255)
    size = models.CharField(max_length=50)
    colour = models.CharField(max_length=255)
    url = models.CharField(max_length=255)

    class Meta:
        managed = False
        db_table = 'i'

class Boi(models.Model):
    mpn = models.ForeignKey(i, on_delete=models.CASCADE)
    qty = models.IntegerField()
    eta = models.DateField()
    class Meta:

        managed = False
        db_table = 'boi'

class Resi(models.Model):
    mpn = models.ForeignKey(i, on_delete=models.CASCADE)
    qty = models.IntegerField()

    class Meta:
        managed = False
        db_table = 'resi'

class i_Eta(models.Model):
    mpn = models.ForeignKey(i, on_delete=models.CASCADE)
    eta = models.DateField()

    class Meta:
        managed = False
        db_table = 'i_eta'

My views function is:

def kids(request):
    kidsquery = i_Eta.objects.select_related('i').values('eta')
    return render(request, 'kids.html', {'kidsquery': kidsquery})

And my template structure is:


{% 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.i.year }}</td>
                <td>{{ k.i.brand }}</td>
                {% if k.i.url %}
                    <td><a href="{{ k.i.url }}" target="_blank">{{ k.i.desc }}</a></td>
                {% else %}
                    <td>{{ k.i.desc }}</td>
                {% endif %}
                <td>{{ k.i.colour }}</td>
                <td>{{ k.i.size }}</td>
                <td>{{ k.i.mpn }}</td>
                {% if k.eta is null %}
                    <td>Contact Us</td>
                {% else %}
                    <td>{{ k.eta }}</td>
                {% endif %}
            </tr>
            {% endfor %}
        </table>
    </div>
    {% endif %}

This results in the ETAs being populated for all items, but I don’t know how to retrieve the values of the table i. If I did, I could then filter by category and also populate my template.

10

What’s the purpose of using .values('eta')?

11

When I imported the models Django wasn’t happy that I didn’t have a primary_key = true for the mpn field (I thought that foreignkey would be equivalent) and so it added its auto id to the model.

So, if I do not specifically select the value of eta only, I get an error about "Unknown column ‘i_eta.id’ in ‘field list’ - doing this allows me to avoid that issue, although if I knew how to fix that issue I think I would be in better shape.

12

See the docs about automatic primary keys. You can set primary_key=True on a ForeignKey field. That should eliminate the auto generated id field and thus stop the error about i_eta.id not existing.

13

Thanks for the information! I never thought of putting a primary_key setting on a foreign key. I replaced the model with a different model with the change.

class Find_Eta(models.Model):
    mpn = models.ForeignKey(i, on_delete=models.CASCADE, primary_key = True)
    eta = models.DateField()

    class Meta:
        managed = False
        db_table = 'i_eta'

I checked the migration file before running it, it had no auto ID field in the list, however I

Unknown column ‘i_eta.mpn_id’ in ‘field list’

To me, it looks like an automatic ID was assigned to the mpn variable, but I don’t see how.

14

It sounds like you’re getting close. You’re running into how Django manages storing the database level column and then providing the related model instance as a part of the ORM. This part of the docs explains it. You’ll want to specify the db_column parameter.

15

Well no more error! Thanks!

I suppose the last question revolves around iterating through the result:

kidsquery = Bikes.objects.filter(bikecategory='kids').select_related('find_eta')

when I iterate through this, I can use

{{ iterable.value }} to get the value from the class that is the subject of the .objects.filter (aka the first in the query), but how to I get a value from the second class (aka the joined one)?

Everything I have been finding has lead me to believe the following would work:

{{ iterable.classname.value }}

but it doesn’t

16

You’re really close. It would be {{ iterable.attribute_with_the_foreign_key.field_of_related_model }}

17

So, in my case with:

class i(models.Model):
    mpn = models.CharField(primary_key=True, max_length=50)
    category = models.CharField(max_length=50)
    year = models.CharField(max_length=4)
    brand = models.CharField(max_length=50)
    desc = models.CharField(max_length=255)
    size = models.CharField(max_length=50)
    colour = models.CharField(max_length=255)
    url = models.CharField(max_length=255)

    class Meta:
        managed = False
        db_table = 'bikes'

class Find_Eta(models.Model):
    mpn = models.ForeignKey(Bi, on_delete=models.CASCADE, primary_key = True, db_column='mpn')
    eta = models.DateField()

    class Meta:
        managed = False
        db_table = 'i_eta'

I tried iterating through for values from both sets, by doing:
{{ k.year }} and whatnot from the first class (worked)
and I tried
{{ k.mpn.eta }} and it didn’t work. Is there an issue considering there is an ‘mpn’ in both classes?

(Found this stackoverflow article which seemed to be relevant)

18

What object is k here?

No, because you’re referencing an attribute of an object.

I’d be extremely cautious about trusting anything from StackOverflow - especially for a question as old as the one you referenced. (Once I saw the age, I didn’t bother reading it so I have no idea what it says.)

19

Sorry, k is the iterable that I used for brevity:

{% for k in kidsquery %}
<tr>
<td>{{ k.year }}</td>
<td>{{ k.brand }}</td>
{% if k.url %}
<td><a href="{{ k.url }}" target="_blank">{{ k.desc }}</a></td>
{% else %}
<td>{{ k.desc }}</td>
{% endif %}
<td>{{ k.colour }}</td>
<td>{{ k.size }}</td>
<td>{{ k.mpn }}</td>
{% if k.mpn.eta is null %} <!-- if we have no date, suggest contacting us -->
<td>Contact Us</td>
{% else %}
<td>{{ k.mpn.eta|date:"F Y" }}</td> <!-- if we have a date, list it in month/year format -->
{% endif %}
</tr>
{% endfor %}
20

Ok, but what object is it? Your models above you identify a model i and Find_Eta, but neither corresponds to this template fragment you’ve posted.