0

I am unable to figure out how to retriever objects using an advanced Q search any perhaps am approaching it in the wrong way entirely.

I think it'll be easier for me to fully define the models before I attempt to describe the problem. The problem I am trying to solve is slightly more complex than the example I am going to give here, but that is because I want to give a clearer picture of what I'm trying to do.

I have two models here:

class Tag(models.Model):
     name = models.CharField()

class Container(models.Model):
    tags = models.ManyToManyField(Tag)

I'm able to retrieve a list of tags that I want:

valid_tags = [Tag(x) for x in ['a', 'b', 'c', 'd']]

And here would be an example of some containers:

containers = [
    Container(tags=[Tag(x) for x in ['a', 'b']),
    Container(tags=[Tag(x) for x in ['b']),
    Container(tags=[Tag(x) for x in ['a', 'b', 'c']),
    Container(tags=[Tag(x) for x in ['e']),
    Container(tags=[Tag(x) for x in ['a', 'e']),
]

What I want the output of the query that I am trying to write is this:

valid_containers = [
    Container(tags=[Tag(x) for x in ['a', 'b']),
    Container(tags=[Tag(x) for x in ['b']),
    Container(tags=[Tag(x) for x in ['a', 'b', 'c']),
]

So, essentially what I am trying to do is an "inner join" type operation where it will return containers that all tags are in the valid_tags.

If I run the following code, it returns the following output, which contains Containers that have tags that do not satisfy the conditions.

Container.objects.filter(tags__in=valid_tags)
[
    Container(tags=[Tag(x) for x in ['a', 'b']),
    Container(tags=[Tag(x) for x in ['b']),
    Container(tags=[Tag(x) for x in ['a', 'b', 'c']),
    Container(tags=[Tag(x) for x in ['a', 'e']),  # Not wanted
]

I've also tried with advanced Q objects, something akin to .filter(tags__in=valid_tags).exclude(~Q(tags__in=valid_tags)), however that just returns the entire list of containers, which I do not want.

I'm not even sure what the query I'm trying to achieve here would look like in SQL, so I am unable to write a raw query that would return the proper response and I would also like to avoid writing raw SQL, if possible. I'm not opposed to it, but I'd like to be able to use Django's built in DB methods.

Also, not sure if this is relevant, but I am running my code against Django 1.11, and am unable to upgrade to Django 2; if the feature I'm looking for would help make this work is in Django 2 but not Django 1.11 I'm unsure if I'd be able to upgrade so I might have to figure out the SQL query.

Edit:

I've found out that annotate supports Q queries. So I then attempted to annotate the count of either a) tags that are in the container that are valid or b) tags that are in the container that are not valid. My hopes were to be able to filter out anything in the case of (b) by just doing .filter(bad_tag_count=0), but the code I have below appears to output just the amount of tags in the object:

containers \
    .annotate(
        valid_tags=Count(Q(tags__in=valid_tags))
    ).annotate(
        bad_tags=Count(~Q(tags__in=valid_tags)
    )

The problem is that both .valid_tags and .bad_tags just output the count of tags contained in the container. I'm really at a loss here as far as what I'm doing wrong with this query.

Polosky
  • 88
  • 5
  • 13
  • https://stackoverflow.com/a/11025600/2897115. I am not sure, but i think .filter(tags__in=valid_tags).exclude(~Q(tags__in=valid_tags)) w.r.t the above link can help. – Santhosh Sep 06 '18 at 18:42
  • That's what I thought would work as well, but unfortunately it just returns as if I had ran .all() instead. – Polosky Sep 06 '18 at 19:00

1 Answers1

0

As it turns out, I realized that I was indeed approaching the problem from the wrong angle. My goal is to find all containers that have tags that satisfy the valid tags condition.

The solution is simple:

non_valid_tags = Tag.objects.exclude(id__in=valid_tags)  # Apparently Django knows that if the query passed in is based on the object here, it will use the same field that is being queried so I don't need to do valid_tags.values_list('id', flat=True), although that might be a point to consider if the query ends up running for too long
valid_containers = Containers.objects.exclude(tags__in=non_valid_tags)  # rows corresponding to [0, 1, 2]

While this isn't the full issue that I need to address, this gives me the starting point to flesh out the query.

Polosky
  • 88
  • 5
  • 13