Django 左连接

发布于 2024-07-17 05:18:24 字数 1009 浏览 11 评论 0原文

我有一些左连接问题。我有以下模型,

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

我需要获取所有服务器实例并将 CommandInfo 左连接到它(如果有)。

现在我正在用原始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()

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()

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

忆悲凉 2024-07-24 05:18:24

您可以使用如下代码:

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

这将返回所有将 s 设置为外键的 CommandInfo 对象的列表。

您可以在 Django 文档中获取更多信息,“向后跟踪关系”。

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

儭儭莪哋寶赑 2024-07-24 05:18:24

有时 Django ORM 需要使用 select_lated() 显式命名左连接字段名称。

这只是我的想法,所以你可能需要调整它,但尝试如下:

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

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')
一绘本一梦想 2024-07-24 05:18:24
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, [])

请注意,您需要获取服务器列表,因为您可以使用没有 CommandInfo 的服务器

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

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文