CODEWITHSUNDEEP
sqlruby-on-rails
user3477405
user3477405

Reputation: 23

Rails: Eager loading and advanced query on has_many :through

Overview

Whenever a new Product is added to the database, it is checked against all of the existing products in the database to determine if it's a potential duplicate. If the newly added product is found to be a potential duplicate, then a DuplicateWarning association is created for the newly added product (pending_product_id), and the potential match (existing_product_id). Furthermore, an integer Match Score is stored on the DuplicateWarning, indicating the strength of the potential match between the two products.

Models

class Product < ActiveRecord::Base
  has_many :matches, through: :duplicate_warnings, source: :existing_product
  has_many :duplicate_warnings, foreign_key: "pending_product_id", dependent: :destroy
end

class DuplicateWarning < ActiveRecord::Base
  belongs_to :pending_product, class_name: "Product"
  belongs_to :existing_product, class_name: "Product"
end

Problem

How can I construct the following query, and include eager loading?

Find Products matching a few Product variables where Product.duplicate_warnings.match_score < 5 and ensure the same Product has no DuplicateWarnings with a match score > 5?

For example, something like:

Product where Product.status == 0 and Product.manufacturer == "Toyota" and Product.duplicate_warnings.match_score < 5 AND ensure the product has no DuplicateWarnings with match_score > 5?

Upvotes: 0

Views: 109

Answers (1)

Tatiana Potetinova
Tatiana Potetinova

Reputation: 141

What do you want to eager load in your case? This will query for the products complying with your conditions.

Product.joins(:duplicate_warnings).select("products.*, max(duplicate_warnings. match_score) as max_score").where(status: 0, manufacturer: "Toyota").group("products.id").having("max(duplicate_warnings. match_score) < 5")

But this won't eager load duplicate_warnings. So you will need more than one query.

Alternatively, you can first find all the products you want to exclude:

exclude_product_ids = DuplicateWarning.where("match_score > 5").group("pending_product_id").pluck(:pending_product_id)

And then load them with associations: Product.where(["status = 0 AND manufacturer = 'Toyota' AND id NOT IN (?)", exclude_product_ids]).includes(:duplicate_warnings)

Upvotes: 1

Related Questions