| 1 | from django.core.management import setup_environ
|
|---|
| 2 | import settings
|
|---|
| 3 | setup_environ(settings)
|
|---|
| 4 |
|
|---|
| 5 | from django.utils.datastructures import SortedDict
|
|---|
| 6 | from django.contrib.auth.models import User
|
|---|
| 7 |
|
|---|
| 8 | # pre-condition: a user (root) must exist and two user with id=1 and id=2 must exist
|
|---|
| 9 |
|
|---|
| 10 | # ensure the order of the extra select-statements stays aligned with the select_params
|
|---|
| 11 | for result in User.objects.extra(select=
|
|---|
| 12 | {'foo3': "SELECT COUNT(auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"}
|
|---|
| 13 | , select_params=['root']).extra(select=
|
|---|
| 14 | {'foo2': "SELECT COUNT(auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 15 | , select_params=['1']):
|
|---|
| 16 | print result.foo3
|
|---|
| 17 | for result in User.objects.extra(select=
|
|---|
| 18 | {'foo1': "SELECT COUNT(auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"}
|
|---|
| 19 | , select_params=['root']).extra(select=
|
|---|
| 20 | {'foo2': "SELECT COUNT(auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 21 | , select_params=['1']):
|
|---|
| 22 | print result.foo1
|
|---|
| 23 |
|
|---|
| 24 | print User.objects.extra(select=
|
|---|
| 25 | {'foo1': "SELECT COUNT(auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"}
|
|---|
| 26 | , select_params=['root']).extra(select=
|
|---|
| 27 | {'foo2': "SELECT COUNT(auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 28 | , select_params=['2']).filter(id=1).values_list('id')
|
|---|
| 29 |
|
|---|
| 30 | # test removing extra select-statements:
|
|---|
| 31 |
|
|---|
| 32 | # using a SortedDict
|
|---|
| 33 | print User.objects.extra(select=SortedDict((
|
|---|
| 34 | ('roo', "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"),
|
|---|
| 35 | ('id1', "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"),
|
|---|
| 36 | ('id2', "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)")
|
|---|
| 37 | ))
|
|---|
| 38 | , select_params=['roo', 1, '2']).values_list('id2',flat=True)
|
|---|
| 39 | print User.objects.extra(select=SortedDict((
|
|---|
| 40 | ('roo', "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"),
|
|---|
| 41 | ('id1', "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"),
|
|---|
| 42 | ('id2', "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)")
|
|---|
| 43 | ))
|
|---|
| 44 | , select_params=['roo', 1, '2']).values('roo')
|
|---|
| 45 |
|
|---|
| 46 | # using chained extra statements, with different selects removed
|
|---|
| 47 | print User.objects.extra(select=
|
|---|
| 48 | {'roo': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"}
|
|---|
| 49 | , select_params=['root']).extra(select=
|
|---|
| 50 | {'id1': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 51 | , select_params=['1']).extra(select=
|
|---|
| 52 | {'id2': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 53 | , select_params=['2']).values('id2')
|
|---|
| 54 |
|
|---|
| 55 | print User.objects.extra(select=
|
|---|
| 56 | {'roo': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"}
|
|---|
| 57 | , select_params=['root']).extra(select=
|
|---|
| 58 | {'id1': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 59 | , select_params=['1']).extra(select=
|
|---|
| 60 | {'id2': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 61 | , select_params=['2']).values_list('id1',flat=True)
|
|---|
| 62 |
|
|---|
| 63 |
|
|---|
| 64 | print User.objects.extra(select=
|
|---|
| 65 | {'roo': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.username = %s)"}
|
|---|
| 66 | , select_params=['root']).extra(select=
|
|---|
| 67 | {'id1': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 68 | , select_params=['1']).extra(select=
|
|---|
| 69 | {'id2': "SELECT (auth_user.id) FROM auth_user WHERE (auth_user.id = %s)"}
|
|---|
| 70 | , select_params=['2']).values_list('roo',flat=True)
|
|---|