如果数据只匹配Rails中一个表到另一个表的某个条件，我该如何插入数据？

I'm somewhat of a begginer to SQL and migrations in general, so it's possible my approach is completely wrong here, but bear with me ;)

我有点像SQL和迁移一般，所以我的方法可能在这里完全错误，但请耐心等待;）

I have a Company model that has attributes for SMTP mailing configuration (like smtp_address and smtp_port), which are optional.

我有一个具有SMTP邮件配置属性的公司模型（如smtp_address和smtp_port），它们是可选的。

However, I now need my User model to be able to have an SMTP configuration as well, so I created a SMTPConfiguration model with attributes address, port, and a polymorphic association to smtpable. This model will belong either to the User or Company model

但是，我现在需要我的用户模型也能够进行SMTP配置，因此我创建了一个SMTPConfiguration模型，其中包含属性地址，端口和与smtpable相关的多态关联。此模型将属于User或Company模型

What I'd like to do now is migrate the data I already have in the companies table over to the SMTP configurations table, which sounds pretty straightforward, except I want to create a new SMTPConfiguration ONLY if the Company actually has the smtp_address and smtp_port set. If smtp_address and smtp_port are not present, the Company should not have a belonging SMTPConfiguration.

我现在要做的是将我已经在公司表中的数据迁移到SMTP配置表，这听起来非常简单，除非我想创建一个新的SMTP配置，如果公司实际上有smtp_address和smtp_port设置。如果不存在smtp_address和smtp_port，则公司不应具有所属的SMTPConfiguration。

This would be fairly easy to do using Ruby conditions and ActiveRecord, but from what I've read inserting rows individually with ActiveRecord can be reaaally long for large databases, so it is a much better idea to use raw SQL. Turns out I suck at SQL :( .

使用Ruby条件和ActiveRecord可以很容易地做到这一点，但是从我读过的内容中，使用ActiveRecord单独插入行对于大型数据库来说可能非常长，所以使用原始SQL更好。事实证明我吮吸SQL :(。

I've searched on * quite a bit, and using this and this, I came up with something like

我已经在*上搜索了很多，并且使用了这个，我想出了类似的东西

execute <<-SQL
  INSERT INTO smtp_configurations (address, port, smtpable_id)
  SELECT (c.smtp_address, c.smtp_port, c.id)
  FROM companies c
  WHERE c.smtp_address <> NULL
SQL

This wouldn't work since it doesn't set created_at, updated_at and smtpable_type, but to be honest I'm out of ideas. Could the activerecord-import gem be of any use?

这不起作用，因为它没有设置created_at，updated_at和smtpable_type，但说实话我没有想法。 activerecord-import gem可以用吗？

I am using Rails 5.1.4 and Postgres 10.3

我正在使用Rails 5.1.4和Postgres 10.3

1 个解决方案

#1

Try next code in your migration:

在迁移中尝试下一个代码：

created_at = Time.current.to_s(:db)
insert_clause = <<-SQL
  INSERT INTO smtp_configurations(
    smtpable_id,
    smtpable_type,
    address,
    port,
    created_at,
    updated_at
  )
  SELECT
    c.id,
    'Company',
    c.smtp_address,
    c.smtp_port,
    '#{created_at}',
    '#{created_at}'
  FROM companies c
  WHERE (c.smtp_address IS NOT NULL) AND (c.port IS NOT NULL)
SQL

execute(insert_clause)

UPDATED: Changed code for plain Rails.

更新：更改了普通Rails的代码。

#1