It it possible to make deep references in joins?

Ruby on Rails
1

Dear Sir or Madam,

I’m trying to address N+1 problem by converting some matching behaviour to SQL (Postgres).

Intro

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:

  1. SKU’s of the both models are equal
  2. Titles of sellables and theirs products (i.e. ChannelProduct#title and Product#title) are equal

Current solution

Pretty simple with where clause:

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')

Problem

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 :smile:

Thank you in advance!

2

One option is to join to custom query instead of just sellables table, i.e.

Replace

left join sellables

With

left join (select sellables.id, sellables.title, sellables.sku_code, products.title as product_title from sellables join products on products.id = sellables.product_id) as sellables

Than resulting query will be

ChannelSellable.select('distinct on (channel_sellables.id) channel_sellables.*, sellables.id as sellable_id').joins('left join (select sellables.id, sellables.title, sellables.sku_code, products.title as product_title from sellables join products on products.id = sellables.product_id) as sellables on (channel_sellables.remote_sku = sellables.sku_code) or (sellables.title channel_sellables.remote_title and channel_products.remote_title = product_title)').joins(:channel_product).where('channel_sellables.sellable_id is NULL')