how to convert multiple joins to rails query

user_14947486 0
Viewed 41

i want to write this query into active record method query

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;

and if I simplify this query bit more

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';

I want to use this in the property serializer, where I'm trying something like this

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')

but join in this is not working.

activerecord ruby-on-rails ruby mysql sql
1 Answers

I am not sure if rails allows to select columns in that manner. we can try another way, but will return array of hash containing column we need

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

result will be array of hash like below example

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

Can you try above once?

user_180732700
Related