Adding extra field to find whether the object is related to another one

Question

I am trying to build an extra field in a Django query to specify whether an article belongs to a Journal.

I am trying to do something like Article.objects.filter(title__icontains='foo').extra(select={in_the_journal:Function_Belongs_To_Journal(journal_id)})

I am currently iterating the search results to find that out but I'd rather like to retrieve this info from the database already

My models.py are as follows:

class Article(models.Model):
    title               = models.CharField(max_length=400)
    content             = models.TextField()
    date                = models.DateTimeField('date published', null=True, default = timezone.now())


class Journal(models.Model):
    name          = models.CharField(max_length=200)
    authors       = models.ManyToManyField(Author, null=True, blank = True)
    articles      = models.ManyToManyField(Article, null=True, blank = True)

Is that a way to add an extra field to indicate that? Either using extra or annotate Django tags?

ndpu · Answer 1 · 2014-01-24T20:50:53.217

1

Just use reverse relationship:

Article.objects.filter(title__icontains='foo', journal__pk=journal_id)

update to comment: if you need all articles and you want to know which of them have journals or not, use RelatedManger:

articles = Article.objects.filter(title__icontains='foo')
for a in articles:
    journals = a.journal_set.all()
    if journals:
        # article have journals
        # list of Journal objects is in journals
    else:
        # article have not journals

Of course you can put this loop in template:

{% for a in articles %}
    {% if a.journal_set.all %}
         color1
    {% else %}
         color2
    {% endif %}
{% endfor %}

edited Jan 24 '14 at 20:50

answered Jan 24 '14 at 20:09

ndpu

22,225
6
54
69

But I don't want to have just the related objects. I'd like to retrieve both, related and unrelated but with a flag to specify that. The flag will be used to show results in different colors. – marcelosalloum Jan 24 '14 at 20:33
We would need some more info on how you plan to use this. Since an article can belong to many journals, are you planning on having a color for every one? Are you planning on displaying all the colors? If so, you could assign a `display_color` to each `Journal`, then loop through each `article.journal_set` and get the `journal.display_color`. – Rob L Jan 24 '14 at 20:47

score 0 · Accepted Answer · answered Jan 29 '14 at 13:41

I found a great solution to avoid iterating over a list. This way, the Database retrieves the result with an extra column.

Performance is much better now, check it out:

journal_id = 10
query = 'SELECT COUNT(*) FROM data_journal_articles as data WHERE data.journal_id = %s AND data.article_id = data_article.id' % journal_id
articles = Article.objects.filter(
    date__gte = request_data['start_date'], 
    date__lte = request_data['end_date'], 
    ).extra(select={'is_checked': query})

The extra field is a counter of relations between a given journal and a given author. This result could be either one or zero. Depending on it, result will be shown differently in the front-end.

Cheers,

Adding extra field to find whether the object is related to another one

2 Answers2