filter dataset not having a value

Question

Suppose i have a schema like below :

Book | Author
-------------
 B1  |   A1
 B1  |   A3
 B1  |   A2

 B2  |   A5 
 B2  |   A4
 B2  |   A3

 B3  |   A5
 B3  |   A6
 B3  |   A1

 B4  |   A1
 B4  |   A5
 B4  |   A6

with below model:

class Books(models.Model):
    author = models.CharField(
        max_length=100,
        blank=False,
        null=False,
        db_index=True,
        verbose_name=_('authors'),
    )
    book = models.CharField(
        max_length=50,
        blank=False,
        null=False,
        db_index=True,
        verbose_name=_('book'),
    )

    book_type = models.CharField(
        max_length=50,
        blank=False,
        null=False,
        default="regular"
    )

    flavour = models.CharField(
        max_length=10,
        blank=False,
        null=False,
        verbose_name=_('flavour'),
    )
    cost = models.IntegerField()
    status = models.CharField(
        max_length=100,
        null=True,
        blank=True,
        db_index=True,
    )

and i want to filter out all those book whose author is not A1 (B2 in this case)

This is simple SQL using group by and not having clause, what i am having tough time is getting it done usinng django queryset and annotate. Most of the annotate work around count which i am not able to fit in this particular case.

any pointers is helpful, thanks! :)

Answer 1

This should do what you want:

Books.objects.exclude(author='A1')

If you want a distinct list of book values then you can do:

Books.objects.exclude(author='A1').values_list('book').distinct()

Your Books model looks a little strange to me though. Not sure what your specific use case is, but it seems that a Book model with a ManyToMany relationship to an Author might be more appropriate.