ORM query to prefetch from different databases without relationship field

Using Django Using the ORM
1

Hi there!

Currently, I have two Models which are in different databases and I need to make a query from both databases to show related data on the list view.

Example models:

class A(models.Model):
    b_uuid = models.UUIDField(db_index=True)


class B(models.Model):
    uuid = models.UUIDField(unique=True, editable=False, db_index=True)

    name = models.CharField(max_length=40)

    class Meta:
        managed = False

Everything works fine when I’m using models to query databases separately.

Is there any way I can use Django ORM by fetching data with the A model and then prefetching all B model objects, where A model objects b_uuid field is related with B model uuid field inside the A model queryset?

And the data will be set to A model queryset objects attribute.

Same as prefetching between two models with a Foreign Key relationship, but not using the Foreign Key field.

2

You can do it manually the same way that Django effectively does a prefetch_related.

Get a list of all the A b_uuid fields, then execute a filter on B where uuid__in that list, then match up the results between the two in your code.

3

Thank you for your answer.

In the end, that’s what I thought I will do if there is no other way to do it natively with ORM. :slight_smile:

4

I think this could help you QuerySet API reference | Django documentation | Django

5

Actually that’s not going to help here. Django does not recognize relationships across different databases.

6

I’ve successfully gotten it to work.

settings.py

DATABASES = {
    "default": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "db.sqlite3",
    },
    "replica": {
        "ENGINE": "django.db.backends.sqlite3",
        "NAME": BASE_DIR / "dbreplica.sqlite3",
    }
}

DATABASE_ROUTERS = ['myapp.routers.TestRouter', ]

myapp.models.py

from django.db import models


class BManager(models.Manager):
    def get_queryset(self):
        return super().get_queryset().using('replica')


class A(models.Model):
    name = models.CharField(max_length=100)

class B(models.Model):
    name = models.CharField(max_length=100)
    a = models.ForeignKey(A, on_delete=models.DO_NOTHING, db_constraint=False)
    objects = BManager()

myapp.test.py

import pytest
from django.db.models import prefetch_related_objects
from model_bakery import baker

from myapp import models


@pytest.mark.django_db(databases=['default', 'replica'])
def test_multidb_prefetch():
    
    a_list = [baker.make('A') for i in range(10)]
    for a in a_list:
        baker.make(
            'B',
            a=a,
            _using='replica',
            _quantity=3,
        )

    a_list = models.A.objects.all()
    
    prefetch_related_objects(a_list, 'b_set')
    assert a_list[0].b_set.all().count() == 3