Solved: Table not found error when ordering by foreign key

[jtm]

My original question
(http://groups.google.com/group/django-users/browse_frm/thread/1581881fb9c1ddc1/#):

Here is a simplified version of what I am trying to do:

App X:
class A:
shortname = meta.CharField(maxlength=6)

App Y:
class B:
the_A = meta.ForeignKey(A)

Y.views:
blist = bs.get_list(order_by=['as.shortname'])

But Django is telling me that there is no table named 'as'.
Everything seems to be importing fine. Does anyone have any
hints?

I even used fully qualified table names
(eg bs.get_list(order_by=['x_as.shortname']) ).

The answer was:

blist = bs.get_list(order_by=['as.shortname'], select_related=True)

Looking at the generated SQL in db.queries, adding select_related makes
Django
bring in every field in every related table, thereby making any field
you wish available
for use by the ORDER BY clause. Not the most efficient query for my
purposes, but
it will do for now.

jtm


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

Re: Table not found error when ordering by foreign key

[jtm]

Hi Adrian,

Thanks for the reply.

I did try using the full table names without success.

This is the error and traceback (with real class names):

 OperationalError at /debtors/current/
 (1109, "Unknown table 'matterlist_matters' in order clause")

Traceback (most recent call last):
File
"/opt/local/lib/python2.4/site-packages/django/core/handlers/base.py"
in get_response
  74. response = callback(request, *callback_args, **callback_kwargs)
File "/Users/jimmy/simpsons/debtors/views.py" in debtorlist
  29. dbtrs = debtreports.get_list(order_by=ordering)
File
"/opt/local/lib/python2.4/site-packages/django/utils/functional.py" in
_curried
  3. return args[0](*(args[1:]+moreargs), **dict(kwargs.items() +
morekwargs.items()))
File
"/opt/local/lib/python2.4/site-packages/django/core/meta/__init__.py"
in function_get_list
  1396. return list(function_get_iterator(opts, klass, **kwargs))
File
"/opt/local/lib/python2.4/site-packages/django/core/meta/__init__.py"
in function_get_iterator
  1379. cursor.execute("SELECT " + (kwargs.get('distinct') and
"DISTINCT " or "") + ",".join(select) + sql, params)
File "/opt/local/lib/python2.4/site-packages/django/core/db/base.py" in
execute
  10. result = self.cursor.execute(sql, params)
File
"/opt/local/lib/python2.4/site-packages/django/core/db/backends/mysql.py"
in execute
  32. return self.cursor.execute(sql, params)
File "/opt/local/lib/python2.4/site-packages/MySQLdb/cursors.py" in
execute
  137. self.errorhandler(self, exc, value)
File "/opt/local/lib/python2.4/site-packages/MySQLdb/connections.py" in
defaulterrorhandler
  33. raise errorclass, errorvalue

the local vars for my view show:

ordering('matterlist_matters.code',)

I'm running revision 2524.

Everything else is working fine (ordering by fields in the class in the
app, ordering using a full table name -
debtors_debtreports.fees_to_date -
for a class in the app, and tracing relationships in the template -
debtor.get_matter.get_client.name), it's just when I am ordering by a
field
in a ForeignKey, or a field in the ForeignKey of a ForeignKey.

Am I expecting too much magic and should I be doing some SQL and/or
python myself to handle this kind of ordering (not to mention the
grouping
which I haven't got to yet, and aggregates).

thanks again,

jimmy


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

Re: Table not found error when ordering by foreign key

[Adrian Holovaty]

On 3/13/06, jtm <[EMAIL PROTECTED]> wrote:
> Y.views:
> blist = bs.get_list(order_by=['as.shortname'])
>
> But Django is telling me that there is no table named 'as'.
> Everything seems to be importing fine. Does anyone have any
> hints?

Go into your database command-line client and type "\dt" (Postgres) or
"show tables;" (MySQL) to list the table names.

Adrian

--
Adrian Holovaty
holovaty.com | djangoproject.com

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

Table not found error when ordering by foreign key

[jtm]

Here is a simplified version of what I am trying to do:

App X:
class A:
shortname = meta.CharField(maxlength=6)

App Y:
class B:
the_A = meta.ForeignKey(A)

Y.views:
blist = bs.get_list(order_by=['as.shortname'])

But Django is telling me that there is no table named 'as'.
Everything seems to be importing fine. Does anyone have any
hints?

thanks,

jtm


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