Cut to multiple joined tables

Question

I have this data model

House has_many :floors(attributes => name, size, city)
Floor has_many :rooms, belongs_to :house(attributes => name, rooms_count)
Room has_many :furnitures, belongs_to :floor(attributes => name, size)
Furniture belongs_to :room(attributes => name, size, color)

and I want to request this method

House.where(city: 'Paris').joins(floors: {rooms: :furnitures}).pluck(name, floors.id, floors.name, rooms.name, furnitures.name)

This is the SQL query I want to have:

=> SELECT houses.name, floors.id, floors.name, rooms.id, rooms.name, furnitures.id, furnitures.name FROM "houses" INNER JOIN "floors" ON "floors"."company_id" = "houses"."id" INNER JOIN "rooms" ON "rooms"."building_id" = "floors"."id" INNER JOIN "furnitures" ON "furnitures"."floor_id" = "rooms"."id" WHERE "houses"."name" = $1 [["city", "Paris"]]

Now I get raw results in a large array of arrays, and I want to sort it like that, but I'm completely stuck:

[ { name: 'toto', floors: [ { id: '123', name: 'tata' rooms: [ { name: 'titi', furnitures: [ { name: 'table' }, { name: 'chair' } ] } ] } ] } ] I need to avoid query N + 1 and refuse to select *. Stones pluck_to_hashmay be useful, but do not work well with a few table queries. Thanks for the help.

Answer 1

, pluck

arrays = House.where(city: 'Paris').joins(floors: {rooms: :furnitures}).pluck('name', 'floors.id', 'floors.name', 'rooms.name', 'furnitures.name')

, , . - .

arrays = [
  ['toto', 123, 'tata', 'titi', 'table'], 
  ['toto', 123, 'tata', 'titi', 'chair'],
  ['bob', 124, 'sue', 'hall', 'table'],
  ['bob', 124, 'sue', 'foyer', 'table']
]

arrays.inject([]) do |results, row|
  name, floor_id, floor_name, room_name, furniture_name = row

  house = results.detect{|o| o[:name] == name} || {name: name, floors: []}
  results |= [house]

  floor = house[:floors].detect{|o| o[:id] == floor_id} || {id: floor_id, name: floor_name, rooms: []}
  house[:floors] |= [floor]

  room = floor[:rooms].detect{|o| o[:name] == room_name} || {name: room_name, furnitures: []}
  floor[:rooms] |= [room]

  furniture = room[:furnitures].detect{|o| o[:name] == furniture_name} || {name: furniture_name}
  room[:furnitures] |= [furniture]

  results
end

-

---
- :name: toto   
  :floors:
  - :id: 123
    :name: tata
    :rooms:
    - :name: titi
      :furnitures:
      - :name: table
      - :name: chair
- :name: bob   
  :floors:
  - :id: 124
    :name: sue
    :rooms:
    - :name: hall
      :furnitures:
      - :name: table
    - :name: foyer
      :furnitures:
      - :name: table