python之Django 高级查询

我正在使用 Django ORM 来处理我的数据库查询。我有以下数据库表:

• 资源
• 资源池
• 资源池元素
• 预订

以及以下模型:

class Resource(models.Model): 
 
    name = models.CharField(max_length=200) 
 
class Reservation(models.Model): 
    pass 
 
class ResourcePool(models.Model): 
 
    reservation = models.ForeignKey(Reservation, related_name="pools", db_column="reservation") 
    resources = models.ManyToManyField(Resource, through="ResourcePoolElem") 
    mode = models.IntegerField() 
 
class ResourcePoolElem(models.Model): 
 
    resPool = models.ForeignKey(ResourcePool) 
    resource = models.ForeignKey(Resource) 

目前，我需要查询一组预订中使用的资源。我使用以下查询:

resourcesNames = [] 
reservations = [] 
resources = models.Resource.objects.filter( 
    name__in=resourcesNames, resPool__reservation__in=reservations).all() 

我认为它与类似于此的 sql 查询相匹配:

select * 
from resource r join resource_pool rp join resource_pool_elem rpe join reservation reserv  
where r.id = rpe.resource and   
  rpe.pool = rp.id and 
  reserv.id = rp.reservation and 
  r.name in (resourcesNames[0], ..., resourcesNames[n-1]) 
  reserv.id in (reservations[0], ..., reservations[n-1]) 

现在，我想为这个查询添加一个限制。每个池可能有一个独占模式 bool 标志。将有一个额外的输入列表，其中包含每个池的请求的独占标志，我只想查询独占标志与请求的独占标志匹配的池资源(如果独占 = true 或独占标志为假的池资源)。我可以使用 Python 使用类似于以下的代码构建 SQL 查询:

query = "select *  
    from resource r join resource_pool rp join resource_pool_elem rep 
    join reservation reserv  
    where r.id = rpe.resource and   
    rpe.pool = rp.id and  
    reserv.id = rp.reservation and  
    reserv.id in (reservations[0], ..., reservations[n-1]) and (" 
for i in resourcesNames[0:len(resourcesNames)] 
    if i > 0: 
        query += " or " 
    query += "r.name = " + resourcesNames[i] 
    if (exclusive[i])       
        query += " and p.mode == 0" 
query += ")"     

有没有办法在 Django 查询中表达这个 sql 查询？

请您参考如下方法:

也许你可以用 Q objects 做到这一点.关于您的示例，我遇到了一些问题，但让我们用一个更简单的模型来看一下。

class Garage(models.Model): 
    name     = models.CharField() 
 
class Vehicle(models.Model): 
    wheels   = models.IntegerField() 
    gears    = models.IntegerField() 
    garage   = models.ForeignKey(Garage) 

假设您想获得车库中的所有“多轮”车辆(例如所有摩托车和汽车，但没有独轮车)，但对于汽车，您只想要那些带有 CVT 变速箱的车辆，这意味着它们只有一个齿轮. (这是怎么来的，不知道，但请耐心等待……;)下面应该给你:

from django.db.models import Q 
garage = Garage.objects.all()[0] 
query = Vehicle.objects.filter(Q(garage=garage)) 
query = query.filter(Q(wheels=2) | (Q(wheels=4) & Q(gears=1))) 

鉴于以下可用数据:

for v in Vehicle.objects.filter(garage=garage): 
    print 'Wheels: {}, Gears: {}'.format(v.wheels, v.gears) 
 
Wheels: 1, Gears: 1 
Wheels: 2, Gears: 4 
Wheels: 2, Gears: 5 
Wheels: 4, Gears: 1 
Wheels: 4, Gears: 5 

运行查询会给我们:

for v in query: 
    print 'Wheels: {}, Gears: {}'.format(v.wheels, v.gears) 
 
Wheels: 2, Gears: 4 
Wheels: 2, Gears: 5 
Wheels: 4, Gears: 1 

最后，为了适应您的情况，您可以使用以下内容:

query = models.Resource.objects.filter(Q(resPool__reservation__in=reservations)) 
query = query.filter(Q(name__in(resourcesNames)) 
query = query.filter(Q(resPool__exclusive=True) & Q(resPool__mode=0))