Django advanced query
Question: I'm using Django ORM to handle my database queries. I have the following db
tables:

  * resource
  * resource_pool
  * resource_pool_elem
  * reservation

and the following models:

    
    
    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)
    

Currently, I need to query the resources used in a set of reservations. I use
the following query:

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

which I think matches to a sql query similar to this one:

    
    
    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])
    

Now, I want to add a restriction to this query. Each pool may have a exclusive
mode boolean flag. There will be an extra input list with the requested
exclusive flags of each pool and I only want to query the resources of pools
which exclusive flag match the requested exclusive flag if exclusive = true OR
resources of pools which exclusive flag is false. I could build the SQL query
using Python with a code similar to this:

    
    
    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 += ")"    
    

Is there a way to express this sql query in a Django query?


Answer: Perhaps you can do this with [Q
objects](https://docs.djangoproject.com/en/dev/topics/db/queries/#complex-
lookups-with-q-objects). I have some issues wrapping my head around your
example, but lets look at it with a simpler model.

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

Say you want to get all "multiple-wheeled" vehicles in the garage (e.g. all
motorcycles and cars, but no unicycles), but for cars, you only want those
with a CVT transmission, meaning they only have a single gear. (How this came
up, no clue, but bear with me... ;) The following should give you that:

    
    
    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)))
    

Given the following available data:

    
    
    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
    

Running the query will give us:

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

Finally, to adapt it to your case, you might be able to use something along
the following lines:

    
    
    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))
    

