CODEWITHSUNDEEP
ruby-on-rails
Matic Jan
Matic Jan

Reputation: 113

How to override table column value with column value from second table if it exist?

I'm building e-commerce application in which you can set user specific prices for products. If price for specific product for specific user is not set, it will show default product price.

It works fine, but I'm looking for more efficient solution since I'm not happy with current one.

Tables:

Models:

class User < ApplicationRecord
  has_many :prices
end

class Product < ApplicationRecord
  has_many :prices
  validates :name, presence: true

  def self.with_user_prices(current_user)
    Product.joins(
      Product.sanitize_sql_array(['LEFT OUTER JOIN prices ON prices.user_id = ?
        AND products.id = prices.product_id', current_user])
    ).select('products.*, prices.user_price')
  end
end

class Price < ApplicationRecord
  belongs_to :product
  belongs_to :user
end

How I get all products with user specific prices in controller:

@products = Product.with_user_prices(current_user)

How I display them in view:

<% @products.each do |product| %>

  <%= product.user_price ? product.user_price : product.regular_price %>

<% end %>

As you see I'm currently joining prices table and then in view I display user_price (prices table) if it exists, otherwise regular_price (products table).

I would love to solve everything in a single query keeping only one price column with appropriate value according to the current_user

Upvotes: 2

Views: 247

Answers (2)

Jay-Ar Polidario
Jay-Ar Polidario

Reputation: 6603

New Answer:

please do not mark this answer as correct because I just basically extended Marek's code and yours into mine, as after several attempts I came to what you've already done anyway, but I'm just putting it here in case it helps anyone:

app/models/product.rb

class Product < ApplicationRecord
  def self.with_user_prices(user)
    joins(
      sanitize_sql_array([
        "LEFT OUTER JOIN prices on prices.product_id = products.id AND prices.user_id = ?", user.id
      ])
    ).select(
      'products.*',
      'COALESCE(prices.user_price, products.regular_price) AS price_for_user'
    )
  end
end

controller:

@products = Product.with_user_prices(current_user)

view:

<% @products.each do |product| %>
  <%= product.price_for_user %>
<% end %>

Old Answer (Inefficient Code):

Untested but can you try the following? (Not sure if this is more or less efficient than your approach)

app/models/product.rb

class Product < ApplicationRecord
  has_many :prices

  def price_for_user(user)
    prices.includes(:user).where(
      users: { id: user.id }
    ).first&.user_price || regular_price
  end
end

controller:

# will perform LEFT OUTER JOIN (to eager load both `prices` and `prices -> user`) preventing N+1 queries
@products = Product.eager_load(prices: :user)

view:

<% @products.each do |product| %>
  <%= product.price_for_user(current_user) %>
<% end %>

Upvotes: 1

Marek Lipka
Marek Lipka

Reputation: 51151

You can make use of SQL COALESCE function:

class Product < ApplicationRecord
  # ...

  def self.with_user_prices(user)
    includes(:prices).where(prices: { user_id: user.id }).select(
      'products.*, COALESCE(prices.user_price, products.regular_price) as price'
    )
  end
end

Then you can use it simply by:

<%= product.price %>

Note that I simplified Product.with_user_prices method a little bit by using includes, which is gonna generate SQL LEFT JOIN query since there's condition on prices.

Upvotes: 2

Related Questions