Search code examples
mysqlduplicatessubquery

Reuse MySQL subquery in various WHERE clause without subquery duplication

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.

Table join structure

product_brands(1) <-> (n)products(1) <-> (n)product_categories(1) <-> (n)product_listings

The set of ids returned by the subquery can be for any of the 4 tables above.

Subquery returning the sets of ids

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

Main query here using the ids in WHERE clauses

select
  product_listings.*
from
  product_listings
  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 in (5951765, 252242)      <---| Replace these fixed ids
    or product_categories.ch_id in (5951765, 252242) <---| with the "record_id" set
    or products.ch_id in (5951765, 252242)           <---| returned by the subquery
    or product_brands.ch_id in (5951765, 252242);    <---|

Both queries works perfectly independently. But cannot succesfully merge them into one.

Only dirty way I found is to repeat the subquery at each WHERE clause. Tried it and it works, but doubtfully the most effective and optimized way to do it. Tried using variable, but only one value can be stored - unfortunately not a viable option.

Spent countless hours ressearching on how to avoid repeating a subquery and been rewriting those in many ways, but still can't get it to work.

Any suggestion on how to integrate the subquery elegantly and efficiently?

Currently working with Mysql Ver 14.14 Distrib 5.7.37, for Linux (x86_64)

UPDATE 2022/04/16: Adding sample data of translations table and expected results of both queries

Sample of the translations table with those 2 ids

+-----------+----------------+--------+-------------------------------+
| record_id | table          | locale | translation                   |
+-----------+----------------+--------+-------------------------------+
|   5951765 | products       | en_CA  | Jack Daniel's                 |
|    252242 | product_brands | en_CA  | Dixon's & Jack Daniel's       |
+-----------+----------------+--------+-------------------------------+

Here is the subquery response

+-----------+
| record_id |
+-----------+
|   5951765 |
|    252242 |
+-----------+

And a the main query response (final expected results) using the set of hardcoded ids. I modified the select clause to return specific columns to make the table readable instead of the '*'. First 2 columns are the located set of ids in the products and product_brands table and 2 other one are from the corresponding product_listings record extracted via the joins.

+------------+----------+--------------+-----------------+
| product_id | brand_id | listing_cspc | listing_format |
+------------+----------+--------------+-----------------+
|    5951765 |  5936861 | 798248       | 6x750           |
|    5951765 |  5936861 | 545186       | 6x750           |
|    5951956 |   252242 | 400669       | 12x750          |
|    5951955 |   252242 | 400666       | 12x750          |
|    5951701 |   252242 | 437924       | 12x750          |
|    5951337 |   252242 | 20244        | 6x750           |
|    5950782 |   252242 | 65166        | 12x750          |
|    5950528 |   252242 | 104941       | 12x750          |
|    5949763 |   252242 | 13990091     | 12x750          |
|    5949750 |   252242 | 614064       | 12x750          |
...
|    1729121 |   252242 | 280248       | 12x750          |
|    1729121 |   252242 | 36414        | 12x750          |
+------------+----------+--------------+-----------------+

As you can see, the ids from the subquery are matching different column. In this case 5951765 is the products.ch_id and the 252242 is the product_brands.ch_id.

Below is a visual representation of what I'm trying to achieve considering the current (1):(n) relations of the tables Inout/output

Solution

  • 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;