CODEWITHSUNDEEP
djangodjango-orm
bisamov
bisamov

Reputation: 730

Filter based on foreign key

So i was working on this problem for many days already and can't make it work. Basically I have three models

class Year(models.Model):
    year = models.CharField()


class Book(models.Model):
     name = models.CharField(
        verbose_name = "Library Name",
        max_length = 255
    )

    author = models.ForeignKey(
        Author,
        on_delete=models.CASCADE
    )

    year = models.ForeignKey(
        Year,
        on_delete=models.CASCADE
    )

class Author(models.Model):
    name = models.CharField(
        verbose_name = "Author's Name",
        max_length = 255
    )

    num = models.CharField(
        max_length = 255,
        default=0
    )

For example, If I pass 2018 i want retrieve all the Authors who published book in 2018. I tried different queries like Year.objects.filter(year=2018).filter()

and don't know how to filter the rest

Upvotes: 2

Views: 69

Answers (2)

HuLu ViCa
HuLu ViCa

Reputation: 5452

I can see that year is a CharField, so you must use quotes to make the query, and you must use a lookup (year__year) to query for a field in a related model.

authors = Book.objects.filter(year__year='2018').values('author').distinct()

Upvotes: 1

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 476594

You can filter on the content of a related model, by using double underscores ('__'), so you can filter on the year attribute of the year relation of a Book of that Author:

Author.objects.filter(book__year__year='2018').distinct()

The .distinct() is useful, since otherwise Authors who published multiple books in 2018, will occur multiple times in the queryset.

That being said, constructing a Year object here, seems rather strange, and making it a CharField is even more strange (since sorting by year will result in sorting *lexicographically). The num field in the Author model, probably should be an IntegerField [Django-doc] as well.

A better modeling might be:

class Book(models.Model):
     name = models.CharField(
        verbose_name = "Library Name",
        max_length = 255
    )
    author = models.ForeignKey(
        Author,
        on_delete=models.CASCADE
    )
    year = models.IntegerField()

class Author(models.Model):
    name = models.CharField(
        verbose_name = "Author's Name",
        max_length = 255
    )
    num = models.IntegerField(default=0)

In that case we can query for Authors who published a book in 2018 with:

Author.objects.filter(book__year=2018).distinct()

It is however still unclear what num is doing here. If it holds the number of books, then using an .annotate(..) might be a better option, since it avoids data duplication.

Upvotes: 2

Related Questions