Login

Filter on Multiple M2M Objects Simultaneously

Author:
axiak
Posted:
April 5, 2007
Language:
Python
Version:
.96
Tags:
models q m2m db orm
Score:
7 (after 7 ratings)

This snippet should allow you to do queries not before possible using Django's ORM. It allows you to "Split" up the m2m object you are filtering on. This is best described by example:

Suppose you have Article and Tag, where Article has a m2m relation with Tag (related_name = 'tag'). If I run:

from django.db.models.query import Q
Article.objects.filter(Q(tag__value = 'A') & Q(tag__value = 'B'))
> # no results

I would expect to get no results (there are no tags with both a value of 'A' and 'B'). However, I would like to somehow get a list of articles with tags meeting that criteria. Using this snippet, you can:

from django.db.models.query import Q
from path.to.qsplit         import QSplit
Article.objects.filter(QSplit(Q(tag__value = 'A')) & QSplit(Q(tag__value = 'B'))) 
> # articles with both tags

So now they are split into different Tag entries. Notice how the QSplit() constructor takes a Q object---it's possible to give this any complicated Q-type expression.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
from django.db.models.query import Q as DjangoQ
from django.utils.datastructures import SortedDict


def replaceDict(string, dict):
    " Will take a dictioary and an arbitrary string, and replace the string with the dictioary."
    if len(dict) == 0:
        return string

    curDict = dict.copy()
    keys = curDict.keys()
    keys.sort(lambda x,y: cmp(len(y), len(x)))
    key = keys[0]
    val = curDict[key]

    del curDict[key]

    offset  = 0
    strings = [string]


    while strings[-1].find(key) != -1:
        loc = strings[-1].find(key)

        strings.append(strings[-1][len(key) + loc:])

        strings[-2] = strings[-2][:loc]


    return val.join([replaceDict(s,curDict) for s in strings])




class QSplit(DjangoQ):
    " Encapsulates a single JOIN-type query into one object "

    def __init__(self, q):
        " Creates a single Q-ish object that separates itself from other Q objects. "
        self.q = q

    def get_sql(self, opts):
        " This will generate the correct (joins, where, params) tuple. "
        from django.conf import settings

        engine_lower = settings.DATABASE_ENGINE.lower()

        if 'mysql' in engine_lower:
            tick = '`'
        elif 'postgres' in engine_lower or 'sqlite' in engine_lower:
            tick = '"'
        else:
            tick = '"'

        joins, where, params = self.q.get_sql(opts)
        key_replace = {}
        joins2      = SortedDict()
        where2      = []


        for key, val in joins.items():
            cur_key = key.strip(tick)
            cur_val = '%s__%s' % (key.strip(tick), hash(self))

            key_replace[cur_key] = cur_val

            joins2['%s%s%s' % (tick, cur_val, tick)] = val



        for key, val in joins2.items():
            joins2[key] = (val[0],val[1],replaceDict(val[2],key_replace))


        where2 = [replaceDict(clause, key_replace) for clause in where]
        return joins2, where2, params

More like this

  1. Tags & filters for rendering search results by exogen 7 years, 4 months ago
  2. Allow filtering and ordering by counts of related query results by exogen 8 years, 3 months ago
  3. render_markup filter, specify the markup filter as a string by exogen 8 years, 3 months ago
  4. CustomQueryManager by zvoase 7 years ago
  5. Bitwise operator queryset filter by hgeerts@osso.nl 5 years, 3 months ago

Comments

Klowner (on May 25, 2007):

Works beautifully although I was having some trouble with sqlite, so I made a small change to line 48:

elif settings.DATABASE_ENGINE.lower() in ('postgres','sqlite3'):

And now it works just fine

#

axiak (on May 25, 2007):

Updated to work in SQLite. Thanks.

#

arcanum (on April 27, 2008):

Hi, now that queryset-refactor branch is merged with trunk, what happens to this snippet?

#

Please login first before commenting.