django 中以合理的方式将新表添加到查询集中的内部函数是什么?
在 django 1.2 中:
我有一个带有额外参数的查询集,该参数引用当前未包含在 django 为此查询集生成的查询中的表。
如果我将 order_by 添加到引用另一个表的查询集中,django 将以正确的方式添加到另一个表的联接,并且额外的工作。但如果没有 order_by,额外的参数就会失败。我可以在另一个表中添加一个无用的辅助 order_by ,但我认为应该有更好的方法来做到这一点。
以合理的方式添加连接的 django 函数是什么?我知道这一定是在某个地方被调用的。
这是一些示例代码。它选择给定用户的所有读数,并用存储在“朋友”中的另一个用户给出的评级(如果有)来注释结果。
class Book(models.Model):
name = models.CharField(max_length=200)
urlname = models.CharField(max_length=200)
entrydate=models.DateTimeField(auto_now_add=True)
class Reading(models.Model):
book=models.ForeignKey(Book,related_name='readings')
user=models.ForeignKey(User)
rating=models.IntegerField()
entrydate=models.DateTimeField(auto_now_add=True)
readings=Reading.objects.filter(user=user).order_by('entrydate')
friendrating='(select rating from proj_reading where user_id=%d and \
book_id=proj_book.id and rating in (1,2,3,4,5,6))'%friend.id
readings=readings.extra(select={'friendrating':friendrating})
目前,读数不起作用,因为读数的连接设置不正确。但是,如果我添加一个订单,例如:
.order_by('entrydate','reading__entrydate')
django 神奇地知道通过外键添加内部连接,我就得到了我想要的。
附加信息:
print readings.query ==>
select ((select rating from proj_reading where user_id=2 and book_id=proj_book.id and rating in (1,2,3,4,5,6)) as 'hisrating', proj_reading.id, proj_reading.user_id, proj_reading.rating, proj_reading.entrydate from proj_reading where proj_reading.user_id=1;
假设 用户.id=1 friend.id=2
错误是:
OperationalError: Unknown column proj_book.id in 'where clause'
发生这种情况是因为表 proj_book 未包含在查询中。重申一下我上面所说的 - 如果我现在执行 Readings2=readings.order_by('book__entrydate') 我可以看到正确的连接已设置并且查询有效。
理想情况下,我只想弄清楚 qs.query 函数的名称是什么,该函数查看两个表并找出它们如何通过外键连接,然后手动调用它。
In django 1.2:
I have a queryset with an extra parameter which refers to a table which is not currently included in the query django generates for this queryset.
If I add an order_by to the queryset which refers to the other table, django adds joins to the other table in the proper way and the extra works. But without the order_by, the extra parameter is failing. I could just add a useless secondary order_by to something in the other table, but I think there should be a better way to do it.
What is the django function to add joins in a sensible way? I know this must be getting called somewhere.
Here is some sample code. It selects all readings for a given user, and annotates the results with the rating (if any) given by another user stored in 'friend'.
class Book(models.Model):
name = models.CharField(max_length=200)
urlname = models.CharField(max_length=200)
entrydate=models.DateTimeField(auto_now_add=True)
class Reading(models.Model):
book=models.ForeignKey(Book,related_name='readings')
user=models.ForeignKey(User)
rating=models.IntegerField()
entrydate=models.DateTimeField(auto_now_add=True)
readings=Reading.objects.filter(user=user).order_by('entrydate')
friendrating='(select rating from proj_reading where user_id=%d and \
book_id=proj_book.id and rating in (1,2,3,4,5,6))'%friend.id
readings=readings.extra(select={'friendrating':friendrating})
at the moment, readings won't work because the join to readings is not set up correctly. however, if I add an order by such as:
.order_by('entrydate','reading__entrydate')
django magically knows to add an inner join through the foreign key and I get what I want.
additional information:
print readings.query ==>
select ((select rating from proj_reading where user_id=2 and book_id=proj_book.id and rating in (1,2,3,4,5,6)) as 'hisrating', proj_reading.id, proj_reading.user_id, proj_reading.rating, proj_reading.entrydate from proj_reading where proj_reading.user_id=1;
assuming
user.id=1
friend.id=2
the error is:
OperationalError: Unknown column proj_book.id in 'where clause'
and it happens because the table proj_book is not included in the query. To restate what I said above - if I now do readings2=readings.order_by('book__entrydate') I can see the proper join is set up and the query works.
Ideally I'd just like to figure out what the name of the qs.query function is that looks at two tables and figures out how they are joined by foreign keys, and just call that manually.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(2)
您生成的查询:
select ((select rating from proj_reading where user_id=2 and book_id=proj_book.id and rating in (1,2,3,4,5,6)) as 'his rating', proj_reading.id、proj_reading.user_id、proj_reading. rating、proj_reading.entrydate from proj_reading where proj_reading.user_id=1;
数据库无法理解 proj_book 的含义,因为它不包含在(来自表中)或内部联接)。
当您添加 order_by 时,您将获得预期结果。 ,因为 order_by 查询在 proj_book 和 proj_reading 之间添加内部联接。
据我所知,如果您这样做 。参考 Book 中的任何其他列,而不仅仅是 order_by,您将得到类似的结果
这样,在步骤 Q1 中,您将强制 DJango 在 Book 表上添加联接,这不是默认值,除非您访问 Book 表的任何字段。
Your generated query:
select ((select rating from proj_reading where user_id=2 and book_id=proj_book.id and rating in (1,2,3,4,5,6)) as 'hisrating', proj_reading.id, proj_reading.user_id, proj_reading.rating, proj_reading.entrydate from proj_reading where proj_reading.user_id=1;
The db has no way to understand what does it mean by proj_book, since it is not included in (from tables or inner join).
You are getting expected results, when you add order_by, because that order_by query is adding inner join between proj_book and proj_reading.
As far as I understand, if you refer any other column in Book, not just order_by, you will get similar results.
This way, at step Q1, you are forcing DJango to add join on Book table, which is not default, unless you access any field of Book table.
你的意思是:
你可以使用“__”来创建表连接。
编辑:
它不会工作,因为您没有正确定义表连接。
这是一个 pesdocode,未经测试。
但是,我建议您使用 django 过滤器而不是编写 sql 查询。
文档了解更多详细信息。
you mean:
You can use "__" to create table joins.
EDIT:
It wont work because you do not define table join correctly.
This is a pesdocode and it is not tested.
But, i advice you to use django filters instead of writing sql queries.
Documentation for more details.