Take data from 4 joined tables in Rails

Question

What I need - is to get the data from 4 tables. I need to have users country, main_services name, that only have exportable: true, and quantity from each last reservation_line connected to main_service.

These are the connections and table fields of my models:

Reservatoin.rb :

# == Schema Information
#
# Table name: reservations
#
#  id                  :integer          not null, primary key
#  from_date           :date
#  to_date             :date
#  ...
#

class Reservation < ApplicationRecord
  belongs_to :user
  has_many :reservation_lines, dependent: :destroy
end

User.rb :

# == Schema Information
#
# Table name: users
#
#  id                     :integer          not null, primary key
#  ...
#  country                :string(255)

class User < ApplicationRecord
  has_many :reservations, dependent: :destroy
end

ReservationLine.rb :

# == Schema Information
#
# Table name: reservation_lines
#
#  id                 :integer          not null, primary key
#  reservation_id     :integer
#  quantity           :integer
#  serviceable_type   :string(255)
#  serviceable_id     :integer
#

class ReservationLine < ApplicationRecord
  belongs_to :reservation
  belongs_to :serviceable, polymorphic: true, optional: true
end

MainService.rb :

# == Schema Information
#
# Table name: main_services
#
#  id                  :integer          not null, primary key
#  name                :string(255)
#  main_service        :boolean          default(FALSE)
#  input_type          :string(255)
#  exportable          :boolean          default(FALSE)
#

class MainService < ApplicationRecord
  has_many :reservation_lines, as: :serviceable
end

So, I decided to write a scope. It looks like this:

reservation model:

scope :nationalities_with_date_test, -> (from, to) { joins(:user)
                                                      .joins(:reservation_lines)
                                                      .joins('INNER JOIN main_services ON main_services.id = reservation_lines.serviceable_id')
                                                      .where('reservation_lines.serviceable_type =?', 'MainService')
                                                      .where('main_services.exportable =?', true)
                                                      .select('users.country, main_services.name, SUM(reservation_lines.quantity) as quantity')
                                                      .where('reservations.from_date <= ? AND reservations.to_date >= ? AND (reservations.aasm_state = ? OR reservations.aasm_state = ? OR reservations.aasm_state = ?)',
                                                             to,
                                                             from,
                                                             'in_place', 'closed', 'finished')
                                                      .group('users.country')
                                                     }

As you can see I take all the quantity I have in the reservation_lines table, but I need to take only the last one for each main_service, and I don't get how can I do it, or even if is it possible at all. Any help would be appreciated. Thanks.

writing a scope for this is a bad idea. And for selecting the last entries from grouped data you will need to resort to writing raw [SQL queries](http://api.rubyonrails.org/classes/ActiveRecord/Querying.html#method-i-find_by_sql) using sub-queries. For e.g. you will need do something like illustrated [here](https://stackoverflow.com/a/12513074/936494) and [here](https://stackoverflow.com/a/31067437/936494). Hope this helps. — Jignesh Gohel, Jul 11 '18 at 14:26

Take data from 4 joined tables in Rails

0 Answers0