CODEWITHSUNDEEP
pythonmysqldjangodjango-models
db0
db0

Reputation: 3779

Django Models : Get elements considering their presence as a foreign key in another table

I have a hard time explaining exactly what I'm trying to do with Django Models, but I managed to write down the MySQL queries.

from django.db import models
from django.contrib.auth.models import User

class Item(models.Model):
    id = models.PositiveIntegerField(unique=True, primary_key=True)
    something = models.CharField(max_length=100)

class OwnedItem(models.Model):
    id = models.PositiveIntegerField(unique=True, primary_key=True)
    item = models.ForeignKey(Item)
    owner = models.ForeignKey(User)
    isWorking = models.BooleanField(default=False)

MySQL version: http://pastebin.com/kyiMCJfm

Get all items owned by John that are working:

SELECT i.*, o.Owner, o.isWorking FROM Item as i JOIN OwnedItem AS o WHERE o.Item = i.Id AND o.Owner = 'john' AND o.isWorking=1 GROUP BY i.id

Get all items John doesn't own or that are not working:

SELECT * FROM Item WHERE id NOT IN (SELECT Item FROM OwnedItem WHERE owner='john' AND isWorking=1)

How can I write those queries using the Django models syntax?

Upvotes: 2

Views: 79

Answers (1)

Rohan
Rohan

Reputation: 53326

For 1st

Item.objects.filter(itemowned__owner__username__iexact="john", 
                   itemowned__isworking=True)

For 2nd, you need to use Q objects

Item.objects.exclude(Q(itemowned__owner__username__iexact="john") |
                    Q(itemowned__isworking=True))

Upvotes: 1

Related Questions