如何将多个联接转换为rails查询

Question

我想将此查询写入活动记录方法查询

SELECT properties.name ,fields.name ,field_values.value FROM properties
LEFT JOIN fields 
ON properties.company_id=fields.company_id AND fields.required_model='Property'
LEFT JOIN field_values
ON field_values.field_id=fields.id  AND field_values.field_id=fields.id AND 
field_values.fieldable_id=properties.id;

如果我把这个查询简化一点

SELECT  fields.name ,field_values.value FROM fields 
LEFT JOIN field_values
ON field_values.field_id=fields.id AND 
field_values.fieldable_id=4892
where
fields.company_id=3 AND fields.required_model='Property';

我想在属性序列化程序中使用它，我正在尝试类似的东西

object.company.fields.join("LEFT JOIN field_values ON 
field_values.field_id=fields.id AND 
field_values.fieldable_id=4892").where(required_model: 
'Property')

但加入进来是行不通的。

Answer 1

我不确定rails是否允许以这种方式选择列。我们可以尝试另一种方法，但将返回包含我们需要的列的哈希数组

query = <<~SQL.squish
SELECT properties.name AS properties_name, fields.name AS fields_name ,field_values.value as fields_value
FROM properties
LEFT JOIN fields
ON (
properties.company_id=fields.company_id AND 
fields.required_model='Property'
)
LEFT JOIN field_values
ON (
field_values.field_id=fields.id AND 
field_values.field_id=fields.id AND
field_values.fieldable_id=properties.id
)
SQL
result = ActiveRecord::Base.connection.exec_query(query).to_hash

结果将是散列数组，如下面的示例

[
{
"properties_name"=>"PROPERTY_NAME", "fields_name"=>"FIELDS_NAME", "fields_value"=>"FIELDS_VALUE"
}
{
"properties_name"=>"PROPERTY_NAME", "fields_name"=>"FIELDS_NAME", "fields_value"=>"FIELDS_VALUE"
}
]

你能试试上面的一次吗？