Uziel Trujillo Uziel Trujillo - 3 months ago 20
Ruby Question

How to make a query in rails with multiple tables

I have 3 tables

hotel.rb

class Hotel < ActiveRecord::Base
...
has_many :folios, dependent: :destroy
...
end


folio.rb

class Folio < ActiveRecord::Base
...
belongs_to :hotel
has_many :expenses, dependent: :destroy
...
end


expense.rb

class Expense < ActiveRecord::Base
...
belongs_to :folio
...
end


The structure of tables is

create_table "hotels", force: :cascade do |t|
...
t.string "nameHotel"
...
end

create_table "folios", force: :cascade do |t|
...
t.integer "hotel_id"
...
end

create_table "expenses", force: :cascade do |t|
t.integer "folio_id"
t.float "total"
t.string "observations"
t.datetime "created_at", null: false
end


I want to create a report that display by hotel all expenses from a range of dates, something like

1 | Romano Palace
----------------------------------------------
expense_id | created_at | observations | total
1 2016-08-01 Something x 3,000
4 2016-08-07 Something y 4,000

2 | Crowne Plaza
----------------------------------------------
expense_id | created_at | observations | total
3 2016-08-05 Something x 1,000
5 2016-08-12 Something y 10,000





My Logic:

1.- I guess I must create a query with content all the hotels that have at least one folio and store in variable @hotels

2.- The variable @hotels go with each and puts id and nameHotel

3.- For every hotel get all expenses with range by current_date

I try this:

1.- For get all hotels that have at least one folio

@hotels = Folio.all.group_by { |f| f.hotel_id }


2.- I get an object @hotels with this structure

=> {
72=>[#<Folio:0x007f48ed0e97b8
...
id: 2,
hotel_id: 72,
...
]
93=>
[#<Folio:0x007f48ed0e9678
...
id: 3,
hotel_id: 93,
...
]
87=>
[#<Folio:0x007f48ed0e9538
...
id: 1,
hotel_id: 87,
...
#<Folio:0x007f48ed0e93d0
...
id: 5,
hotel_id: 87,
...
#<Folio:0x007f48ed0e9290
...
id: 4,
hotel_id: 87,
#<Folio:0x007f48ed0e9150
...
id: 6,
hotel_id: 87,
...
]
}


3.- I prefer get an object like, i can't get it

=> {
72=>[2]
93=>
[3]
87=>
[1,5,4,6]
}


4.- To the end i will try get expenses with something like this

@expenses = Expense.all.where( "folio_id IN (?)", [@folios] )


But I can not pass point 2

I would appreciate that someone help me

Answer
Expence.all
       .where('created_at > ? AND created_at < ?', @start_date, @end_date)
       .includes(:folio => :hotel)
       .group_by { |e| e.folio.hotel }
       .each do |hotel, grouped_expenses_by_hotel|

  puts hotel.nameHotel
  puts 'expense_id | created_at | observations | total' # Headers for table

  grouped_expenses_by_hotel.each do |expence|

    puts "#{expense.id} | #{expence.created_at} | #{expence.observation} | #{expence.total}"

  end
end
Comments