在SQL中创建许多相关的对象，例如INSERT ... SELECT [英] Creating many related objects like INSERT ... SELECT in SQL

问题描述

我正在发送邮件，我需要跟踪每条邮件的发送时间，时间，向谁发送，等等。

I'm sending out messages and I need to keep track of each message being sent out, when, to whom, etc.

所以我有收件人除了邮件外，还有一个额外的表模型，每次创建新邮件时，我都需要填充收件人。邮件的收件人将从第三个模型填充，该模型包含我要发送到的所有当前电子邮件地址。

So I have the recipients in a an extra table model besides the message, and I need to populate the recipients every time I create a new message. The Recipient of a message will be populated from a third model, which contains all the current e-mail addresses that I want to sent out to.

所以我的问题是如何以最有效的方式解决这个问题？

我知道我可以做类似的事情：

So my question is how I would go about this the most efficient way?
I know I can do something similar to:

m = Message.objects.create(*args)
for email in ModelWithEmails.active.values_list('email', flat=True):
    Recipient.objects.create(message=m, email=email)

但这仍然涉及将所有电子邮件地址从数据库中取出，我想尽可能将其保留在数据库中，因为每次都会提取数千个地址。

But that will still involve getting all the e-mail addresses out of the database and I would like to keep it all inside of the database if possible, as there's several thousand addresses that will be fetched every time.

推荐答案

您无法使用django ORM进行INSERT .. SELECT，但可以进行批量插入（自django 1.4起）：

You can't do INSERT .. SELECT with django ORM, but you can do a bulk insert (since django 1.4):

m = Message.objects.create(*args)
recipients = []
for email in ModelWithEmails.active.values_list('email', flat=True):
    recipients.append(Recipient(message=m, email=email))

Recipient.objects.bulk_create(recipients)

或更有效率：

  Or a tiny bit more efficient:

m = Message.objects.create(*args)
emails = ModelWithEmails.active.values_list('email', flat=True)
Recipient.objects.bulk_create([Recipient(message=m, email=email) for email in emails])

 

对于INSERT .. SELECT，您必须倒下回到原始SQL。

For INSERT .. SELECT you'll have to fall back to raw SQL.

这篇关于在SQL中创建许多相关的对象，例如INSERT ... SELECT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！