Dear Sir or Madam,
I’m trying to address N+1 problem by converting some matching behaviour to SQL (Postgres).
Let’s say we’ve the following models: ChannelSellable belongs to Sellable,
ChannelSellable - is a snapshot and remote representation of Sellable model, also belongs to ChannelProduct
Sellable - is a local model, i.e. user created thru the backend, etc. and also belongs to Product
Both can be linked as per association, but in addition we provide the logic to help user to find matched models, so, the models considered as matching if one of the following true:
- SKU’s of the both models are equal
- Titles of sellables and theirs products (i.e. ChannelProduct#title and Product#title) are equal
Pretty simple with
def matching_sellable Sellable.where("sellables.sku_code = :remote_sku OR (sellables.title = :remote_sellable_title AND products.title = :remote_product_title)", remote_sku: remote_sku, remote_sellable_title: remote_title, remote_product_title: channel_product_remote_title).first end
All good, but as a number of entries grows we facing performance issues, so, in order to reduce the number of queries I considered to convert matching function to the query.
New solution, i.e. SQL
ChannelSellable.select('distinct on (channel_sellables.id) channel_sellables.*, sellables.id as sellable_id').joins('left join sellables on sellables.deleted_at is NULL and ((channel_sellables.remote_sku = sellables.sku_code) or (sellables.title channel_sellables.remote_title and channel_products.remote_title = PRODUCTS.TITLE ))').joins(:channel_product).where('channel_sellables.sellable_id is NULL')
The problem I have is with
PRODUCTS.TITLE. As I mention earlier
Sellable belongs to
Product, so, sellables table has product_id, but I’m not quite sure how to reference it in joins, is it possible at all? I’d appreciate any ideas
Thank you in advance!