django - django orm：select_related，用除模型外的虚假外键欺骗反向外键，会出错吗？(django orm: select_related, fooling reverse foreign key with a fake foreign key addition to the model, what can go wrong?)

Question

I am trying to learn how to use Django's ORM for more advanced queries, instead of using raw sql.

(我正在尝试学习如何使用Django的ORM进行更高级的查询，而不是使用原始sql。)

select_related makes joins to reduce database hits, in principle it could make the joins that I would do manually.

(select_related进行联接以减少数据库命中率，原则上它可以像我手动进行的那样进行联接。)

But there is a problem: It doesn't use reverse foreign key relationships to make the sql.

(但是有一个问题：它不使用反向外键关系来制作sql。)

For the schema I have this is a nuisance.

(对于模式，我有一个麻烦。)

I have found what looks like a surprisingly easy work around, and I'm worried that it will be incorrect in ways I don't understand.

(我发现看起来很容易解决，但我担心以我不理解的方式会出错。)

I'm using a legacy DB, and it is read-only, and therefore unmanaged by the ORM, and there are no consequences for cascade settings.

(我使用的是旧版数据库，它是只读的，因此不受ORM的管理，并且对级联设置没有影响。)

I have made models for its tables via manage.py analysedb which did a very good job;

(我已经通过manage.py analysedb为其表建立了模型，这做得很好。)

I have a 6000 line model file, and only needed to spend ten minutes fixing things.

(我有一个6000线模型文件，只需要花十分钟来修复问题。)

One tweak I did was a hack to fool select_related by creating a foreign key relationship which is actually a reverse FK relationship.

(我所做的一项调整是通过创建外键关系（实际上是反向FK关系）来愚弄select_related 。)

This means I have two entries in my model pointing to the same database column, which is the primary key (correct) and also now a foreign key.

(这意味着我在模型中有两个条目指向同一数据库列，这是主键（正确），现在也是外键。)

I exploit the "new" column in select_related .

(我利用在“新的”列select_related 。)

The main table for my query is Service.

(我查询的主表是服务。)

It has foreign key relationships to a number of other tables, and that works well with select_related.

(它与许多其他表具有外键关系，并且与select_related一起很好地工作。)

The column servrecid_1 is my artificial addition.

(列servrecid_1是我的人工添加。)

The other two columns are two of the genuine FKs;

(另外两列是真正的FK中的两个。)

there are more.

(还有更多。)

class Service(models.Model):
    servrecid = models.AutoField(db_column='ServRecID', primary_key=True)
    servrecid_1 = models.ForeignKey('RecServ', models.DO_NOTHING, to_field='servrecid',
                                    db_column='ServRecID')  # Field name made lowercase.

    visitrecordid = models.ForeignKey('Visit', models.DO_NOTHING, db_column='VisitRecordID', blank=True,
                                      null=True)  # Field name made lowercase.
    itemno = models.ForeignKey(Fees, models.DO_NOTHING, db_column='ItemNo', to_field='itemno')
    ...
    class Meta:
        managed = False  # same for all the models


class RecServ(models.Model):
    allocationid = models.AutoField(db_column='AllocationID', primary_key=True)  # Field name made lowercase.
    servrecid = models.ForeignKey('Service', models.DO_NOTHING, db_column='ServRecID')  # Field name made lowercase.
    receiptno = models.ForeignKey(Receipt, models.DO_NOTHING, db_column='ReceiptNo')  # Field name made lowercase.

(There are more relations than I show in the snippets above)

(（关系比我在上面的摘录中显示的还要多）)

With this, I can now do queries like this:

(这样，我现在可以执行以下查询：)

q = models.Service.objects.select_related('visitrecordid__servdoctor').select_related('visitrecordid__invoiceto') 
        .select_related('visitrecordid__patientno').select_related('itemno').select_related(
    'servrecid_1__receiptno').all()[:5]

which creates a query with these joins:

(使用这些联接创建查询：)

    ... FROM [SERVICE] INNER JOIN [REC_SERV] ON ([SERVICE].[ServRecID] = [REC_SERV].[ServRecID])
 INNER JOIN [RECEIPT] ON ([REC_SERV].[ReceiptNo] = [RECEIPT].[ReceiptNo]) 
LEFT OUTER JOIN [VISIT] ON ([SERVICE].[VisitRecordID] = [VISIT].[VisitRecordID]) 
LEFT OUTER JOIN [CM_PATIENT] ON ([VISIT].[PatientNo] = [CM_PATIENT].[PATIENT_ID]) 
LEFT OUTER JOIN [DOCTOR] ON ([VISIT].[ServDoctor] = [DOCTOR].[DoctorCode]) 
LEFT OUTER JOIN [INVOICETO] ON ([VISIT].[InvoiceTo] = [INVOICETO].[InvoiceTo]) 
INNER JOIN [FEES] ON ([SERVICE].[ItemNo] = [FEES].[ItemNo])

The first join only appears because of my false FK.

(第一次加入只是因为我的FK错误而出现。)

The SQL looks fine to me, I think I have solved my problem.

(SQL对我来说看起来不错，我想我已经解决了我的问题。)

Should this actually work?

(这应该有效吗？)

What will happen now with this mutual foreign key relationship between both tables?

(两个表之间的这种相互外键关系现在将发生什么？)

  ask by Tim Richardson translate from so

Answer 1

等待大神答复