Complex postgres query in rails

Question

Currently having a query for filter with parameters premium_amount_lower_range, premium_amount_upper_range, application_date_lower_range, application_date_upper_range, insurance_type_id

@filtered_quotes = current_user.insurance_subscribers.includes(:insurance_types).includes(:insurance_subscribers_types, :insurance_subscribers_types_carriers).
                   premium_amount(params[:dashboard][:premium_amount_lower_range], params[:dashboard][:premium_amount_upper_range]).
                   duration(params[:dashboard][:application_date_lower_range], params[:dashboard][:application_date_upper_range]).
                   insurance_type(params[:dashboard][:insurance_type_id])

But Now need to filter according to their status also. Having problem with it. I have status column in insurance_subscribers and insurance_subscribers_types_carriers tables, both columns are enum.

I tried to add where clause like

@filtered_quotes = current_user.insurance_subscribers.includes(:insurance_types).includes(:insurance_subscribers_types, :insurance_subscribers_types_carriers).
               where("insurance_subscribers_types_carriers.status = 1")
#              ...

This is giving me error PG::UndefinedTable: ERROR: missing FROM-clause entry for table "insurance_subscribers_types_carriers"

but when i try to do like

@filtered_quotes = current_user.insurance_subscribers.includes(:insurance_types).includes(:insurance_subscribers_types, :insurance_subscribers_types_carriers).
           where(status: 1)
#          ....

This will put the where clause on insurance_subscribers.

Trying to add a simple where clause WHERE insurance_subscribers_types_carriers.status = 1 in above query but Having so much trouble with this query.

Associations

insurance_subscriber.rb

has_many :insurance_subscribers_types, dependent: :destroy
has_many :insurance_types, through: :insurance_subscribers_types
has_many :insurance_subscribers_types_carriers, through: :insurance_subscribers_types

insurance_types.rb

has_many :insurance_subscribers, through: :insurance_subscribers_types
has_many :insurance_subscribers_types
has_many :insurance_subscribers_types_carriers

insurance_subscriber_type.rb

belongs_to :insurance_subscriber
belongs_to :insurance_type
has_many :carriers, through: :insurance_subscribers_types_carriers
has_many :insurance_subscribers_types_carriers, dependent: :destroy

insurance_subscribers_types_carrier.rb

belongs_to :carrier
belongs_to :insurance_subscribers_type

What is the relation between insurance _types, insurance_subscribers and insurance_subscribers_type_carriers? Please add all the associations — Rajdeep Singh
– Rajdeep Singh, Commented Mar 11, 2016 at 7:53

Kemal Akkoyun · Accepted Answer · 2016-03-20 17:17:08Z

If you want to add queries across associated models, first you need to join them. Since you have has_may :through associations, you can accomplish want you want as follows:

InsuranceSubscriber.joins(insurance_subscriber_types: :insurance_subscribers_types_carriers)
.includes(:insurance_types, :insurance_subscribers_types, :insurance_subscribers_types_carriers)
.where("insurance_subscribers_types_carriers.status = ?", 1)

As you see, you can join and reference your associations, even you have has_many :through associations as follows joins(.joins(insurance_subscriber_types: :insurance_subscribers_types_carriers).

You will get sql an output as follows:

InsuranceSubscriber Load (3.3ms)  SELECT "insurance_subscribers".* FROM 
"insurance_subscribers" INNER JOIN "insurance_subscriber_types" ON 
"insurance_subscriber_types"."insurance_subscriber_id" = 
"insurance_subscribers"."id" INNER JOIN "insurance_subscribers_types_carriers" ON 
"insurance_subscribers_types_carriers"."insurance_subscriber_type_id" = 
"insurance_subscriber_types"."id" WHERE (insurance_subscribers_types_carriers.status = 1)

I replicated and tested your question with models structure, like these:

PS: I made small changes in your model names, so be careful. They were so confusing try to simplify them.

insurance_subscriber.rb

# == Schema Information
#
# Table name: insurance_subscribers
#
#  id         :integer          not null, primary key
#  name       :string
#  status     :integer          default("0")
#  created_at :datetime         not null
#  updated_at :datetime         not null
#

class InsuranceSubscriber < ActiveRecord::Base
  has_many :insurance_subscriber_types, dependent: :destroy
  has_many :insurance_types, through: :insurance_subscriber_types
  has_many :insurance_subscribers_types_carriers, through:     :insurance_subscriber_types

  enum status: {active: 0, passive: 1}
end

insurance_subscriber_types.rb

# == Schema Information
#
# Table name: insurance_subscriber_types
#
#  id                      :integer          not null, primary key
#  name                    :string
#  insurance_subscriber_id :integer
#  insurance_type_id       :integer
#  created_at              :datetime         not null
#  updated_at              :datetime         not null
#

class InsuranceSubscriberType < ActiveRecord::Base
  belongs_to :insurance_subscriber
  belongs_to :insurance_type

  has_many :insurance_subscribers_types_carriers, dependent: :destroy
  has_many :carriers, through: :insurance_subscribers_types_carriers
end

insurance_subscribers_types_carriers.rb

# == Schema Information
#
# Table name: insurance_subscribers_types_carriers
#
#  id                           :integer          not null, primary key
#  carrier_id                   :integer
#  insurance_subscriber_type_id :integer
#  status                       :integer          default("0")
#  created_at                   :datetime         not null
#  updated_at                   :datetime         not null
#

class InsuranceSubscribersTypesCarrier < ActiveRecord::Base
  belongs_to :carrier
  belongs_to :insurance_subscriber_type

  enum status: {active: 0, passive: 1}
end

insurance_types.rb

# == Schema Information
#
# Table name: insurance_types
#
#  id         :integer          not null, primary key
#  name       :string
#  created_at :datetime         not null
#  updated_at :datetime         not null
#

class InsuranceType < ActiveRecord::Base
  has_many :insurance_subscribers_types_carriers
  has_many :insurance_subscribers_types_carriers
  has_many :insurance_subscribers, through: :insurance_subscribers_types
end

Plamena Gancheva · Accepted Answer · 2016-03-14 09:09:13Z

0

If you want to use the included association (insurance_subscribers_types_carriers) in the query you have to add "references" otherwise insurance_subscribers_types_carriers will be loaded separately from the main query:

InsuranceSubscriber.includes(:insurance_subscribers_types_carriers)
               .where("insurance_subscribers_types_carriers.status = ?", 1)
               .references(:insurance_subscribers_types_carriers)

answered Mar 14, 2016 at 9:09

Plamena Gancheva

7003 silver badges6 bronze badges

Collectives™ on Stack Overflow

Complex postgres query in rails

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related