How to make a query in rails with multiple tables

Question

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

Pavel Tkackenko · Accepted Answer · 2016-08-13 02:43:30Z

1

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

answered Aug 13, 2016 at 2:43

Pavel Tkackenko

9537 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Uziel Trujillo Over a year ago

This is exactly that i needed, not imagine that could make such queries, never stop to learn :P, thank you very much, you go to heaven :P i was 3 days with the problem

Uziel Trujillo Over a year ago

How can i modify this query if i passing hotel_id parameter, I tried several consultations but without result @Pavel

Collectives™ on Stack Overflow

How to make a query in rails with multiple tables

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related