Django Complex MySQL Query From PHP with Multiple Joins to Django ORM

Question

I have this code from Kohana (it's easily understandable) and I want to convert this to Django ORM given the ff. models:

class Item(models.Model):
    glasses = models.ManyToManyField(Glass, through="ItemGlass")
    collection = models.ForeignKey(Collection)

class ItemGlass(models.Model):
    glass = models.ForeignKey(Glass)
    item = models.ForeignKey(Item)

class Collection(models.Model):
    name = models.CharField(max_length=100)

class Code(models.Model):
    code = models.CharField(max_length=30)
    description = models.TextField()

class Glass(models.Model):
    collection = models.ForeignKey(Collection)
    code = models.ForeignKey(Code)

and the php query (uses Kohana's Database Lib)

$this->select(
                array('cd.id', 'id'), 
                array('cd.description','description'),
                array('COUNT(DISTINCT("items.id"))', 'count')
            )
                    ->from('items')
            ->join(array('collections', 'c'))
            ->on('c.id', '=', 'items.collection_id')
            ->join(array('glasses', 'g'))
            ->on('g.collection_id', '=', 'c.id')
            ->join(array('code', 'cd'))
            ->on('cd.id', '=', 'g.code_id')
            ->where('items.discontinued', '=', FALSE)
            ->group_by('cd.id');

NOTE: the "array" clause you see is translated as

"SELECT cd.id AS id, cd.description AS description, COUNT(DISTINCT(items.id) AS count"

The thing is how do I do it? I can't successfully use select_related to join multiple tables in this case, and I can't find a good "filter trick" for the query. Any ideas?

EDIT: I am considering doing it in plain SQL, but I would prefer to avoid it if a Django ORM query can be done :)

Why the ItemGlass model? Are the more attributes on it that you left out for simplification? — Chris Wesseling
– Chris Wesseling, Commented Jul 5, 2012 at 9:27
ItemGLass is a "through" table, storing additional data. some attributes are left out for simplification. — yretuta
– yretuta, Commented Jul 6, 2012 at 6:13

yretuta · Accepted Answer · 2012-07-03 23:56:33Z

2

I have come up with this after an hour of "head banging":

glasses = Code.objects.filter(glass__collection__item__discontinued=False)\
        .values('id', 'description')\
        .annotate(count=Count('glass__collection__item__id', distinct=True))

answered Jul 3, 2012 at 23:56

yretuta

8,14118 gold badges85 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Chris Wesseling Over a year ago

You are aware that this makes glasses a QuerySet containing Code objects.

yretuta Over a year ago

this was copied from a PHP code, the variable naming must be wrong but I'll keep it as is to maintain that this PHP query translates to this django query

Thomas Over a year ago

This just illustrates the power of the django queryset API does it not?

supervacuo Over a year ago

NB you might consider gradually moving away from using \ for line continuation -- it's not officially recommended due to the possibility of introducing subtle problems, there are better alternatives (here, you could rely on the implicit line continuation from parentheses) and in fact this feature was almost removed from Python entirely!

Greg · Accepted Answer · 2012-07-05 23:24:07Z

0

Definitely don't use SQL, it's a simple query which should be no problem with the ORM. Use something like:

Code.objects.filter(glass__item__discontinued=False) \
    .annotate(count=models.Count('glass__item__id'))

You can add a .values(...) to the end of it if you only want to retrieve specific columns, as in your php example.

edited Jul 5, 2012 at 23:24

answered Jul 2, 2012 at 7:01

Greg

10.4k5 gold badges47 silver badges46 bronze badges

2 Comments

Chris Wesseling Over a year ago

aggregate() is used to aggregate annotations.. annotate() should be used here.

Chris Adams Over a year ago

Your Count() call should have distinct=True as well

Collectives™ on Stack Overflow

Django Complex MySQL Query From PHP with Multiple Joins to Django ORM

2 Answers 2

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related