Friday, July 30, 2010

Re: Raw sql and admin site

# model class
class Category(models.Model):

name = models.CharField(_('name'), max_length=255)
idname = models.CharField(_('idname'), max_length=255,
db_index=True, unique=True,)
prio = models.IntegerField(_('prio'), default=0)
description = models.TextField(_('description'), null=True,
materialised_path = models.TextField(_('materialised
path'),null=True, db_index=True, blank=True)
parent_category = models.ForeignKey("self", null=True,
help_text=_('Set if category
should be child of other category'))
category_container = models.ForeignKey(CategoryContainer,
null=True, blank=True,
help_text="In which box/
filter category should be visible")

# admin class
class CategoryAdmin(admin.Admin):
list_display = ['id', 'name', 'parent_category',
'category_container', 'materialised_path']
list_display_links = ['id', 'name']
prepopulated_fields = {"idname": ("name",)}
ordering = ["-order_path"]

fieldsets = (
('Base data', {'fields': (('name', 'idname'),
('Relations', {'fields': ('parent_category',
('Additional data', {'fields': (('prio'),),
'classes': ('collapse',) }),

def queryset(self, request):
# below 2 lines work
# categories = models.Category.objects.all()
# or
# categories =

# below doesn't work
categories = models.Category.objects.raw('SELECT * FROM
category ORDER BY materialised_path using
return categories

I use materialised_path because I have list of categories and related
Category 1 data
name = 'Audi'
materialised_path = 1

Category 2 data
name = 'A4'
materialised_path = 1.2

Category 3 data
name = 'B7 (2004-2007)'
materialised_path = 1.2.3

Category 4 data
name = 'B7 (2004-2007) 1.9 TDI'
materialised_path =

Product 1 data
name = "AUDI A4 1.9 TDI 116 KM"
materialised_path =

Product 2 data
name = "AUDI A4 1.9 TDI 200 KM"
materialised_path =

so now i can get all products or children of category Audi or A4
children = Category.objects.filter(materialised_path__startswith =
products = Product.objects.filter(materialised_path__startswith =

My problem is to show ManyToMany relations between products and
categories in right order to easy use via Admin interface.
I need something like:
-Audi (materialised_path: 1)
---A4 (materialised_path: 1.2)
------B7 (2004-2007) (materialised_path: 1.2.3)

-BMW (materialised_path: 10)
---3 series (materialised_path: 10.11)

But postgresql without ~<~ operator gets me:
-Audi (materialised_path: 1)
-BMW (materialised_path: 10)
---3 series (materialised_path: 10.11)
---A4 (materialised_path: 1.2)
------B7 (2004-2007) (materialised_path: 1.2.3)

eg. Depth of materialised path is count of char "-"

so if i use sql with order by clause materialised_path i can get that
ordering, but in postgresql i need to use operator ~*~
to get correct ordering, because my database works with utf cluster.

And wheh I try to use the operator I got below error:
Database error
Something's wrong with your database installation. Make sure the
appropriate database tables have been created, and make sure the
database is readable by the appropriate user.

Any ideas what I do wrong?

> Can you show the exact code you are currently using to try and use
> this query in the admin, and the actual error you get?
> --
> DR.

You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to
To unsubscribe from this group, send email to
For more options, visit this group at

No comments:

Post a Comment