Django 中的外连接建模

发布于 2024-07-29 22:54:32 字数 676 浏览 4 评论 0原文

我有一个多对多关系表，其中连接基础中的一些数据

是我的模型的基本版本，如下所示：

class FooLine(models.Model):
    name = models.CharField(max_length=255)

class FooCol(models.Model):
    name = models.CharField(max_length=255)

class FooVal(models.Model):
    value = models.CharField(max_length=255)
    line = models.ForeignKey(FooLine)
    col = models.ForeignKey(FooCol)

我试图搜索特定行的每个值，如果该值不存在，则为空（基本上我是尝试显示 fooval 表，其中未填充的值为空值）典型的 sql 有

SELECT value FROM FooCol LEFT OUTER JOIN 
  (FooVal JOIN FooLine 
  ON FooVal.line_id == FooLine.id AND FooLine.name = "FIXME") 
ON FooCol.id = col_id;

没有办法使用 django 模型对上述查询进行建模

谢谢

原文

I have a many to many relationship table whith some datas in the jointing base

a basic version of my model look like:

class FooLine(models.Model):
    name = models.CharField(max_length=255)

class FooCol(models.Model):
    name = models.CharField(max_length=255)

class FooVal(models.Model):
    value = models.CharField(max_length=255)
    line = models.ForeignKey(FooLine)
    col = models.ForeignKey(FooCol)

I'm trying to search every values for a certain line with a null if the value is not present (basically i'm trying to display the fooval table with null values for values that haven't been filled)
a typical sql would be

SELECT value FROM FooCol LEFT OUTER JOIN 
  (FooVal JOIN FooLine 
  ON FooVal.line_id == FooLine.id AND FooLine.name = "FIXME") 
ON FooCol.id = col_id;

Is there any way to modelise above query using django model

Thanks

分享到QQ

分享到微博

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

发布评论

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

最美不过初阳 2024-08-05 22:54:33

外连接可以被视为一种 hack，因为 SQL 缺乏“导航”。

您所拥有的是一个简单的 if 语句情况。

for line in someRangeOfLines:
    for col in someRangeOfCols:
        try:
            cell= FooVal.objects().get( col = col, line = line )
        except FooVal.DoesNotExist:
            cell= None

这就是外连接的真正含义——尝试使用 NULL 替换进行查找。

唯一的优化如下所示。

matrix = {}
for f in FooVal.objects().all():
    matrix[(f.line,f.col)] = f

for line in someRangeOfLines:
    for col in someRangeOfCols:
        cell= matrix.get((line,col),None)

Outer joins can be viewed as a hack because SQL lacks "navigation".

What you have is a simple if-statement situation.

for line in someRangeOfLines:
    for col in someRangeOfCols:
        try:
            cell= FooVal.objects().get( col = col, line = line )
        except FooVal.DoesNotExist:
            cell= None

That's what an outer join really is -- an attempted lookup with a NULL replacement.

The only optimization is something like the following.

matrix = {}
for f in FooVal.objects().all():
    matrix[(f.line,f.col)] = f

for line in someRangeOfLines:
    for col in someRangeOfCols:
        cell= matrix.get((line,col),None)

回复收藏 0 原文

~没有更多了~