How to obtain and/or save the queryset criteria to the DB?

Question

I would like to save a queryset criteria to the DB for reuse.

So, if I have a queryset like:

Client.objects.filter(state='AL') 

# I'm simplifying the problem for readability. In reality I could have 
# a very complex queryset, with multiple filters, excludes and even Q() objects.

I would like to save to the DB not the results of the queryset (i.e. the individual client records that have a state field matching 'AL'); but the queryset itself (i.e. the criteria used in filtering the Client model).

The ultimate goal is to have a "saved filter" that can be read from the DB and used by multiple django applications.

At first I thought I could serialize the queryset and save that. But serializing a queryset actually executes the query - and then I end up with a static list of clients in Alabama at the time of serialization. I want the list to be dynamic (i.e. each time I read the queryset from the DB it should execute and retrieve the most current list of clients in Alabama).

---

Edit: Alternatively, is it possible to obtain a list of filters applied to a queryset?

Something like:

qs = Client.objects.filter(state='AL')
filters = qs.getFilters()
print filters

{ 'state': 'AL' }

Answer 1

Hi, you can create your own model to store your queries. First field can contains fk to ContentTypes Second field can be just text field with your query etc.

And after that you can use Q object to set queryset for your model.

Answer 2

You can store the sql generated by the query using the queryset's _as_sql() method. The method takes a database connection as an argument, so you'd do:

from app.models import MyModel
from django.db import connection

qs = MyModel.filter(pk__gt=56, published_date__lt=datetime.now())
store_query(qs._as_sql(connection))

Answer 3

serialising your qs object with something like

from pickle import dumps
frozen = dumps(qs)

WILL store all records which are returned from qs into frozen along with the object itself. If that is not an issue for you, then getting current results later on is easy:

from pickle import loads
qs = loads(frozen).all()

the .all() re-executes the query and you have the current result list.

Maybe it is possible to not save all the old records into frozen, but I guess that means digging into the pickle module of your python distribution.

PS: you could of course also use cPickle instead of pickle.

Answer 4

You can do as jcd says, storing the sql,

You can also store the conditions.

In [44]: q=Q( Q(content_type__model="User") | Q(content_type__model="Group"),content_type__app_label="auth")

In [45]: c={'name__startswith':'Can add'}

In [46]: Permission.objects.filter(q).filter(**c)
Out[46]: [<Permission: auth | group | Can add group>, <Permission: auth | user | Can add user>]

In [48]: q2=Q( Q(content_type__model="User") | Q(content_type__model="Group"),content_type__app_label="auth", name__startswith='Can add')

In [49]: Permission.objects.filter(q2)
Out[49]: [<Permission: auth | group | Can add group>, <Permission: auth | user | Can add user>]

In that example you see that the conditions are the objects c and q (although they can be joined in one object, q2) You can then serialize these objects and store them on the database as strings.

--edit--

If you need to have all the conditions on a single database record, you can store them in a dictionary

{'filter_conditions': (cond_1, cond_2, cond_3), 'exclude_conditions': (cond_4, cond_5)} 

and then serialize the dictionary.