Как присоединить несколько таблиц во вложенном запросе?

Question

[Станислав Шабалин]

Так как я пока новичок в sql, то самостоятельно не могу найти способ как устранить ошибку ниже.
Пытаюсь вывести посты с ценами из присоединенной таблицы wc_product_meta_lookup, для которых есть отметка конвертировать в рубли или ее нет вовсе. Эти таблицы тоже пытаюсь присоединить и тут как раз есть проблема.
Вообще, запрос формирования цен корректен в целом ?

SELECT 
	wp_posts.ID, 
    wp_posts.post_title, 
    wc_product_meta_lookup.minPrice, 
    wc_product_meta_lookup.maxPrice 
FROM 
	wp_posts 
	LEFT JOIN wp_term_relationships tr ON (
		wp_posts.ID = wp_term_relationships.object_id
	) 
	LEFT JOIN (
		SELECT 
			product_id, 
			(min_price * GREATEST(1, t2.meta_value*80, t3.meta_value*80)) as minPrice, 
			(max_price * GREATEST(1, t2.meta_value*80, t3.meta_value*80)) as maxPrice 
		FROM 
            wp_wc_product_meta_lookup as t1 
            LEFT JOIN wp_postmeta as t2 ON t1.product_id = t2.post_id 
                AND t2.meta_key = 'convert_to_rub' 
                AND t2.meta_value = 1 
            LEFT JOIN wp_termmeta as t3 ON wp_term_relationships.term_taxonomy_id = t3.term_id 
                AND t3.meta_key = 'convert_to_rub' 
                AND t3.meta_value = 1 

	) wc_product_meta_lookup ON (
		wp_posts.ID = wc_product_meta_lookup.product_id
	) 
WHERE 
	1 = 1 
	AND wp_term_relationships.term_taxonomy_id IN ( 83, 85 ) 
	AND wp_posts.post_type = 'product' 
	AND wc_product_meta_lookup.minPrice > 0 
GROUP BY 
	wp_posts.ID 
ORDER BY 
	wp_posts.post_name ASC

Ошибка такая:

Unknown column 'wp_term_relationships.term_id' in 'on clause'

Comments

[Максим]

тут непонятно зачем вводить wp_wc_product_meta_lookup as t1 таблицу вообще, она что-то должна отсечь?
Сейчас мне видится как просто безполезный объект.

Нет связи между wp_termmeta as t3 и wp_wc_product_meta_lookup as t1 как основной таблицей этого подзапроса, что приведет к некорректной а самое страшное непредсказуемой выборке.

Вердикт - выборка неверна

[Станислав Шабалин]

Максим, эх а я так верил, что истина где-то рядом.

wp_wc_product_meta_lookup Нужна мне для выборки мин и Макс цены у товара. С главной таблицей wp_posts связана через wp_posts.ID = wc_product_meta_lookup.product_id. Внутри этой выборки ещё выполняю два запроса к другим таблицам, чтобы найти пару - мета ключ : мета значение => convert_to_rub : 1 и вычисляю новую цену на лету. Я так понимаю, тут разновидностью join не обойтись?

[Максим]

Станислав Шабалин, ну тут непонятна логика:
wp_wc_product_meta_lookup as t1
LEFT JOIN wp_postmeta as t2 ON t1.product_id = t2.post_id
Это связь частично понятна, не очень понятно почему left и он не экранирован тут t2.meta_value*80
Но вот эта сцепка LEFT JOIN wp_termmeta as t3 ON tr.term_taxonomy_id = t3.term_id никак не завязана на таблицу wp_wc_product_meta_lookup as t1 и соответственно для каждой строки из tr.term_taxonomy_id будет сформирована строка product_id,
(min_price * GREATEST(1, t2.meta_value*80, t3.meta_value*80)) as minPrice,
(max_price * GREATEST(1, t2.meta_value*80, t3.meta_value*80)) as maxPrice
И какая из них корректна будет сложно понять

Тут надо понимать данные и ввести/сцепить таблицу wp_term_relationships tr и wp_wc_product_meta_lookup as t1

Сейчас я попробую это сделать изменив свой ответ, но гарантий что я все учту - нет =(

[Максим]

Станислав Шабалин, обновил ответ, попробуйте

[Станислав Шабалин]

Максим, спасибо вам огромное. Вы уже не первый раз мне помогаете. Буду у ноута вечером - отпишусь. Самому мне долго все это даётся , к сожалению, но в сути почти разобрался, есть вопросы с объединением и алиасами. Благодарю за помощь

[Станислав Шабалин]

Максим , запрос срабатывает, но мне удаленная таблица связей нужна. Я без нее никак. Эта таблица нужна, чтобы выбрать нужную категорию товаров. Вообщем, wp_term_relationships связана с  wp_posts через object_id->ID.
А дальше я через вторую колонку wp_term_relationships.term_taxonomy_id связываю запрос с таблицей  wp_termmeta.term_id (t3) и проверяю t3.meta_key = 'convert_to_rub'.

[Станислав Шабалин]

wp_posts:

ID | post_title   | ...
----------------------
10| H5030001  |
11| Y1001002  |
30| H5030005  |

wp_term_relationships: (таблица связей с категориями/таксономиями)
* object_id ключ для wp_posts->ID и wp_postmeta->post_id
* term_taxonomy_id ключ для wp_termmeta->term_id

object_id | term_taxonomy_id
-----------------------------------
10           | 3
11           | 2
30           | 3

 term_taxonomy_id = 2 это например Обои бумажные,
 term_taxonomy_id = 3 это например Обои флизелиновые

wc_product_meta_lookup (таблица товаров с ценами):
* product_id - ключ для wp_posts->ID

product_id | min_price | max_price | ...
---------------------------------------------
10              | 30            | 35             |
11              | 5              | 5               |
30              | 15000         | 20000.      |

wp_postmeta (таблица доп метаданных для постов):

post_id | meta_key         | meta_value |
---------------------------------------------
10        | convert_to_rub | 1                 |
30        | convert_to_rub | 0                 |

 * Товар  H5030001 надо конвертировать по курсу

wp_termmeta (таблица доп метаданных для категорий/таксономий)

term_id | meta_key         | meta_value |
---------------------------------------------
2       | convert_to_rub  | 1          |

 * Категорию Обои бумажные надо конвертировать по курсу

[Максим]

Станислав Шабалин, Я Вашу таблицу не удалял, а перенес в подзапрос с Join оператором
JOIN wp_term_relationships tr ON (t1.product_id = tr.object_id AND tr.term_taxonomy_id IN ( 83, 85 ) )

Так как Вы используете только meta_value из таблиц t2 и t3 то GREATEST Вам тоже не надо. Итого перепишу в коммент еще раз (умножение на 80 для меня загадка, но ок)

SELECT 
  wp_posts.ID, 
  wp_posts.post_title, 
  (min_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) minPrice, 
  (max_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) maxPrice 
FROM 
  wp_posts 
           JOIN wp_wc_product_meta_lookup wpml ON (wp_posts.ID = wc_product_meta_lookup.product_id) 
           JOIN wp_term_relationships tr ON (wp_posts.ID = tr.object_id AND tr.term_taxonomy_id IN ( 83, 85 ) )
  LEFT JOIN wp_postmeta as t2 ON (tr.object_id = t2.post_id 
                                  AND t2.meta_key = 'convert_to_rub' 
                                  AND t2.meta_value = 1)
  LEFT JOIN wp_termmeta as t3 ON (tr.term_taxonomy_id = t3.term_id 
                                  AND t3.meta_key = 'convert_to_rub' 
                                  AND t3.meta_value = 1)
WHERE 
  1 = 1 
  AND wp_posts.post_type = 'product' 
  AND wc_product_meta_lookup.minPrice > 0 
--GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_name ASC

Получилось както так, поменял t1.product_id = t2.post_id на tr.object_id = t2.post_id, не пугайтесь, это одно и тоже ;-)

[Станислав Шабалин]

Максим, все работает, но вот проверка условия minPrice > 0 вызывает ошибку. Вроде Алисас создан, но ругается.

Unknown column 'minPrice' in 'where clause'

SELECT 
  wp_posts.ID, 
  wp_posts.post_title, 
  (wpml.min_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) minPrice, 
  (wpml.max_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) maxPrice 
FROM 
  wp_posts 
           JOIN wp_wc_product_meta_lookup wpml ON (wp_posts.ID = wpml.product_id) 
           JOIN wp_term_relationships tr ON (wp_posts.ID = tr.object_id AND tr.term_taxonomy_id IN ( 83, 85 ) )
  LEFT JOIN wp_postmeta as t2 ON (tr.object_id = t2.post_id 
                                  AND t2.meta_key = 'convert_to_rub' 
                                  AND t2.meta_value = 1)
  LEFT JOIN wp_termmeta as t3 ON (tr.term_taxonomy_id = t3.term_id 
                                  AND t3.meta_key = 'convert_to_rub' 
                                  AND t3.meta_value = 1)
WHERE 
  1 = 1 
  AND wp_posts.post_type = 'product' 
  AND minPrice > 0 
--GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_name ASC

[Станислав Шабалин]

Максим, и еще. Я не могу отказаться от

LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)

и

AND wp_term_relationships.term_taxonomy_id IN (83,85) )

в проверке WHERE , так как это запрос стороннего плагина фильтра и в нем формируется список таксономий. Я лишь хочу этот запрос дополнить своей таблицей, где добавляю к фильтру minPrice и maxPrice.

Вот основное тело запроса:

SELECT SQL_CALC_FOUND_ROWS wp_posts.ID
FROM wp_posts
LEFT JOIN wp_term_relationships
ON (wp_posts.ID = wp_term_relationships.object_id)
WHERE 1=1
AND wp_posts.ID NOT IN (25220)
AND ( wp_posts.ID NOT IN (
SELECT object_id
FROM wp_term_relationships
WHERE term_taxonomy_id IN (0) )
AND wp_term_relationships.term_taxonomy_id IN (83,85) )
AND wp_posts.post_type = 'product'
AND (wp_posts.post_status = 'publish'
GROUP BY wp_posts.ID
ORDER BY wp_posts.post_name ASC
LIMIT 0, 20

[Максим]

Станислав Шабалин, >Unknown column 'minPrice' in 'where clause'
Прям до слез, не можете поправить?

SELECT 
  wp_posts.ID, 
  wp_posts.post_title, 
  (wpml.min_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) minPrice, 
  (wpml.max_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) maxPrice 
FROM 
  wp_posts 
           JOIN wp_wc_product_meta_lookup wpml ON (wp_posts.ID = wpml.product_id) 
           JOIN wp_term_relationships tr ON (wp_posts.ID = tr.object_id AND tr.term_taxonomy_id IN ( 83, 85 ) )
  LEFT JOIN wp_postmeta as t2 ON (tr.object_id = t2.post_id 
                                  AND t2.meta_key = 'convert_to_rub' 
                                  AND t2.meta_value = 1)
  LEFT JOIN wp_termmeta as t3 ON (tr.term_taxonomy_id = t3.term_id 
                                  AND t3.meta_key = 'convert_to_rub' 
                                  AND t3.meta_value = 1)
WHERE 
  1 = 1 
  AND wp_posts.post_type = 'product' 
  AND wpml.min_price > 0 
--GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_name ASC

[Максим]

Станислав Шабалин, >и еще. Я не могу отказаться от

мне лень разбераться, если Вам надо только добавить min and max вставте такой селект:

LEFT JOIN (
    SELECT 
      product_id, 
      (t1.min_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) as minPrice, 
      (t1.max_price * coalesce(t2.meta_value*80, t3.meta_value*80,1)) as maxPrice 
    FROM 
            wp_wc_product_meta_lookup as t1 
            JOIN wp_term_relationships tr ON (t1.product_id = tr.object_id AND tr.term_taxonomy_id IN ( 83, 85 ) )
            LEFT JOIN wp_postmeta as t2 ON tr.object_id = t2.post_id 
                AND t2.meta_key = 'convert_to_rub' 
                AND t2.meta_value = 1 
            LEFT JOIN wp_termmeta as t3 ON tr.term_taxonomy_id = t3.term_id 
                AND t3.meta_key = 'convert_to_rub' 
                AND t3.meta_value = 1 
     where t1.min_price > 0

  ) wc_product_meta_lookup ON (
    wp_posts.ID = wc_product_meta_lookup.product_id
  )

Добавите с Left - будут пустые строки, добавите Join пустых не будет

[Станислав Шабалин]

Максим,

не можете поправить?

То что Вы предложили это мне понятно и так. Я же не просто так спросил. Мне не ясно как можно указать именно алиас minPrice в where моего запроса. В дальнейшем я буду его дорабатывать для фильтра с выбором порога цены.

[Станислав Шабалин]

Максим, суть понятна - это главное, дальше буду пробовать внедрить в код. Спасибо Вам.

[Максим]

Станислав Шабалин, Если у Вас будет результирующий запрос с group by можно having использовать ;-)

Answer 1

[Максим]

Запрос вцелом непонятен, но пока ошибка в алиасах
UPD.
Попробуйте так:

SELECT 
  wp_posts.ID, 
    wp_posts.post_title, 
    wc_product_meta_lookup.minPrice, 
    wc_product_meta_lookup.maxPrice 
FROM 
  wp_posts 
  LEFT JOIN (
    SELECT 
      product_id, 
      (min_price * GREATEST(1, coalesce(t2.meta_value*80,1), coalesce(t3.meta_value*80),1)) as minPrice, 
      (max_price * GREATEST(1, coalesce(t2.meta_value*80,1), coalesce(t3.meta_value*80),1)) as maxPrice 
    FROM 
            wp_wc_product_meta_lookup as t1 
            LEFT JOIN wp_postmeta as t2 ON t1.product_id = t2.post_id 
                AND t2.meta_key = 'convert_to_rub' 
                AND t2.meta_value = 1 
            JOIN wp_term_relationships tr ON (t1.product_id = tr.object_id AND tr.term_taxonomy_id IN ( 83, 85 ) )
            LEFT JOIN wp_termmeta as t3 ON tr.term_taxonomy_id = t3.term_id 
                AND t3.meta_key = 'convert_to_rub' 
                AND t3.meta_value = 1 

  ) wc_product_meta_lookup ON (
    wp_posts.ID = wc_product_meta_lookup.product_id
  ) 
WHERE 
  1 = 1 
  AND wp_posts.post_type = 'product' 
  AND wc_product_meta_lookup.minPrice > 0 
--GROUP BY wp_posts.ID 
ORDER BY wp_posts.post_name ASC

Пока убрал GROUP BY wp_posts.ID если будут дублиаты, с ними надо разобраться кто дублирует и почему, может надо наложить еще условий