# Complex SQL through the Django ORM

**URL:** <https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099>\
**Category:** Using Django\
**Created:** [December 19, 2021, 4:49am UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099 "2021-12-19T04:49:56Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 4:49am UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/1 "2021-12-19T04:49:56Z")

</div>

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?

```auto
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.

---

<div class="post-metadata">

**Author:** ![massover](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/massover/32/1792_2.png) [@massover](https://forum.djangoproject.com/u/massover)\
**Post date:** [December 19, 2021, 1:42pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/2 "2021-12-19T13:42:27Z")

</div>

1. Use [values](https://docs.djangoproject.com/en/4.0/ref/models/querysets/) to select only certain columns
2. Use [database functions](https://docs.djangoproject.com/en/4.0/ref/models/querysets/) for coalesce and date formatting
3. Use the [contains lookup](https://docs.djangoproject.com/en/4.0/ref/models/querysets/) 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?

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 2:38pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/3 "2021-12-19T14:38:40Z")

</div>

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:

```auto
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

```

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 4:55pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/4 "2021-12-19T16:55:28Z")

</div>

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')`

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 19, 2021, 4:57pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/5 "2021-12-19T16:57:56Z")

</div>

I believe you an create an [unmanaged model](https://docs.djangoproject.com/en/3.2/ref/models/options/#managed) that points to the view. This [post](https://rescale.com/using-database-views-in-django-orm/) goes a bit more into it.

> [@chrisjeanson](#):
>
> Query 2: (pulls the unlinked views successfully)  
> `query = i_Eta.objects.values('eta')`

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

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 5:00pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/6 "2021-12-19T17:00:27Z")

</div>

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'
```

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 19, 2021, 5:05pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/7 "2021-12-19T17:05:50Z")

</div>

So I think what you want is something like:

```auto
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](https://docs.djangoproject.com/en/4.0/ref/models/database-functions/#coalesce). 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.

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 5:23pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/8 "2021-12-19T17:23:38Z")

</div>

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!]

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 7:08pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/9 "2021-12-19T19:08:42Z")

</div>

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:

```auto

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:

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

```

And my template structure is:

```auto

{% 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.

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 19, 2021, 8:43pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/10 "2021-12-19T20:43:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 9:24pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/11 "2021-12-19T21:24:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 19, 2021, 10:01pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/12 "2021-12-19T22:01:53Z")

</div>

See the docs about [automatic primary keys](https://docs.djangoproject.com/en/4.0/topics/db/models/#automatic-primary-key-fields). 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.

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 11:10pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/13 "2021-12-19T23:10:21Z")

</div>

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.

```auto
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.

---

<div class="post-metadata">

**Author:** ![CodenameTim](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/codenametim/32/31507_2.png) [@CodenameTim](https://forum.djangoproject.com/u/CodenameTim)\
**Post date:** [December 19, 2021, 11:16pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/14 "2021-12-19T23:16:40Z")

</div>

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](https://docs.djangoproject.com/en/4.0/ref/models/fields/#database-representation). You’ll want to specify the [`db_column`](https://docs.djangoproject.com/en/4.0/ref/models/fields/#db-column) parameter.

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 19, 2021, 11:55pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/15 "2021-12-19T23:55:06Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 19, 2021, 11:58pm UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/16 "2021-12-19T23:58:51Z")

</div>

> [@chrisjeanson](#):
>
> Everything I have been finding has lead me to believe the following would work:
> 
> `{{ iterable.classname.value }}`
> 
> but it doesn’t

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

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 20, 2021, 12:26am UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/17 "2021-12-20T00:26:28Z")

</div>

So, in my case with:

```auto
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](https://stackoverflow.com/questions/21152677/django-select-related-how-to-display-related-table-data-in-a-template) which seemed to be relevant)

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 20, 2021, 12:30am UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/18 "2021-12-20T00:30:12Z")

</div>

> [@chrisjeanson](#):
>
> and I tried  
> {{ k.mpn.eta }} and it didn’t work.

What object is `k` here?

> [@chrisjeanson](#):
>
> Is there an issue considering there is an ‘mpn’ in both classes?

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

> [@chrisjeanson](#):
>
> (Found [this stackoverflow article](https://stackoverflow.com/questions/21152677/django-select-related-how-to-display-related-table-data-in-a-template) which seemed to be relevant)

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.)

---

<div class="post-metadata">

**Author:** ![chrisjeanson](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/chrisjeanson/32/6740_2.png) [@chrisjeanson](https://forum.djangoproject.com/u/chrisjeanson)\
**Post date:** [December 20, 2021, 12:34am UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/19 "2021-12-20T00:34:34Z")

</div>

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

```auto
{% 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 %}

```

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [December 20, 2021, 12:36am UTC](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099/20 "2021-12-20T00:36:10Z")

</div>

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.

[Next page](https://forum.djangoproject.com/t/complex-sql-through-the-django-orm/11099.md?page=2)