使用JOIN表达式将查询转换为Django ORM的通用关系 [英] Translating query with JOIN expressions and a generic relation to Django ORM

问题描述

manager = models.ForeignKey（User，on_delete = models.CASCADE）
＃ ...

class Event（models.Model）：
business = models.ForeignKey（Business，on_delete = models.CASCADE）

text = models.TextField （）
when = models.DateTimeField（）
likes = GenericRelation（'Like'）

类喜欢（models.Model）：
person = models.ForeignKey （User，on_delete = models.CASCADE）

content_type = models.ForeignKey（ContentType）
object_id = models.PositiveIntegerField（）
content_object = GenericForeignKey（'content_type'，'object_id '）

date = models.DateTimeField（auto_now = True）

我在 models.py 中有这个结构。以下是重要模型的解释：

事件模型具有business字段，该字段链接到某个Business对象，该对象进一步具有manager字段。此外，事件模型具有描述事件发生日期的when字段。

另一方面，Like模型具有可以链接到某个Event对象的通用外键字段，并且个人和日期字段，描述谁给予了喜好，何时被给予该事件。

目标是在用户页面上显示所有喜欢的活动，用户以及该用户所管理的所有事件。可以使用此SQL命令简单地完成：

  SELECT事件* 
 FROM事件
 INNER JOIN 
 business 
 ON（event.business_id = business.id）
 LEFT JOIN 
'like'
 ON（event.id = object_id AND content_type_id = 17）
 WHERE（'like'.person_id = 1 OR business.manager_id = 1）; 
  

但是现在结果必须排序，已经在Like和when中提到了date在事件模型中。排序行为应如下所示：如果Event对象来自Like对象，那么它应该在该对象中按date排序，在其他情况下，它应该在Event中按when排序 - 这是事件改变的位置。因此，最后的原始查询如下所示：

  SELECT事件* 
 FROM事件
 INNER JOIN 
 business 
 ON（event.business_id = business.id AND business.manager_id = 1）
 LEFT JOIN 
'like'
 ON（event.id = object_id AND content_type_id = 17 AND person_id = 1）
 ORDER BY COALESCE（'like'.date，event.'when'）DESC; 
  

我现在把上一个查询转换成Django ORM，但是我完全迷失了部分。有人可以帮我吗感谢提前！

解决方案

在另一天的挣扎之后，我终于解决了。虽然它不是从上面产生相同的查询，并且效率不高（因为它是在查询事件中选择所有喜欢的），但它似乎是在ORM中唯一可行的方法：

  from django.db.models import Case，When，F 
 
 Event.objects.filter（\\ \\ 
 Q（business__manager = person）| \ 
 Q（likes__person = person））\ 
 .order_by（\ 
 Case（\ 
 When（ ？___ 
 
 
 default = F（'when'））
 .desc（））
  

这是它产生的SQL：

  SELECT事件* 
 FROM事件
 INNER JOIN 
 business 
 ON（event.business_id = business.id）
 LEFT OUTER JOIN 
'like'
 ON（event.id = object_id AND content_type_id = 17）
 WHERE（business.manager_id = 2 OR'like'.person _id = 2）
 ORDER BY CASE 
 WHEN'like'.person_id = 2 THEN'like'.date 
 ELSE event.'when'
 END DESC; 
  

class Business(models.Model):
    manager = models.ForeignKey(User, on_delete=models.CASCADE)
    #...

class Event(models.Model):
    business = models.ForeignKey(Business, on_delete=models.CASCADE)

    text = models.TextField()
    when = models.DateTimeField()
    likes = GenericRelation('Like')

class Like(models.Model):
    person = models.ForeignKey(User, on_delete=models.CASCADE)

    content_type = models.ForeignKey(ContentType)
    object_id = models.PositiveIntegerField()
    content_object = GenericForeignKey('content_type', 'object_id')

    date = models.DateTimeField(auto_now=True)

So I have this structure in models.py. Here's an explanation of important models:
Event model has "business" field which links to the certain Business object, which further has "manager" field. Also, Event model has "when" field which describes the date when an event will occur.
On the other side, Like model has generic foreign key field which can link to the certain Event object, and also "person" and "date" fields which describe who gave like and when it was given to that event.

The goal is now to display on user page all liked events by targeted user, and all events which manager is that user. It can be simply done with this SQL command:

SELECT event.*
FROM event
INNER JOIN 
     business
     ON (event.business_id = business.id)
LEFT JOIN
    'like'
    ON (event.id = object_id AND content_type_id = 17)
WHERE ('like'.person_id = 1 OR business.manager_id = 1);

But now the results have to be sorted, by already mentioned "date" in Like and "when" in Event model. The sorting behavior should be as follows: If the Event object derives from Like object then it should be sorted by "date" in that Like object, in other case it should be sorted by "when" in Event - this is where the things change. Therefore, here's how the final raw query looks like:

SELECT event.*
FROM event
INNER JOIN
    business
    ON (event.business_id = business.id AND business.manager_id = 1)
LEFT JOIN
    'like'
    ON (event.id = object_id AND content_type_id = 17 AND person_id = 1)
ORDER BY COALESCE('like'.date, event.'when') DESC;

And I have now to translate that last query to Django ORM, but I'm completely lost on that part. Can anyone help me? Thanks in advance!

解决方案

After another day of struggling, I've finally solved it. Although it does not produce the same query from above and is not efficient like it (because it's selecting all likes on the queried event), it seems like it's the only good way of doing it in ORM:

from django.db.models import Case, When, F

Event.objects.filter( \
        Q(business__manager=person) | \
        Q(likes__person=person)) \
    .order_by( \
        Case( \
            When(likes__person=person, then=F('likes__date')), \
            default=F('when')) \
    .desc())

Here's what SQL it produces:

SELECT event.*
FROM event
INNER JOIN
    business
    ON (event.business_id = business.id)
LEFT OUTER JOIN
    'like'
    ON (event.id = object_id AND content_type_id = 17)
WHERE (business.manager_id = 2 OR 'like'.person_id = 2)
ORDER BY CASE
    WHEN 'like'.person_id = 2 THEN 'like'.date
    ELSE event.'when'
END DESC;

这篇关于使用JOIN表达式将查询转换为Django ORM的通用关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！