Active record query with condition on instance, using foreign key

Question

I have 2 models:

class Product < ApplicationRecord
    belongs_to :discount
end

Products table has several columns, among which an integer: price_cents.

class Discount < ApplicationRecord
    has_many :products
end

Discounts table has several columns, among which a float: saving.

Thus I can manipulate product prices through: product_instance.price_cents and product_instance.discount.saving

Now I want to get products where prices < params[:max_price]. For all products, whether a discount is applied or not. I tried few things, my last miserable attempt is :

Product.joins(:discount).where('price_cents <= ?', params[:max_price].to_i * 100 / (1 - (self.discounts.saving || 0)))

I don't know how to reach discount.saving in ActiveRecord query involving products.

Please note I don't want to achieve this with @products.select { |product| ... } or other methods, as I am chaining queries from a search form. I need to keep ActiveRecord relation.

Answer 1

Try next:

products_with_discount = Product.joins(:discount).where('(price_cents *(1 - discounts.saving)) <= ?', params[:max_price].to_i )
products_without_discount = Product.where.not(id: 
products_with_discounts).where('price_cents < ?', param[:max_price].to_i)
products_with_discounts + products_without_discounts

It is not best the solution (also I am not sure about syntax and maybe calculation in sql not correct, but main point I think will be clear.) But it is simple (another one using LEFT OUTER JOIN but it require more complicated sql and it will be depend by DB).