limiting __in lookup in django

user_14822711 0
Viewed 44

i have are question about "__in" lookup in Django ORM.

So here is the example of code:

tags = [Tag.objects.get(name="sometag")]
servers = Server.objects.filter(tags__in=tags)[offset_from:offset_to]
server_infos = []
for server in servers:
    server_infos.append(query.last())

So here is the problem: we making about 60-70 sql requests for each server. But i want to do something like this:

tags = [Tag.objects.get(name="sometag")]
servers = Server.objects.filter(tags__in=tags)[offset_from:offset_to]
server_infos = ServerInfo.objects.filter(contains__in=servers)
assert servers.count() == server_infos.count()

Can i do this without raw sql request? All i need to understand is how to limit "__in" expression in Django to get only last value as in example above. Is it possible?

Update, my models:


class Tag(models.Model):
    
    name = models.CharField(max_length=255, blank=True)

    added_at = models.DateTimeField(auto_now_add=True, null=True)

    
    def __str__(self):
        return self.name


class Server(models.Model):
    
    ip = models.CharField(max_length=255, blank=True)
    port = models.IntegerField(blank=True)
    
    name = models.CharField(max_length=255, blank=True)
    tags = models.ManyToManyField(Tag)    
    
    added_at = models.DateTimeField(auto_now_add=True, null=True)

    
    def __str__(self):
        return self.name
    
    def get_server_online(self):
        query = ServerInfo.objects.filter(contains=self)
        if query.exists():
            return query.last().online
        return 0
    
    
class ServerInfo(models.Model):
    
    contains = models.ForeignKey(Server, \
        on_delete=models.CASCADE, null=True, blank=True)
    
    map = models.CharField(max_length=255, blank=True)
    game = models.CharField(max_length=255, blank=True)
    online = models.IntegerField(null=True)
    max_players = models.IntegerField(null=True)
    
    outdated = models.BooleanField(default=False)
    tags = models.ManyToManyField(Tag)
    
    ping = models.IntegerField(null=True)
    
    
    def __str__(self):
        
        return f"Current map {self.map} and current online {self.online}/{self.max_players}"
orm django-orm django-models django
2 Answers

I think the problem is that tags is ManyToMany and a server may be selected twice by two different tags. Also that a server may have >1 serverinfos, because it's a ForeignKey relation not a OneToOne.

Possibilities:

Make sure a server is returned only once in the queryset:

servers = Server.objects.filter(tags__in=tags).distinct()[offset_from:offset_to]

(or distinct('pk') ?)

Make sure only one ServerInfo instance is returned per server:

server_infos = ServerInfo.objects.filter(contains__in=servers).distinct('contains')

Or use prefetch_related in the Servers query, and then avoid subsequent queries by always referring to the serverinfo objects through the related name (default "serverinfo_set")

I absolutely hate "magic" default related names, and would always code one explicitly: contains = models.ForeignKey(Server, ..., related_name='server_infos', ...)

servers = Server.objects.filter(tags__in=tags).distinct(
    ).prefetch_related('serverinfo')[offset_from:offset_to]

for server in servers:
    server_info = server.serverinfo_set.first() 

    # or
    for info in server.serverinfo_set:

NB don't start applying filters to serverinfo_set if you don't want to hit the DB N times. Filter by iterating through what is presumably a short list, and which is already in memory in the queryset anyway.

user_52469060

The following should retrieve the data using two queries, one to get all the servers and the second one to get all the server infos using prefetch_related(). When filtering on a ManyToManyField you have to use distinct() to avoid duplicate results.

servers = Server.objects.filter(
    tags__name="sometag").distinct().prefetch_related("server_info_set")

for server in servers:
    print(server.name)
    for info in server.server_info_set.all():
        print(info)

If you only want to retrieve one certain info per server you would have to provide a custom queryset to prefetch_related() using Prefetch() objects.

from django.db.models import Prefetch

servers = Server.objects.filter(       
 tags__name="sometag").distinct().prefetch_related(Prefetch('server_info_set', 
        queryset=ServerInfo.objects.filter(outdated=False), 
        to_attr="current_infos"
    )
)

for server in servers:
    print(server.name)
    for info in server.current_infos.all():
        print(info)
user_1839100
Related