Django Custom Model ForeignKey Field for Spanning Databases

related.py

from django.core import exceptions
from django.db.models.fields.related import ForeignKey
from django.db.utils import ConnectionHandler, ConnectionRouter

connections = ConnectionHandler()
router = ConnectionRouter()


class SpanningForeignKey(ForeignKey):

    def validate(self, value, model_instance):
        if self.rel.parent_link:
            return
        # Call the grandparent rather than the parent to skip validation
        super(ForeignKey, self).validate(value, model_instance)
        if value is None:
            return

        using = router.db_for_read(self.rel.to, instance=model_instance)
        qs = self.rel.to._default_manager.using(using).filter(
            **{self.rel.field_name: value}
        )
        qs = qs.complex_filter(self.get_limit_choices_to())
        if not qs.exists():
            raise exceptions.ValidationError(
                self.error_messages['invalid'],
                code='invalid',
                params={
                    'model': self.rel.to._meta.verbose_name, 'pk': value,
                    'field': self.rel.field_name, 'value': value,
                },  # 'pk' is included for backwards compatibility
            )

Trying to use your gist, and I am having trouble running the migrations. I get the same error as yomguy above. "Relation does not exist."

I have a custom router file for each database.

Is the call to ConnectionHandler necessary? The result doesn't seem to get used anywhere.

I am getting ConnectionDoesNotExist error when I use this logic. It seems to hit if not qs.exists(): code. Any comments ?

I got an IntegrityError if try this method, and solved by creating a new integer field that reference to the pk/id from other database (field) with EasyAutocomplete plugin. you can checkout at my article for more: https://python.web.id/blog/how-to-implement-foreignkey-for-django-multi-databases/

Hello. I use this field. All work fine except the filter. Any idea what's wrong?

class CityManager(models.Model):
    city = SpanningForeignKey(City, null=True)
    email = models.EmailField(max_length=100)

>>> CityManager.objects.filter(city__name='Алматы')
Traceback (most recent call last):
 blablabla
sqlite3.OperationalError: no such table: mydb_core_city
 blablabla
django.db.utils.OperationalError: no such table: mydb_core_city
>>>

Hello, Jared.

Please specify how this patch worked on postgres-level. Which SQL query was generated by Django on the background in your case and which database backend did you use?

I tried this on Postgresql and it did not work. Moreover, postgres does not allow to refer to tables in different databases.
For example, assuming you are in db1 database, simple query like this:

select * from db2.table1;

fails with:

ERROR: relation "db2.table1" does not exist.

Although there is such a table in the db2 database, Postgres can't find it. So there's a doubt it will work from Django's side.

Please specify how this patch worked on postgres-level. Which SQL query was generated by Django on the background in your case and which database backend did you use?

This was implemented with a MySQL backend originally. I haven't invested any time in getting it to work with a postgres backend. you might check out the links in my original post to see if they have updated their implementations.

Good luck!

This was implemented with a MySQL backend originally.

Could you specify which flavour of MySQL was used? Standard InnoDB or some other?

Thanks in advance.

InnoDB if I remember correctly Get Outlook for iOS<https://aka.ms/o0ukef>

…

________________________________ From: Igor <notifications@github.com> Sent: Tuesday, July 16, 2019 22:05 To: gcko Cc: Jared Scott; Author Subject: Re: gcko/related.py This was implemented with a MySQL backend originally. Could you specify which flavour of MySQL was used? Standard InnoDB or some other? Thanks in advance. — You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub<https://gist.github.com/de1383080e9f8fb7d208?email_source=notifications&email_token=AAD7NR7CGTKLNYPQMSQJMCTP7XIQXA5CNFSM4ICGFHY2YY3PNVWWK3TUL52HS4DFVNDWS43UINXW23LFNZ2KUY3PNVWWK3TUL5UWJTQAFVMME#gistcomment-2971842>, or mute the thread<https://github.com/notifications/unsubscribe-auth/AAD7NR4CQJTINVGI72PBB63P7XIQXANCNFSM4ICGFHYQ>.

Trying to use your gist, and I am having trouble running the migrations. I get the same error as yomguy above. "Relation does not exist."

I have a custom router file for each database.
@DustinHolden have you figured out this problem. Currently, I have the same issue and can not find any suitable solution.

Trying to use your gist, and I am having trouble running the migrations. I get the same error as yomguy above. "Relation does not exist."
I have a custom router file for each database.
@DustinHolden have you figured out this problem. Currently, I have the same issue and can not find any suitable solution.

What are the Databases you are using? The code written was deployed for MySQL databases running Django 1.8 - I haven't verified if it continues to be a workable solution - there may have been some changes to Django that might necessitate updating this solution.

@gcko Postgres. Yes, it is django 2.1 now, so many changes have been done. But, I am asking @DustinHolden, because we have same problem, maybe he found some solution.

Also searching for an answer as the proposed solution doesn't work for me (django 2.2).

@ldsad7, as for us, we refused this feature and replaced it with regular microservices.

Hello. The code has been working well but today I got unexpected error of 'SpanningForeignKey' object has no attribute 'rel'. I looked at the ForeignKey code and it seems like the "rel" attribute has been changed to "remote_field"? And res.to seems to be changed to remote_field.model? Do you need to update the code?
Thanks, Sijian

**If you are using mysql and django2.2, kindly use the following for the ForeignKey **

**PROBE THANKS TO Jared Scott aka gcko **

from django.core import exceptions
from django.db.models.fields.related import ForeignKey
from django.db.utils import ConnectionHandler, ConnectionRouter
#from django.db.models import ForeignKey
connection = ConnectionHandler()
router = ConnectionRouter()

class  SpanningForeignKey(ForeignKey):
    def validate(self, value, model_instance):
        if self.remote_field.parent_link:
            return
        # Call the grandparent rather than the parent to skip validation
        super(ForeignKey, self).validate(value, model_instance)
        
        if value is None:
            return

        using = router.db_for_read(self.remote_field.model, instance=model_instance)
        qs = self.remote_field.model._default_manager.using(using).filter(
            **{self.remote_field.field_name: value}
        )

        qs = qs.complex_filter(self.get_limit_choices_to())
        if not qs.exists():
            raise exceptions.ValidationError(
                self.error_messages['invalid'],
                code='invalid',
                params={
                    'model': self.remote_field.model._meta.verbose_name, 
                    'pk': value,
                    'field': self.remote_field.field_name, 'value': value,
                },  # 'pk' is included for backwards compatibility
            )

Hello,

Thanks you very much for your solution! However, it seems like migrations are not registering the SpanningForeignKey field. Adding them manually does not fix the issue aswell. Do you have any idea what is going wrong?

Regards,
Charles

@Sarrus1, whats the problem

is there any solution for manytomanyfield?

@Sarrus1, cut and paste the error for further help..@diegobill, have not seen one,but keep on looking, the internet is a strange place

I'm trying to use this class to allow for a lookup of a user in auth_user in a 'default' DB , different from my application DB. ('test2' here).
the table I created looks like:

CREATE TABLE IF NOT EXISTS "test2_m1" ("id" integer NOT NULL PRIMARY KEY AUTOINCREMENT, "description" text NOT NULL, "user_id" integer NOT NULL ); -- no foreign key

But when I run these statements, Django generates the following query wth an INNER JOIN to auth_user, and SpanningForeignKey.validate() is not called at all (I added a debug line to test):

from test2.models import *
M1.objects.get(user__username = 'xyz')
db_for_read: test2 ---> test2
ZZZZ SELECT "test2_m1"."id", "test2_m1"."user_id", "test2_m1"."description" FROM "test2_m1" INNER JOIN "auth_user" ON ("test2_m1"."user_id" = "auth_user"."id") WHERE "auth_user"."username" = xyz

The problem here is that the underlying Django code still wants to form a query with an INNER JOIN on the app-specific DB, not the default DB, expecting "auth_user" to be in that DB.

My questions:

1. How do I get Django to NOT do that INNER JOIN on "auth_user" , especially when I specified no foreign key in the app's DB ? Shouldn't the SpanningForeignKey class prevent this from happening ?
2. If SpanningForeignKey.validate() won't do that for me, what other method(s) must I override ?