#13844: Errors when using character fields for aggregation
-------------------------------------+-------------------------------------
Reporter: zegrep@… | Owner: wogan
Type: Bug | Status: assigned
Milestone: | Component: Database layer
Version: 1.2 | (models, ORM)
Resolution: | Severity: Normal
Triage Stage: Accepted | Keywords:
Needs documentation: 0 | Has patch: 1
Patch needs improvement: 1 | Needs tests: 1
-------------------------------------+-------------------------------------
Changes (by julien):
* needs_better_patch: 0 => 1
* needs_tests: 0 => 1
Old description:
> My intension is joining tables with two different formated columns,
> by using django's double underscore magic to resolve the querysets.
>
> The values in table B are preceded by leading zeros.
>
> table A --> id = "10"
>
> table B --> id = "000010"
>
> {{{
> select distinct b.id from a, b where where a.id = TRIM(leading '0' FROM
> b.id);
> }}}
>
> The resulting code should look like this
> {{{
> qs_a=A.objects.all().values('id')
> qs_b=B.objects.annotate(id_trim=Trim('id', position='leading',
> char='0')).filter(id_trim__in=qs_a)
> }}}
>
> I use the following code to implement the extra functionality.
>
> {{{
> try:
> import psycopg2
> except ImportError, e:
> import psycopg
> else:
> psycopg = psycopg2._psycopg
>
> class Trim(django.db.models.Aggregate):
> name = 'Trim_pg'
> django.db.models.Trim=Trim
>
> class Trim_pg(django.db.models.sql.aggregates.Aggregate):
> '''
> position = [leading, trailing, both]
> char = <character to remove>
> '''
>
> sql_function = 'TRIM'
> sql_template = '''%(function)s(%(position)s %(char)s FROM
> %(field)s)'''
>
> def __init__(self, col, distinct=False, **extra):
> assert extra.has_key('position'), u'no position'
> assert extra['position'] in ('leading', 'trailing', 'both'),
> 'position no in [leading, trailing, both]'
> assert extra.has_key('char'), u'no char'
> assert len(extra['char']) == 1, 'only one character'
> extra['char']=str(psycopg2._psycopg.QuotedString(extra['char']))
> #Quoting
> super(Trim_pg, self).__init__(col, distinct=distinct, **extra)
> django.db.models.sql.aggregates.Trim_pg=Trim_pg
> }}}
>
> The problem is, that "convert_values" makes for a "CharField"
> a cast to float. My solution is to return the value for CharFields
> without the cast.
>
> {{{
> Index: db/backends/__init__.py
> ===================================================================
> --- db/backends/__init__.py (Revision 12595)
> +++ db/backends/__init__.py (Arbeitskopie)
> @@ -438,6 +438,8 @@
> return int(value)
> elif internal_type in ('DateField', 'DateTimeField',
> 'TimeField'):
> return value
> + elif internal_type in ('CharField'):
> + return value
> # No field, or the field isn't known to be a decimal or integer
> # Default to a float
> return float(value)
> }}}
New description:
My intension is joining tables with two different formated columns,
by using django's double underscore magic to resolve the querysets.
The values in table B are preceded by leading zeros.
table A --> id = "10"
table B --> id = "000010"
{{{
select distinct b.id from a, b where where a.id = TRIM(leading '0' FROM
b.id);
}}}
The resulting code should look like this
{{{
qs_a=A.objects.all().values('id')
qs_b=B.objects.annotate(id_trim=Trim('id', position='leading',
char='0')).filter(id_trim__in=qs_a)
}}}
I use the following code to implement the extra functionality.
{{{
try:
import psycopg2
except ImportError, e:
import psycopg
else:
psycopg = psycopg2._psycopg
class Trim(django.db.models.Aggregate):
name = 'Trim_pg'
django.db.models.Trim=Trim
class Trim_pg(django.db.models.sql.aggregates.Aggregate):
'''
position = [leading, trailing, both]
char = <character to remove>
'''
sql_function = 'TRIM'
sql_template = '''%(function)s(%(position)s %(char)s FROM
%(field)s)'''
def __init__(self, col, distinct=False, **extra):
assert extra.has_key('position'), u'no position'
assert extra['position'] in ('leading', 'trailing', 'both'),
'position no in [leading, trailing, both]'
assert extra.has_key('char'), u'no char'
assert len(extra['char']) == 1, 'only one character'
extra['char']=str(psycopg2._psycopg.QuotedString(extra['char']))
#Quoting
super(Trim_pg, self).__init__(col, distinct=distinct, **extra)
django.db.models.sql.aggregates.Trim_pg=Trim_pg
}}}
The problem is, that "convert_values" makes for a "CharField"
a cast to float. My solution is to return the value for CharFields
without the cast.
{{{
Index: db/backends/__init__.py
===================================================================
--- db/backends/__init__.py (Revision 12595)
+++ db/backends/__init__.py (Arbeitskopie)
@@ -438,6 +438,8 @@
return int(value)
elif internal_type in ('DateField', 'DateTimeField',
'TimeField'):
return value
+ elif internal_type in ('CharField'):
+ return value
# No field, or the field isn't known to be a decimal or integer
# Default to a float
return float(value)
}}}
--
Comment:
Patch needs improvement as per chronos' comment. Also needs tests.
--
Ticket URL: <http://code.djangoproject.com/ticket/13844#comment:6>
Django <http://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
--
You received this message because you are subscribed to the Google Groups
"Django updates" group.
To post to this group, send email to django-updates@googlegroups.com.
To unsubscribe from this group, send email to
django-updates+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-updates?hl=en.
