查询中的Django-ORM连接子表

Question

我想用Django ORM。我构建SQL查询：

select itinerary.id, count(users.home_location_id) from itinerary_itinerary as itinerary left join (select to_applicationuser_id as id, users.home_location_id from custom_auth_applicationuser_friends as friends join custom_auth_applicationuser as users on friends.to_applicationuser_id = users.id where from_applicationuser_id = 28)
    as users on itinerary.location_id = users.home_location_id 
    WHERE user_id = 28 
    GROUP BY itinerary.id, users.home_location_id

有谁能告诉我如何用子查询中的表进行左连接？
28是当前用户的用户名。
我用的是：

^{pr2}$

但我搞错了

ProgrammingError relation "(select to_applicationuser_id as id, users.home_location_id fro" does not exist

升级版

模型（只是简单的方案）：

class ApplicationUser(models.Model):
    name = models.CharField(max_length=255)
    home_location = models.ForeignKey(Location)
    friends = models.ManyToManyFieled('self')

class Location(models.Model):
    loc_name = models.CharFiled(max_length=255)

class Itinerary(models.Model):
    user = models.ForeignKey(ApplicationUser)
    location = models.ForeignKey(Location)

Answer 1

当您使用extra添加表时，它们将被添加到from列表中，而from列表不接受sql语句。 我不认为您需要在这里使用extra，您可以使用ORM获得类似的查询，而不需要连接select语句。下面的代码（使用筛选和注释）将给出与我能够理解您的查询相同的结果：

ApplicationUser.objects.filter(
    Q(itinerary__location_id = F('friends__home_location_id')) |
    Q(friends__home_location__isnull=True),
    id=28,                                                    
    ).values_list(
        'itinerary__id', 'friends__home_location_id'
    ).annotate(location_count = Count('friends__home_location_id')
    ).values_list('itinerary__id', 'location_count')