limiting __in lookup in django-CodePudding

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}"

CodePudding user response：

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.

CodePudding user response：

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)