Extracting a set record ids from a translations table using a subquery. Then need to feed this set of ids to several WHERE clauses of another query in order to extract the record from a specific table (product_listings) via a series of joins.

Solution found! See

CodePudding user response：

Translations seems to be the driver for this so I would consider a view and drive from the view

Create view yoursubquery as vids;

select
  product_listings.*
from vids
  left join product_listings pn product_listings.id = vids.record_id
  left join product_categories on product_categories.ch_id = product_listings.ch_vintage_id
  left join products on products.ch_id = product_categories.ch_product_id
  left join product_brands on product_brands.ch_id = products.ch_brand_id

Sample data would be good..

CodePudding user response：

FINALLY! Got it to work.

With @P.Salmon suggestion to store the subquery result in a view, I then did a cross join on that view and use the results in the WHERE clause of the main query.

But that led me to now simply skip the view and the true final solution is to put the subquery in the cross join thus skipping the view. Sleek and VERY performant.

Final query with subquery in the croos join

select
  product_listings.*
from
  product_listings
  cross join (
    select
      record_id
    from
      translations
    where
      translations.locale = 'en_CA'
      and (
        translations.table = 'product_listings'
        or translations.table = 'product_categories'
        or translations.table = 'products'
        or translations.table = 'product_brands'
      )
      and MATCH (translations.translation) AGAINST (' jack*' IN BOOLEAN MODE)
  ) as vids
  left join product_categories on product_categories.ch_id = product_listings.ch_vintage_id
  left join products on products.ch_id = product_categories.ch_product_id
  left join product_brands on product_brands.ch_id = products.ch_brand_id
where
  product_listings.ch_id = vids.record_id
  or product_categories.ch_id = vids.record_id
  or products.ch_id = vids.record_id
  or product_brands.ch_id = vids.record_id
order by
  product_brands.ch_id desc,
  products.ch_id desc;