当我使用find_by_sql别名时，我很困惑 [英] when i was using find_by_sql alias, i got confused

问题描述

我正在使用RoR和mysql数据库. 我想将timestamp列转换为字符串，所以有了SQL:

I'm using RoR and mysql database. I want to convert timestamp column to string, so i have the SQL:

@books = Book.find_by_sql("select id, name, date_format(borrow_date, '%m/%d/%Y') borrow_date from books")
@books[0].borrow_date  => 'Sun, 03 Aug 2014 17:00:00 PDT -07:00'

当我输出@_books [0] .borrow_date之类的border_date时，我得到了未格式化的字符串'Sun，2014年8月3日17:00:00 PDT -07:00'，这是不期望的. 我想要"2014年8月3日"

When i output the borrow_date like @books[0].borrow_date, i got un-formatted string 'Sun, 03 Aug 2014 17:00:00 PDT -07:00' which is not expected. I want "08/03/2014"

如果我将别名更改为不同于列名的别名，则会得到预期的数据字符串.

If i change alias name that different than the column name, i get expected data string.

@books = Book.find_by_sql("select id, name, date_format(borrow_date, '%m/%d/%Y') borrow_date_other from books")
@books[0].borrow_date_other  => "08/03/2014"

我不想用ruby代码格式化日期字符串，因为我需要从oracle数据库中提取相同的数据，只想在使用前转换为字符串.

I don't want to format date string in ruby code since i need to pull the same data from oracle database and just want to convert to string before use.

为什么RoR不允许使用相同的列名和别名?我想念什么吗? 预先谢谢你.

Why RoR doesn't allow same column name and alias name? Do i miss something? Thank you in advance.

推荐答案

您正在从数据库中积极加载Books，因此AREL尝试将您的select语句映射到模型.在这种情况下，它将日期映射到borrow_date属性并将其转换为Date对象.然后，您选择的原始格式将不再相关，因为它已解析为具有自己的打印日期规则的对象.

You are actively loading Books from the database, so AREL tries to map your select statement to the model. In this case, it maps the date to the borrow_date attribute and converts it into a Date object. The original formatting you selected is then no longer relevant as it was parsed to an object which has its own rules for printing the date.

您的第二个选择有效，因为您的Book模型上可能没有该名称的属性，因此无法将其映射到特定类型，因此使用普通的String.

Your second select works since there probably is no attribute of that name on your Book model, so it cannot be mapped to a specific type, so plain String is used.

您可以简单地在模型中添加像formatted_borrow_date这样的函数，或使用帮助程序使用ruby方法格式化borrow_date来避免对SQL和日期进行硬编码.

You could simply add a function like formatted_borrow_date to your model or use a helper to have it format the borrow_date using ruby methods to avoid the hardcoded SQL and date formatting.

这篇关于当我使用find_by_sql别名时，我很困惑的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！