如何输出原始SQL Active Record选择查询的结果? [英] How to output the results of a raw SQL Active Record select query?

问题描述

我正在控制台中测试一些Active Record调用.我经常有这样的原始SQL选择:

I am testing some Active Record call in the console. I often have raw SQL selects like this:

Model.joins(:other_model).where(some_column:foo).select("other_model.column AS col1, model.column AS col2")

这将导致以下结果:

[#<Model:0x00007fa45f25b3d0 id: nil>,
 #<Model:0x00007fa45f25b1c8 id: nil>,
 #<Model:0x00007fa45f25afc0 id: nil>,
 #<Model:0x00007fa45f25acc8 id: nil>,
 #<Model:0x00007fa45f25aa48 id: nil>,
 #<Model:0x00007fa45f25a6d8 id: nil>]

我可以让控制台从"other_model.column" 输出值吗?我发誓我以前曾见过这样的花招，但似乎在任何地方都找不到.

Can I have the console output the values from the "other_model.column"? I swear I saw a trick for this before but can't seem to find this anywhere.

推荐答案

从根本上讲，问题在于ActiveRecord将尝试将结果行转换为 Model s.但是，该属性仍处于加载状态！它们只是不在模型的字符串表示中，因为它们不作为表中的列存在.

Fundamentally, the issue is that ActiveRecord is going to attempt to cast the resulting rows as Models. However, the attributes are still loaded! They're just not in the string representation of the model, since they don't exist as columns on the table.

给出关系:

relation = Model.joins(:other_model).where(some_column:foo)
                .select("other_model.column AS col1, model.column AS col2")

只需映射出 #attributes :

relation.map(&:attributes)

无论如何，您最终仍然会在属性中使用 id 字段，但是您可以轻松地忽略它们，或者根据需要进一步处理映射的属性以将其删除.

You will still end up with id fields in attributes regardless, but you can easily ignore them, or further process the mapped attributes to remove them if needed.

例如:

> Account.where("id < 1000")
         .select("id * 1024 as bigid")
         .limit(10)
         .map(&:attributes)
=> [{"id"=>nil, "bigid"=>819200},
    {"id"=>nil, "bigid"=>820224},
    {"id"=>nil, "bigid"=>822272}]

另一种选择是跳过ActiveRecord加载，并使用 #to_sql 来仅执行原始查询.在这种情况下，我有一个Postgres连接，所以我正在使用 #exec_query ;它可能会根据您的RDBMS而有所不同:

Another alternative would be to skip the ActiveRecord loading, and use #to_sql to just execute a raw query. In this case, I have a Postgres connection, so I'm using #exec_query; it may vary based on your RDBMS:

给出我们的关系:

> ActiveRecord::Base.connection.exec_query(relation.to_sql).to_hash
=> [{"bigid"=>"819200"}, {"bigid"=>"820224"}, {"bigid"=>"822272"}]

这篇关于如何输出原始SQL Active Record选择查询的结果?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！