Using extra() to add table alias

[Austin Gabel]

I am trying to use the ORM to build the following query:
"""SELECT * FROM category AS node, category AS parent WHERE node.lft BETWEEN parent.lft AND parent.rgt"""

I am attempting to create the table aliases using .extra() but I'm having a bit of difficulty.  I have left out the where statements in order to reduce complexity while troubleshooting this issue.  My current code looks like this.
category_list = Category.objects.all().extra(tables=['category'])

The way I understand the documentation [1] is if a table is listed in the tables argument that already exists in the query, it will add the table with a generic alias. However, it is simply ignoring the additional table.  This is the SQL that is produces.

('SELECT "category"."id", "category"."lft", "category"."rgt", "category"."tree_id", "category"."depth", "category"."name" FROM "category" ORDER BY "category"."name" ASC', ())

Is this not the correct way to go about adding a table alias?  Am I just missing something or am I way off track here?




[1] http://docs.djangoproject.com/en/dev/ref/models/querysets/#extra-select-none-where-none-params-none-tables-none-order-by-none-select-params-none

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[michael luger]

this might be a django bug .

a workaround using a view for the second table usage worked for me .
in your case something like:

create view category_parent as select * from category;

category_list =
Category.objects.all().extra(tables=['category_parent'],
where=['category.lft BETWEEN category_parent.lft AND
category_parent.rgt'])

On 13 Mai, 22:23, Austin Gabel <aga...@gmail.com> wrote:
> I am trying to use the ORM to build the following query:
> """SELECT * FROM category AS node, category AS parent WHERE node.lft BETWEEN
> parent.lft AND parent.rgt"""
>
> I am attempting to create the table aliases using .extra() but I'm having a
> bit of difficulty.  I have left out the where statements in order to reduce
> complexity while troubleshooting this issue.  My current code looks like
> this.
> category_list = Category.objects.all().extra(tables=['category'])
>
> The way I understand the documentation [1] is if a table is listed in the
> tables argument that already exists in the query, it will add the table with
> a generic alias. However, it is simply ignoring the additional table.  This
> is the SQL that is produces.
>
> ('SELECT "category"."id", "category"."lft", "category"."rgt",
> "category"."tree_id", "category"."depth", "category"."name" FROM "category"
> ORDER BY "category"."name" ASC', ())
>
> Is this not the correct way to go about adding a table alias?  Am I just
> missing something or am I way off track here?
>

> [1]http://docs.djangoproject.com/en/dev/ref/models/querysets/#extra-sele...

[Austin Gabel]

Well, yeah that would work but I would really hate to use a view to work around this.

On Fri, May 14, 2010 at 3:25 AM, michael luger <michae...@univie.ac.at> wrote:

this might be a django bug .

a workaround using a view for the second usage worked for me . in your

case something like:

create view category_parent as select * from category;

category_list =

Category.objects.all().extra(tables=['category_parent'],
where=['category.lft BETWEEN category_parent.lft AND
category_parent.rgt'])

On 13 Mai, 22:23, Austin Gabel <aga...@gmail.com> wrote:

> I am trying to use the ORM to build the following query:
> """SELECT * FROM category AS node, category AS parent WHERE node.lft BETWEEN
> parent.lft AND parent.rgt"""
>
> I am attempting to create the table aliases using .extra() but I'm having a
> bit of difficulty.  I have left out the where statements in order to reduce
> complexity while troubleshooting this issue.  My current code looks like
> this.
> category_list = Category.objects.all().extra(tables=['category'])
>
> The way I understand the documentation [1] is if a table is listed in the
> tables argument that already exists in the query, it will add the table with
> a generic alias. However, it is simply ignoring the additional table.  This
> is the SQL that is produces.
>
> ('SELECT "category"."id", "category"."lft", "category"."rgt",
> "category"."tree_id", "category"."depth", "category"."name" FROM "category"
> ORDER BY "category"."name" ASC', ())
>
> Is this not the correct way to go about adding a table alias?  Am I just
> missing something or am I way off track here?
>

> [1]http://docs.djangoproject.com/en/dev/ref/models/querysets/#extra-sele...

>
> --
> You received this message because you are subscribed to the Google Groups "Django users" group.
> To post to this group, send email to django...@googlegroups.com.
> To unsubscribe from this group, send email to django-users...@googlegroups.com.
> For more options, visit this group athttp://groups.google.com/group/django-users?hl=en.

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To post to this group, send email to django...@googlegroups.com.
To unsubscribe from this group, send email to django-users...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.

[Austin Gabel]

I just found out that I could use join() for this instead.
.query.join((None, 'category', None, None))

This got me what I wanted.