CODEWITHSUNDEEP
djangoleft-join
Mike
Mike

Reputation: 7841

django left join

I have a bit of a left join issue.. I have the following models

class CommandInfo(models.Model):
    server = models.ForeignKey(Server)
    count = models.IntegerField(default=1)
    ts = models.DateTimeField(auto_now=True)

class Server(models.Model):
    name = models.CharField(max_length=100)
    group = models.ForeignKey(ApplicationGroup, blank=True, default=0)
    host = models.CharField(max_length=100)
    ip = models.IPAddressField(db_index=True)
    about = models.TextField()
    firstTS = models.DateTimeField(auto_now_add=True)
    lastTS = models.DateTimeField(auto_now=True)
    processed = models.SmallIntegerField(max_length=1, default=0)

    def __unicode__(self):
        return self.host

I need to grab all the server instances and left join the CommandInfo to it if there is one.

Right now I'm doing it in raw sql

from django.db import connection
cursor = connection.cursor()
cursor.execute("SELECT host,ts,count as host FROM servers_server LEFT JOIN cmds_commandinfo ON server_id=servers_server.id")
servers = cursor.fetchall()

Upvotes: 1

Views: 4143

Answers (3)

Alexei Orishchenko
Alexei Orishchenko

Reputation: 31

commands_by_server_id = defaultdict(list)
for c in CommandInfo.objects.select_related('server'):
  commands_by_server_id[c.server.id].append(c)

servers = Server.objects.all()
for s in servers:
  s.commands = commands_by_server_id.get(s.id, [])

Please note that you need to get the servers list due you can servers without CommandInfo

Upvotes: 0

Van Gale
Van Gale

Reputation: 43912

Sometimes the Django ORM needs the left join field name to be explicitly name with select_related().

This is just off the top of my head so you'll probably need to tweak it, but try something like:

s = Server.objects.select_related('commandinfo_set')

Upvotes: 0

mipadi
mipadi

Reputation: 410722

You can use code like the following:

s = Server.objects.get(id=1)
cmdinfo = s.commandinfo_set.all()

Which would return a list of all CommandInfo objects that have s set as the foreign key.

You can get more info at the Django docs, "Following Relationships Backward".

Upvotes: 2

Related Questions