How do I write a Django query that uses a complex "on" clause in its inner join?

Question

I'm using Django, Python 3.7, and PostgreSQL 9.5. I have these models:

class Article(models.Model):
    ...
    label = models.TextField(default='', null=True)


class Label(models.Model):
    name = models.CharField(max_length=200)

I want to write a Django query that retrieves all the articles whose label contains a name from the Labels table. In PostGres, I can structure my query like so:

select a.* from myapp_article a join myapp_label l on a.label ilike '%' || l.name || '%';

but I have no idea how to pull this off in Django on account of the "on" clause and "ilike". How do I pull this off?

This is not how Django supposed to work. Shub show you the general Django way to join two table, there should be a common key for linking two table not by `like`. If you have reason don't want to link two table by foreign key, then use `Manager.raw()`. — MT-FreeHK, Mar 21 '19 at 03:14
Definitely DON'T use `.raw` - this is really simple to do with the ORM tools Django gives you — Robin Zigmond, Mar 23 '19 at 21:47
You have no relationship between Article and Label in your models. Your SQL is also without a relations. I don't believe that you tried that it works how you expect. Your question is also completely unclear what you want to get. — hynekcer, Mar 25 '19 at 19:56

Mehak · Answer 1 · 2019-03-28T10:52:30.863

If you've to do a case insensitive search on Article's label for matching names, then you can use regex and pass it a flat list of all the label names like so:

Article.objects.filter(label__iregex=r'(' + '|'.join(Label.objects.all().values_list('name', flat=True)) + ')')

What the above query does is, it makes a flat list of labels:

['label1' , 'label2', 'label3']

and then the string is joined like this:

'(label1|label2|label3)'

and a similar SQL query is used:

SELECT * from FROM "app_article" WHERE "app_article"."label" ~* (label1|label2|label3)

Otherwise, for case sensitive approach, you can use this:

names_list = Label.objects.all().values_list('name', flat=True)
Article.objects.filter(label__in=names_list)

score 0 · Answer 2 · answered Mar 21 '19 at 02:15

0

In your class Article you will have to declare label as foreignkey to class Label

class Article(models.Model):
    ...
    label = models.ForeignKey(Label, default='', on_delete=models.CASCADE)

And then you can access it.

answered Mar 21 '19 at 02:15

Banks

179
13

Hi, Thanks but that does not accurately describe the relationship between the models (i.e. there is no LABEL_ID field in my myapp_article table). That's what makes this quesiton so hard. – Dave Mar 21 '19 at 14:41
@Dave it doesn't matter that there is no such field at the moment, if you update the model to add the necessary `ForeignKey` then run the `makemigrations` and `migrate` commands, the column will get added automatically. In Django, you should only ever think about Model instances (objects), and let Django take care of the raw details of tables/columns/joins etc. for you :) – Robin Zigmond Mar 23 '19 at 21:48

score 0 · Accepted Answer · answered Mar 24 '19 at 12:22

This wouldn't translate into same SQL query, but would yield the same results, using an inner query.

inner_query = Label.objects.annotate(article_label=OuterRef('label')).filter(article_label__icontains=F('name'))
articles = Article.objects.annotate(labels=Subquery(inner_query.values('name')[:1])).filter(labels__isnull=False)

This should roughly should translate to this:

select a.* from myapp_article a where exists (select l.* from myapp_label l where a.label ilike '%' || l.name || '%')

But due to a current issue in Django regarding using OuterRef's in annotations, this approach doesn't work. We need to use a workaround suggested here until the issue is fixed to make this query work, like this:

Define a custom expression first

class RawCol(Expression):

    def __init__(self, model, field_name):
        field = model._meta.get_field(field_name)
        self.table = model._meta.db_table
        self.column = field.column
        super().__init__(output_field=CharField())

    def as_sql(self, compiler, connection):
        sql = f'"{self.table}"."{self.column}"'
        return sql, []

Then build your query using this expression

articles = Article.objects.all().annotate(
    labels=Subquery(
        Label.objects.all().annotate(
            article_label=RawCol(Article, 'label')
        ).filter(article_label__icontains=F('name')).values('name')[:1]
    )
).filter(labels__isnull=False)

This should return instances of Article model whose label field contain a value from the name field of Label model

Thanks for this thorough answer. As I'm fairly new to Django and its conventions, what path/file name does the "RawCol" class go into? — Dave, Mar 25 '19 at 01:38
It depends on your project structure, but as this is a general tool that could be used anywhere in the application, it could go into a file called model_utils.py inside main application (next to settings.py). For me, I create an application called "common" in every Django project, and place these kind of files there. — Ozgur Akcali, Mar 25 '19 at 06:24
I went with your suggestion about creating a separate application, "common." But I'm having another issue. I get a "NameError: name 'Expression' is not defined" error on the line, "class RawCol(Expression)". Where is that word "Expression" coming from? — Dave, Mar 26 '19 at 20:21
You would also need to import CharField, you can import both like this: from django.db.models import Expression, CharField — Ozgur Akcali, Mar 26 '19 at 20:48

How do I write a Django query that uses a complex "on" clause in its inner join?

3 Answers3