On Thu, Feb 3, 2011 at 6:40 AM, Sneaky Wombat <[email protected]> wrote: > I'm not sure what sort of answer this question will lead to, but here > goes. I know foreignkeys on separate databases isn't supposed to work, > according to the django docs, but I got it to work. First off, I'm > using an old release (1, 2, 0, 'alpha', 1), one of the earlier multidb > releases. Because I hacked on it for so long and so much to make it > work for my needs, I've literally ended up with a branch of my own to > maintain. As such, I port features in as I need them from various > patches to the trunk. It sucks, but anyway, I came across a problem > where I wanted to use django-pyodbc to connect to a mssql server on > another machine. This machine was not my default router, in fact, it > was the third database I moved into my dbrouter. Further complicating > my design, (lack thereof) I needed to use a field on a table in this > mssql database as a foreign key to my primary database (mysql). > > I don't want to be lectured on my use of foreign keys like this, I > know. > > At first it seemed ok, and then I found a nugget on the django doc > pages that said foreign keys to external databases are not allowed/ > won't work. (http://docs.djangoproject.com/en/dev/topics/db/multi-db/ > #cross-database-relations) Since I ported/added things I needed in the > past, I didn't let this deter me. I pressed on. After a lot of > experimenting, I discovered a very strange behavior. Take this for > example: > > class mssqlTable(Model): > # this is one of three non-default databases in my db router > # this would get selected explictly using db_for_read/write > display = models.CharField(max_length=128) > def __unicode__(self): > return "mssqlTable:%s"%self.id > > class mysqlTable(Model): > # this is in my default database in my db router > assignee = models.ForeignKey(mssqlTable) > display = models.CharField(max_length=128) > def __unicode__(self): > return "mysqlTable:%s"%self.id > > Now the weirdness, and I think Model.objects.defer has it's hands in > this somewhere, but didn't look too hard there, other than watch it > try to defer in pdb. > > launch the shell: python manage.py shell > import the models... > x=mysqlTable.objects.all() > > As long as I don't try to do: > > x.assignee > > It works great. As soon as i try to hit that deferred field though, > it pukes with a horrible error that comes up from django-pyodbc. I can > see x.assignee_id though, that is populated with an int value of the > FK from the external DB. However, if I do: > > x=mysqlTable.objects.all()[0] > > x.assignee > > it works great! I never did find out exactly what that slice does, > but I later surmised that the slice is causing the connection.alias to > hold the right handle to the right database.
Then you surmised very, very badly. It does a little more than that. mysqlTable.objects.all() is a queryset. It's a representation of a group of objects resulting from a query. By taking the slice, you are pulling out a single object from that query. Hence, x = mysqlTable.objects.all() x.assignee doesn't work, because a list of objects doesn't have an assignee. x = mysqlTable.objects.all()[0] x.assignee does work, because the first object in the result set has an assignee. This has nothing to do with multi-db -- it's a fundamental part of how querysets work. Without wanting to be rude -- if you haven't got a grasp of this level of detail of Django, you might want to consider whether you're up to the task of making cross-database joins work. However, if you're still confident you can get the job done, there are two issues that you'll need to resolve: 1) The mechanics in Foreign keys that prohibit cross-database joins. There isn't currently any way to remove these checks, so you'l need to do some customization. I'm not fundamentally opposed to making these checks 2) The process of retrieving remote objects when you use foreign key accessors. This should really just be a matter of using to_python() to turn a foreign key value into a remote query. I'm not sure why "where" comes into this whole discussion, because cross database queries aren't *ever* going to work (or, at least, they aren't going to work without some *serious* effort). An implementation of cross-database foreign keys shouldn't need to do anything with the WHERE clause AFAICT. Defer doesn't have anything to do with it either. Foreign Keys don't use the same mechanics as deferred fields. A deferred field is the idea of "don't retrieve column X until it is explicitly requested". Foreign Keys always retrieve the *primary key* of the related object, but lazily load the related object when it is requested. Similar idea, but quite different implementation. However, you're in pretty much unexplored territory here. Conceptually, it should be possible, but there are reasons why we dropped cross-database joins from the multi-db implementation that we delivered :-) Yours, Russ Magee %-) -- You received this message because you are subscribed to the Google Groups "Django developers" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/django-developers?hl=en.
