id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
3642	[multi-db] Filters with nested foreign keys across databases fail	Carole Zieler <carole.zieler@…>	nobody	"If you have two or more databases:

DATABASE_ENGINE = 'mysql'           # 'postgresql', 'mysql', 'sqlite3' or 'ado_mssql'.
DATABASE_NAME = 'djangodb'             # Or path to database file if using sqlite3.
DATABASE_USER = 'root'             # Not used with sqlite3.
DATABASE_PASSWORD = ''         # Not used with sqlite3.
DATABASE_HOST = 'somehost'             # Set to empty string for localhost. Not used with sqlite3.
DATABASE_PORT = ''             # Set to empty string for default. Not used with sqlite3.


OTHER_DATABASES = {
    'mortgagedb': {
           'DATABASE_NAME': 'mortgagedb',
           'DATABASE_USER': 'root',
           'DATABASE_PASSWORD': '',
           'MODELS': ['mortgagedb']
    },
    'userdb': {
           'DATABASE_NAME': 'userdb',
           'DATABASE_USER': 'root',
           'DATABASE_PASSWORD': '',
           'MODELS': ['userdb']
    },
.
.
.



Then you have models that look like this:

Users Model:
class Company(models.Model):
    company_id = models.AutoField(primary_key=True)
    company_name = models.CharField(blank=True, maxlength=150)
    ....other properties

class UserInfo(models.Model):
    user_id = models.AutoField(primary_key=True)
    login = models.CharField(blank=True, maxlength=765)
    first_name = models.CharField(blank=True, maxlength=60)
    middle_name = models.CharField(blank=True, maxlength=60)
    last_name = models.CharField(blank=True, maxlength=60)
    company = models.ForeignKey(Company,related_name=""company_users"")


Loans Model:
(import users model up top)
class Mortgage(models.Model):
    mortgage_id = models.AutoField(primary_key=True)
    property = models.ForeignKey(Property)
    obligor_user = models.ForeignKey(UserInfo,  null=True)


Then in the views if you try to do this:

paramsnew = {}
paramsnew['obligor_user__company__company_name']= request.POST['company_name'] # __istartswith fails too, as well as trying to do company ids with __in
mortgages = Mortgage.objects.filter(**params).distinct() #Mortgage.objects.filter(**params) fails too



You get the following error:
Exception Value:  	(1146, ""Table 'mortgagedb.user_info' doesn't exist"")
Exception Location: 	/usr/lib/python2.4/site-packages/MySQLdb/connections.py in defaulterrorhandler, line 33
Traceback (innermost last)
Switch to copy-and-paste view

    * /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/core/handlers/base.py in get_response
        67. # Apply view middleware
        68. for middleware_method in self._view_middleware:
        69. response = middleware_method(request, callback, callback_args, callback_kwargs)
        70. if response:
        71. return response
        72.
        73. try:
        74. response = callback(request, *callback_args, **callback_kwargs) ...
        75. except Exception, e:
        76. # If the view raised an exception, run it through exception
        77. # middleware, and if the exception middleware returns a
        78. # response, use that. Otherwise, reraise the exception.
        79. for middleware_method in self._exception_middleware:
        80. response = middleware_method(request, e)
      ▶ Local vars
      Variable 	Value
      callback 	
      <function rendermortgageadmin at 0xb6d5c3e4>
      callback_args 	
      ()
      callback_kwargs 	
      {}
      e 	
      <_mysql_exceptions.ProgrammingError instance at 0xb6c85f4c>
      exceptions 	
      <module 'django.core.exceptions' from '/usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/core/exceptions.pyc'>
      mail_admins 	
      <function mail_admins at 0xb7196fb4>
      middleware_method 	
      <bound method TransactionMiddleware.process_exception of <django.middleware.transaction.TransactionMiddleware object at 0xb705c82c>>
      path 	
      '/rsswww/mortgageadmin/'
      request 	
      <ModPythonRequest path:/rsswww/mortgageadmin/, GET:<MultiValueDict: {}>, POST:<MultiValueDict: {'city': [''], 'zip': [''], 'filter': ['1'], 'state': [''], 'company_name': ['Trinity'], 'endmortgageamount': [''], 'startmortgageamount': ['']}>, COOKIES:{'sessionid': '61fc326c056ad6757f419441d9ac599f'}, META:{'AUTH_TYPE': None, 'CONTENT_LENGTH': 0L, 'CONTENT_TYPE': None, 'GATEWAY_INTERFACE': 'CGI/1.1', 'HTTP_ACCEPT': 'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5', 'HTTP_ACCEPT_CHARSET': 'ISO-8859-1,utf-8;q=0.7,*;q=0.7', 'HTTP_ACCEPT_LANGUAGE': 'en-us,en;q=0.5', 'HTTP_CONNECTION': 'close', 'HTTP_CONTENT_LENGTH': '87', 'HTTP_CONTENT_TYPE': 'application/x-www-form-urlencoded', 'HTTP_COOKIE': 'sessionid=61fc326c056ad6757f419441d9ac599f', 'HTTP_HOST': '192.168.1.71', 'HTTP_USER_AGENT': 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.9) Gecko/20050711 Firefox/1.0.5', 'HTTP_X_FORWARDED_FOR': '66.177.134.144', 'HTTP_X_FORWARDED_HOST': 'dev.realsecuritizationsystems.com', 'HTTP_X_FORWARDED_SERVER': 'dev.realsecuritizationsystems.com', 'HTTP_~~~~~~~': '~~~~:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~', 'HTTP_~~~~~~~~~~~~~~~': '~~~~~~~~~~~~', 'PATH_INFO': '/mortgageadmin/', 'PATH_TRANSLATED': None, 'QUERY_STRING': None, 'REMOTE_ADDR': '192.168.1.111', 'REMOTE_HOST': None, 'REMOTE_IDENT': None, 'REMOTE_USER': None, 'REQUEST_METHOD': 'POST', 'SCRIPT_NAME': None, 'SERVER_NAME': 'rssws1d.realdatacenter.com', 'SERVER_PORT': 0, 'SERVER_PROTOCOL': 'HTTP/1.1', 'SERVER_SOFTWARE': 'mod_python'}>
      resolver 	
      <django.core.urlresolvers.RegexURLResolver object at 0xb705c7ec>
      response 	
      None
      self 	
      <django.core.handlers.modpython.ModPythonHandler object at 0xb724dfcc>
      settings 	
      <django.conf.LazySettings object at 0xb7202acc>
      urlresolvers 	
      <module 'django.core.urlresolvers' from '/usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/core/urlresolvers.pyc'>
    * /home/carole/rssproject/rsswww/views/mortgages.py in rendermortgageadmin
       129. params['appraiser_user__user_id'] = current_user.user_id
       130.
       131. # do they need to filter the list further?
       132. count = 0
       133. if request.session.get(""SortMortgageListBy""):
       134. count = Mortgage.objects.filter(**params).order_by(request.session.get(""SortMortgageListBy"")).distinct().count()
       135. else:
       136. count = Mortgage.objects.filter(**params).distinct().count() ...
       137.
       138. if count > 300:
       139. specialtitle=""View A Filtered List Of Mortgages:""
       140. # we want to have them filter the list down some..this number is too much to look at anyway
       141. return render_to_response('mortgageadmin.html',{'mortgagecount':count,'displayfilter':""yes"",'specialtitle':specialtitle,'current_role':current_role,'company_name':company_name,'city':city,'state':state,'zip':zip,'menu':menu},context_instance=RequestContext(request))
       142. else:
      ▶ Local vars
      Variable 	Value
      add 	
      1
      city 	
      ''
      company_name 	
      ''
      count 	
      0
      current_role 	
      'ORIGINATOR'
      current_user 	
      <UserInfo: UserInfo object>
      delete 	
      1
      edit 	
      1
      menu 	
      '<ul id=""primary-nav""> <li style=""width:110px;""><a href=""/rsswww/"">Home</a></li> <li class=""menuparent""><a href=""#"" >My Account</a><ul><li><a href=""/rsswww/changemypassword"">Change Password</a></li><li><a href=""/rsswww/myprofile"">My Profile</a></li><li><a href=""/rsswww/choosereport"">My Reports</a></li></ul></li><li class=""menuparent"" style=""width:130px;""><a href=""#"">Companies/People</a><ul><li><a href=""/rsswww/companyadmin"">Companies</a></li><li><a href=""/rsswww/useradmin"">People</a></li></ul></li><li class=""menuparent"" style=""width:120px;""><a href=""#"">Securitization</a><ul><li><a href=""/rsswww/mortgageadmin"">Admin Mortgages</a></li></ul></li><li><a href=""/rsswww/logout"">Logout</a></li></ul>'
      obligor_company 	
      <Company: Trinity Property Investments, Inc.>
      params 	
      {'obligor_user__company__company_name': 'Trinity', 'originator_user__user_id': 50001L}
      paramsnew 	
      {'obligor_user__company__company_name': 'Trinity', 'originator_user__user_id': 50001L}
      privilegelist 	
      ['ADD MORTGAGE', 'EDIT MORTGAGE', 'UPLOAD MORTGAGE DOCUMENTS', 'CREATE PROPERTY BUDGET', 'APPROVE/REJECT PROPERTY BUDGET', 'CREATE CONSTRUCTION DRAW', 'APPROVE/REJECT DRAW', 'VIEW USER NOTES', 'ADD/EDIT USER NOTES', 'VIEW USER DOCUMENTS', 'ADD/EDIT/REMOVE USER DOCUMENTS', 'VIEW COMPANY NOTES', 'ADD/EDIT COMPANY NOTES', 'VIEW COMPANY DOCUMENTS', 'ADD/EDIT/REMOVE COMPANY DOCUME', 'APPROVE MORTGAGE DOCUMENT', 'RESTRICTED COMPANY/PEOPLE ADMI']
      request 	
      <ModPythonRequest path:/rsswww/mortgageadmin/, GET:<MultiValueDict: {}>, POST:<MultiValueDict: {'city': [''], 'zip': [''], 'filter': ['1'], 'state': [''], 'company_name': ['Trinity'], 'endmortgageamount': [''], 'startmortgageamount': ['']}>, COOKIES:{'sessionid': '61fc326c056ad6757f419441d9ac599f'}, META:{'AUTH_TYPE': None, 'CONTENT_LENGTH': 0L, 'CONTENT_TYPE': None, 'GATEWAY_INTERFACE': 'CGI/1.1', 'HTTP_ACCEPT': 'text/xml,application/xml,application/xhtml+xml,text/html;q=0.9,text/plain;q=0.8,image/png,*/*;q=0.5', 'HTTP_ACCEPT_CHARSET': 'ISO-8859-1,utf-8;q=0.7,*;q=0.7', 'HTTP_ACCEPT_LANGUAGE': 'en-us,en;q=0.5', 'HTTP_CONNECTION': 'close', 'HTTP_CONTENT_LENGTH': '87', 'HTTP_CONTENT_TYPE': 'application/x-www-form-urlencoded', 'HTTP_COOKIE': 'sessionid=61fc326c056ad6757f419441d9ac599f', 'HTTP_HOST': '192.168.1.71', 'HTTP_USER_AGENT': 'Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.9) Gecko/20050711 Firefox/1.0.5', 'HTTP_X_FORWARDED_FOR': '66.177.134.144', 'HTTP_X_FORWARDED_HOST': 'dev.realsecuritizationsystems.com', 'HTTP_X_FORWARDED_SERVER': 'dev.realsecuritizationsystems.com', 'HTTP_~~~~~~~': '~~~~:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~', 'HTTP_~~~~~~~~~~~~~~~': '~~~~~~~~~~~~', 'PATH_INFO': '/mortgageadmin/', 'PATH_TRANSLATED': None, 'QUERY_STRING': None, 'REMOTE_ADDR': '192.168.1.111', 'REMOTE_HOST': None, 'REMOTE_IDENT': None, 'REMOTE_USER': None, 'REQUEST_METHOD': 'POST', 'SCRIPT_NAME': None, 'SERVER_NAME': 'rssws1d.realdatacenter.com', 'SERVER_PORT': 0, 'SERVER_PROTOCOL': 'HTTP/1.1', 'SERVER_SOFTWARE': 'mod_python'}>
      sort 	
      ''
      specialtitle 	
      'Your Current Mortgages'
      state 	
      ''
      zip 	
      ''
    * /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/models/query.py in count
       195. counter._limit = None
       196. counter._select_related = False
       197. select, sql, params = counter._get_sql_clause()
       198. cursor = connection.cursor()
       199. if self._distinct:
       200. id_col = ""%s.%s"" % (backend.quote_name(self.model._meta.db_table),
       201. backend.quote_name(self.model._meta.pk.column))
       202. cursor.execute(""SELECT COUNT(DISTINCT(%s))"" % id_col + sql, params) ...
       203. else:
       204. cursor.execute(""SELECT COUNT(*)"" + sql, params)
       205. return cursor.fetchone()[0]
       206.
       207. def get(self, *args, **kwargs):
       208. ""Performs the SELECT and returns a single object matching the given keyword arguments.""
      ▶ Local vars
      Variable 	Value
      backend 	
      <module 'django.db.backends.mysql.base' from '/usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/mysql/base.pyc'>
      connection 	
      <django.db.backends.mysql.base.DatabaseWrapper object at 0xb6c859cc>
      counter 	
      Error in formatting:(1146, ""Table 'mortgagedb.user_info' doesn't exist"")
      cursor 	
      <django.db.backends.util.CursorDebugWrapper object at 0xb6c85cac>
      db 	
      Connection: <django.db.backends.mysql.base.DatabaseWrapper object at 0xb6c859cc> (ENGINE=mysql NAME=mortgagedb)
      id_col 	
      '`mortgage`.`mortgage_id`'
      params 	
      ['Trinity', 50001L]
      select 	
      ['`mortgage`.`mortgage_id`', '`mortgage`.`property_id`', '`mortgage`.`user_id`', '`mortgage`.`credit_score`', '`mortgage`.`ltarv`', '`mortgage`.`ltv_current`', '`mortgage`.`ltv_after`', '`mortgage`.`mortgage_amount`', '`mortgage`.`estimated_months`', '`mortgage`.`obligor_user_id`', '`mortgage`.`obligor_company_id`', '`mortgage`.`receiver_company_id`', '`mortgage`.`closing_agent_user_id`', '`mortgage`.`closing_agent_company_id`', '`mortgage`.`mortgage_broker_user_id`', '`mortgage`.`mortgage_broker_company_id`', '`mortgage`.`title_company_id`', '`mortgage`.`title_company_user_id`', '`mortgage`.`assignment_date`', '`mortgage`.`letter_date`', '`mortgage`.`origination_date`', '`mortgage`.`cancellation_date`', '`mortgage`.`fail_to_close_date`', '`mortgage`.`mature_date`', '`mortgage`.`payoff_date`', '`mortgage`.`completion_date`', '`mortgage`.`life_of_loan`', '`mortgage`.`originator_user_id`', '`mortgage`.`originator_company_id`', '`mortgage`.`escrow`', '`mortgage`.`loan_amount`', '`mortgage`.`payoff_amount`', '`mortgage`.`appraisal`', '`mortgage`.`loan_to_value`', '`mortgage`.`points`', '`mortgage`.`points_dollars`', '`mortgage`.`interest_rate`', '`mortgage`.`interest_amount`', '`mortgage`.`cost_of_credit_yearly_rate`', '`mortgage`.`finance_charge`', '`mortgage`.`number_of_payments`', '`mortgage`.`total_payments`', '`mortgage`.`daily_interest`', '`mortgage`.`days_of_interest`', '`mortgage`.`deferred_interest`', '`mortgage`.`total_interest`', '`mortgage`.`due_date`', '`mortgage`.`first_payment_month_year`', '`mortgage`.`last_payment_month_year`', '`mortgage`.`update_user_id`', '`mortgage`.`address_id`', '`mortgage`.`time_stamp`']
      self 	
      Error in formatting:(1146, ""Table 'mortgagedb.user_info' doesn't exist"")
      sql 	
      ' FROM `mortgage` INNER JOIN `user_info` AS `mortgage__obligor_user` ON `mortgage`.`obligor_user_id` = `mortgage__obligor_user`.`user_id` INNER JOIN `company` AS `mortgage__obligor_user__company` ON `mortgage__obligor_user`.`company_id` = `mortgage__obligor_user__company`.`company_id` WHERE (`mortgage__obligor_user__company`.`company_name` = %s AND `mortgage`.`originator_user_id` = %s)'
    * /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/util.py in execute
         5. def __init__(self, cursor, db):
         6. self.cursor = cursor
         7. self.db = db
         8.
         9. def execute(self, sql, params=()):
        10. start = time()
        11. try:
        12. return self.cursor.execute(sql, params) ...
        13. finally:
        14. stop = time()
        15. # If params was a list, convert it to a tuple, because string
        16. # formatting with '%' only works with tuples or dicts.
        17. if not isinstance(params, (tuple, dict)):
        18. params = tuple(params)
      ▶ Local vars
      Variable 	Value
      params 	
      ('Trinity', 50001L)
      self 	
      <django.db.backends.util.CursorDebugWrapper object at 0xb6c85cac>
      sql 	
      'SELECT COUNT(DISTINCT(`mortgage`.`mortgage_id`)) FROM `mortgage` INNER JOIN `user_info` AS `mortgage__obligor_user` ON `mortgage`.`obligor_user_id` = `mortgage__obligor_user`.`user_id` INNER JOIN `company` AS `mortgage__obligor_user__company` ON `mortgage__obligor_user`.`company_id` = `mortgage__obligor_user__company`.`company_id` WHERE (`mortgage__obligor_user__company`.`company_name` = %s AND `mortgage`.`originator_user_id` = %s)'
      start 	
      1172897283.3177619
      stop 	
      1172897283.3196101
    * /usr/lib/python2.4/site-packages/Django-0.95-py2.4.egg/django/db/backends/mysql/base.py in execute
        28. # It's only used when DEBUG=True.
        29. class MysqlDebugWrapper:
        30. def __init__(self, cursor):
        31. self.cursor = cursor
        32.
        33. def execute(self, sql, params=()):
        34. try:
        35. return self.cursor.execute(sql, params) ...
        36. except Database.Warning, w:
        37. self.cursor.execute(""SHOW WARNINGS"")
        38. raise Database.Warning, ""%s: %s"" % (w, self.cursor.fetchall())
        39.
        40. def executemany(self, sql, param_list):
        41. try:
      ▶ Local vars
      Variable 	Value
      params 	
      ['Trinity', 50001L]
      self 	
      <MySQLdb.cursors.Cursor object at 0xb6c85a2c>
      sql 	
      'SELECT COUNT(DISTINCT(`mortgage`.`mortgage_id`)) FROM `mortgage` INNER JOIN `user_info` AS `mortgage__obligor_user` ON `mortgage`.`obligor_user_id` = `mortgage__obligor_user`.`user_id` INNER JOIN `company` AS `mortgage__obligor_user__company` ON `mortgage__obligor_user`.`company_id` = `mortgage__obligor_user__company`.`company_id` WHERE (`mortgage__obligor_user__company`.`company_name` = %s AND `mortgage`.`originator_user_id` = %s)'
    * /usr/lib/python2.4/site-packages/MySQLdb/cursors.py in execute
       130. else:
       131. self.messages.append((TypeError, m))
       132. self.errorhandler(self, TypeError, m)
       133. except:
       134. exc, value, tb = exc_info()
       135. del tb
       136. self.messages.append((exc, value))
       137. self.errorhandler(self, exc, value) ...
       138. self._executed = query
       139. self._warning_check()
       140. return r
       141.
       142. def executemany(self, query, args):
 143.
      ▶ Local vars
      Variable 	Value
      ListType 	
      <type 'list'>
      TupleType 	
      <type 'tuple'>
      args 	
      ['Trinity', 50001L]
      exc 	
      <class _mysql_exceptions.ProgrammingError at 0xb6d3cbfc>
      exc_info 	
      <built-in function exc_info>
      query 	
      'SELECT COUNT(DISTINCT(`mortgage`.`mortgage_id`)) FROM `mortgage` INNER JOIN `user_info` AS `mortgage__obligor_user` ON `mortgage`.`obligor_user_id` = `mortgage__obligor_user`.`user_id` INNER JOIN `company` AS `mortgage__obligor_user__company` ON `mortgage__obligor_user`.`company_id` = `mortgage__obligor_user__company`.`company_id` WHERE (`mortgage__obligor_user__company`.`company_name` = %s AND `mortgage`.`originator_user_id` = %s)'
      self 	
      <MySQLdb.cursors.Cursor object at 0xb6c85a2c>
      value 	
      <_mysql_exceptions.ProgrammingError instance at 0xb6c85f4c>
    * /usr/lib/python2.4/site-packages/MySQLdb/connections.py in defaulterrorhandler
        26.
        27. """"""
        28. error = errorclass, errorvalue
        29. if cursor:
        30. cursor.messages.append(error)
        31. else:
        32. connection.messages.append(error)
        33. raise errorclass, errorvalue ...
        34.
        35.
        36. class Connection(_mysql.connection):
        37.
        38. """"""MySQL Database Connection Object""""""
  39.
      ▶ Local vars "		closed	Database layer (models, ORM)	other branch		wontfix	multi-db		Accepted	0	0	0	0	0	0