CODEWITHSUNDEEP
pythonsqldjango
Alan Evangelista
Alan Evangelista

Reputation: 3163

Django advanced query

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

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?

Upvotes: 2

Views: 2004

Answers (2)

jugurtha
jugurtha

Reputation: 1

You could use a django cursor to make queries , for instance

see documentation : https://docs.djangoproject.com/en/dev/topics/db/sql/

from django.db import connection

def my_custom_sql(self):
    cursor = connection.cursor()

    cursor.execute("UPDATE bar SET foo = 1 WHERE baz = %s", [self.baz])

    cursor.execute("SELECT foo FROM bar WHERE baz = %s", [self.baz])
    row = cursor.fetchone()

    return row

Upvotes: 0

jro
jro

Reputation: 9484

Perhaps you can do this 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))

Upvotes: 3

Related Questions