How to build one to one relationship for legacy model tables

Using Django Using the ORM
1

Hi Everyone,

I have two legacy models as follows (what legacy models for my case meaning that the underlying tables had been created before the mapping django model created and I only have read permission to the tables):

class Customer(models.ClickhouseModel):
    memberid = models.Int64Field(db_column='MemberId', primary_key=True)
    activememberid = models.Int64Field(db_column='ActiveMemberId')
    
    class Meta:
        managed=False
        db_table='Customer'


class Sale(models.ClickhouseModel):
    tranid = models.Int64Field(db_column='TranId', null=False, blank=True, primary_key=True)
    memberid=models.Int64Field(db_column='MemberId', null=True, blank=True)

    class Meta:
        managed=False
        db_table='Sale'

Given Sale.memberid is the foreign key of Customer.memberid, is it possible to set relationship e.g. one to one relationship from these two legacy models, so that I can retrieve ActiveMemberId when querying sale model table?

select
a.TranId
,a.MemberId
,b.ActiveMemberId <---- retrieve ActiveMemberId when querying sale table
from Sale as a
left join Customer as b
on a.MemberId = b.MemberId

Thank you in advance

Kelvin

2

why don’t use foreign key??

class Sale(models.ClickhouseModel):
    member=models.ForeignKey(Customer, db_column='MemberId', null=True, blank=True)

a = Customer.objects.filter({some value})
Sale.obejcts.filter(member_id__in=a.value_list('memberid', Flat=True))
3

can we use ForeignKey() for legacy models?

4

foreign key save model pk value in database.

if you use in memberid that customer pk, just change memberid field int64field to foreign key.
maybe it works.

5

It depends upon whether the field is an actual foreign key, and whether you are using these models as “read-only” reference data or actively updating them.

If you created these models using inspectdb, then be aware that inspectdb is a starting point, not the end point. The inspectdb command doesn’t identify everything that may apply to those models.

From the docs at Auto-generate the models:

This feature is meant as a shortcut, not as definitive model generation.

This means that Django is expecting you to change these definitions as needed. It’s only going to get you 90% of the way there, it’s up to you to finish tuning those models.

6

Hi Ken,

I have modified Sale model and replaced memberid field with OneToOneField().

But an error occurred when I made the following query.

Sale.objects.filter(customer__activememberid=999)

The error message shows that Customer does not existed in DatabaseB. The reason is that multiple databases are used in my Django project. Customer is belonged to DatabaseA, whereas Sale is belonged to DatabaseB, and a dbrouter.py with a Router class is used to control the routering.

Given the settings above, do you know how to design the routering function db_for_read(model , **hints ) ？Apparently, I already tried and set the following condition in db_for_read() but it does not work.

if model==Customer:
return DatabaseA

Thank you for your insight in advance.

Kelvin

7

Quoting directly from the docs for Cross-database-relations:

Django doesn’t currently provide any support for foreign key or many-to-many relationships spanning multiple databases. If you have used a router to partition models to different databases, any foreign key and many-to-many relationships defined by those models must be internal to a single database.

1 Like
8

Oh my…that means there is nothing I can do about it except repeatedly using CTE to get my activememberid back :frowning:

Anyway, thank you for your clarification.

Kelvin

9

I sort of found a solution to get this relationship limitation resolved. Actually, I can create two views for Sale and Customer tables respectively into one database first, then instead of mapping the tables, we can build the relational mapping with the views instead :slight_smile: . Hopefully this will work.

Kelvin

10

in your case, try this…

pk_customer = Customer.objects.get().pk
seles = Sale.objects.filter(memberid__in=pk_customer)
1 Like
11

I made it and this solution worked. :slight_smile: