当类似的代码在别处工作时，为什么Rails会产生这个SQL错误？

I have this rake task to import a csv file that I'm trying to debug, with a similar pattern to create records in 8 different tables from each row in the file. Records in the independent tables get created, then I need to find those records' id numbers to use as foreign keys to create records in the dependent tables.

我有这个rake任务来导入我正在尝试调试的csv文件，使用类似的模式从文件中的每一行创建8个不同表中的记录。创建独立表中的记录，然后我需要找到这些记录的id号，以用作外键在依赖表中创建记录。

The error says:

错误说：

rake aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'BLANCO) LIMIT 1' at line 1: SELECT  `fields`.* FROM `fields`  WHERE (IGNACIO BLANCO) LIMIT 1

which I verified in the MySQL console. But in the previous five tables, this similar code works fine to obtain the foreign keys:

这是我在MySQL控制台中验证的。但是在前面的五个表中，这个类似的代码可以很好地获取外键：

require 'csv'

    CSV.foreach('public/partial.csv', :headers => true) do |row|
        # create the Company object
        this_company_name = row['name'].strip!
        this_operator_num = row['operator_num']        
        if !(Companies.exists?(:company_name => this_company_name))
          Companies.create(company_name: this_company_name, operator_num: this_operator_num)
        end
        thecompany = Companies.find_by(company_name: this_company_name)
        company_id = thecompany.id

        # create the County object
        this_county_name = row['county'].strip!
        if !(Counties.exists?(county_name: this_county_name))
          Counties.create(county_name: this_county_name)
        end
        thecounty = Counties.find_by(county_name: this_county_name)
        county_id = thecounty.id

        # create the Gastype object  
        this_gastype_name = row['gas_type'].strip!
        if !(Gastypes.exists?(gas_type: this_gastype_name))
          Gastypes.create(gas_type: this_gastype_name)
        end
        thegastype = Gastypes.find_by(gas_type: this_gastype_name)
        gastype_id = thegastype.id

        # create the Field object
        this_field_name = row['field_name'].strip!
        this_field_code = row['field_code'].strip!
        if !(Fields.exists?(field_name: this_field_name))
          Fields.create(field_name: this_field_name, field_code: this_field_code)
        end
        thisfield = Fields.find_by(this_field_name)
        field_id = thisfield.id
...

The SQL statement that Rails created which produces the error is:

Rails创建的产生错误的SQL语句是：

SELECT  `fields`.* FROM `fields`  WHERE (IGNACIO BLANCO) LIMIT 1;

which has an obviously incorrect WHERE clause. My question is why did Rails not produce the correct statement, like:

它有明显不正确的WHERE子句。我的问题是为什么Rails没有产生正确的陈述，例如：

SELECT fields.* FROM fields WHERE (field_name ='IGNACIO BLANCO') LIMIT 1;

SELECT字段。* FROM字段WHERE（field_name ='IGNACIO BLANCO'）LIMIT 1;

Should I change how the .find_by statement is written? Or is there a better way of obtaining the requisite foreign key?

我应该改变.find_by语句的写法吗？或者有更好的方法来获得必要的外键吗？

1 个解决方案

#1

Because of this line:

因为这条线：

thisfield = Fields.find_by(this_field_name)

You're simply passing a string to find_by, and Rails will consider it to be raw SQL.

您只需将字符串传递给find_by，Rails会将其视为原始SQL。

You need to use either of these two solutions:

您需要使用以下两种解决方案之一：

thisfield = Fields.find_by_field_name(this_field_name)
thisfield = Fields.find_by(field_name: this_field_name)

#1