MySQL JOIN из трёх таблиц без разбора данных

VANHALEN

Новичок
Есть такая ситуация.. Joomshopping. Делаю выгрузку для яндекс маркета. Есть три таблицы products, categories и products_to_categories. Не самый может удобный вариант для устройства каталога, но разработчикам видимо виднее, да и их мотивацию я понимаю кое в чём. Изначально никаких проблемм не было.. Перечислил все категории, которые опубликованы, потом вывел все товары, которые опубликованы и сопоставил их с категориями на лету. И долгое время всё отлично работало, пока не попался хитрый сайт, на котором хозяин снял с публикации категории, а товары которые в них находились, не снял с публикации. Получается, что я перечислил все реально опубликованные категории и реально опубликованные товары, а при их сопоставлении товара с категорией он ссылается на категорию, которая отключена и соответсвенно отсутствует в перечисленном массиве. Ситуация редкая, оюычно люди снимают с публикации отдельные товары.

Вобщем нужно при выборке товаров из таблицы учитывать не только опубликован он или нет, а ещё джойнить 2 таблицы и смотреть чё там к чему.
Из products нам нужен для этого только product_id и product_publich, из categories - category_id и category_publish. В products_to_categories у нас просто спопоставление в двух полях: product_id и category_id

Попытаюсь спросить по-русски
Вытащить из products всё, где product_publish = 1 и посмотреть чтобы в таблице products_to_categories каждый id вытащенного товара был сопоставлен (в одной строке) только той категории, у которой в таблице categories, в поле category_publish тоже = 1. Вот как то так это по-человечески звучит. Это позволит не разбирать данные массивов, а просто не выбирать лишнего.

Вопрос в следующем: как звучит этот запрос на своём языке. Не приходилось пользоваться джойнами. Дуб дубом.
 

VANHALEN

Новичок
Посмотрел как это в теории делается, попытался изобразить сам

SELECT * FROM `shop_jshopping_products` JOIN shop_jshopping_products_to_categories ON shop_jshopping_products.product_id = shop_jshopping_products_to_categories.product_id JOIN shop_jshopping_categories ON shop_jshopping_products_to_categories.category_id = shop_jshopping_categories.category_id WHERE shop_jshopping_products.product_publish = '1' AND shop_jshopping_categories.category_publish = '1'"

Ну во-первых он всё равно спотыкнулся 4 раза из 887 товаров в том месте, где один товар был сразу в нескольких категориях. А вот это уже не такая редкость. Ну допустим это проблема наверное не запроса, а обработки данных. Но появилась ещё одна проблема. В products и categories много полей с одинаковыми названиями (алиас, описание, ещё что-то) и при выводе он заполнил поля из последней заджойненой таблицы. Как их правильно выводить то?

Пока что просто перевернул JOIN в обратную сторону categories -> products_to_categories -> products. Но исчезла не проблема с полями, а её последствия. Тоесть и названия и описания заполняются из последней таблицы, как и нужно. Но я понимаю, что это не правильное решение.

По поводу ошибки, где скрипт спотыкался, пытаясь понять к какой категории относится товар. Ситуация следущая: есть 2 товара, которые находятся одновременно в 2х категориях, одна из которых снята с публикации, но не удалена. Вот оттуда и 4 ошибки. Что делать не заню
 
Последнее редактирование:

ksnk

прохожий
Ничего не понятно
http://sqlfiddle.com/#!9/90fcd3/1
вот пример. Выкинуты все "ненужные" поля. Что-нибудь не устраивает ? Где пример должен "спотыкаться" и почему? На каких тестовых данных это проявляется ?
 

VANHALEN

Новичок
Ничего не понятно
http://sqlfiddle.com/#!9/90fcd3/1
вот пример. Выкинуты все "ненужные" поля. Что-нибудь не устраивает ? Где пример должен "спотыкаться" и почему? На каких тестовых данных это проявляется ?
Немного сократил свой перевёрнутый вариант.

SELECT * FROM `shop_jshopping_categories` JOIN shop_jshopping_products_to_categories USING (category_id) JOIN shop_jshopping_products USING (product_id) WHERE shop_jshopping_categories.category_publish = '1' AND shop_jshopping_products.product_publish = '1'

У меня в цикле товар сопоставляется с категорией, взятой и другого, уже готового массива. Может в этом дело. Я просто физически ещё не успел отделить мух от котлет. А поскольку я названия и алиасы категорий получаю в этом же заджойненом запросе, то я сейчас и возьму их из него-же, тогда посмотрим. Я просто только вот пару часов назад начал учится пользоваться всякими джойнами и ещё не разделил в выводе поля с одинаковыми названиями. Вобщем, ща разделю, посмотрим тогда.

Ну и что получается, что я сходу запрос с джойнами правильно составил?
 
Последнее редактирование:

VANHALEN

Новичок
Всё! Работает!

Коротко опишу в чём была херня. Джойн я составил изначально правильно, хотя и делал это первый раз в жизни. Ошибки были:
url формировался из алиаса, который я доставл в другом запросе, загонял их в массив, потом перечислял категории. Из этого же запроса его и брал. Вобщем индексы не совпадали. После того, как я переназвал в заджойненом запросе поле и в url подтягивал уже оттуда его, ошибка с offset ушла. И действительно товар начал показываться дважды, в двух разных категориях, с правильными url.

Вторая ошибка была родственная. Товар (который в нескольких включенных категориях) по ссылкам находился и открывался в разных категориях, а поле category_id , которым яндекс пользуется, писал также из массива созданного ранее. Когда переобозвал его в новом запросе и взял оттуда, то и яндекс его стал видеть как положено.

Попутно решил ещё одну проблему. offer_id брался из id товара, следовательно в выдаче яндексу могло оказаться 2 и более предложений с одинаковым id, а вроде как он этого не любит. Заменил обычным счётчиком.

Как переобозвать поля с одинаковыми названиями в разных таблицах нашёл здесь

Теперь, когда я попробовал что такое JOIN можно и характеристики товаров подтянуть, это ещё минимум 2 таблицы джойнить. Боюсь тяжеловато будет, а как оптимизировать я не представляю. Наверное не генерировать файл при запросе, а создавать и сохранять, но это уже не очень элегантно
 

VANHALEN

Новичок
Сразу спрошу, пока всё так хорошо пошло. Запрос получился не маленький, учитывая что в запросе ещё переменные из настроек, которые подставляют префикс таблиц. Я знаю что желательно перечислять поля, а не тянуть все сразу, но всё равно самые большие поля (с описанием) я использую. Так вот, есть ли вариант как то пакетно присвоить всем полям индексы что-ли, чтобы явно их в запросе не переименовывать через AS? Или есть варианты выводить их в цикле, указывая из какой таблицы выводится поле?
 

ksnk

прохожий
Категорий много ? Если не более 1000 штук, то, imho, энергетически выгоднее иметь уже прочитанный список категорий с названиями и получать запросом только индексы категорий.
Возможно, проще будет так
SQL:
SELECT shop_jshopping_products.*, GROUP_CONCAT(shop_jshopping_categories.category_id) as category_id FROM `shop_jshopping_categories`
JOIN shop_jshopping_products_to_categories USING (category_id)
JOIN shop_jshopping_products USING (product_id)
WHERE shop_jshopping_categories.category_publish = '1' AND shop_jshopping_products.product_publish = '1'
group by shop_jshopping_products.product_id
Список категорий будет через запятую, получение имен категорий будет на стороне php.
Из плюсов - нет проблем с дублированием товаров для разных категорий.
 
Последнее редактирование:

VANHALEN

Новичок
Список категорий будет через запятую, получение имен категорий будет на стороне php.
Из плюсов - нет проблем с дублированием товаров для разных категорий.
Не, как раз через запятую не нужно. Он в одном проходе цикла должен писать только один id категории. Отсюда же и вопрос с дублированием. Как раз они и должны дублироваться, если в каталоге так. Тоесть яндекс должен разложить один товар хоть в 10 категорий, если на сайте так сделано. А дублирование у меня было потому, что id товара в xml выдаче было равно id товара в базе, поэтому они могли повторятся, если в разных категориях находится товар. Сейчас id это просто счётчик. Вобщем сейчас всё работает как часы. И маркет и турбо страницы.

Категорий не больше 1000? Ну вы блин даёте :) Это же кто будет админить такой магазин? С какой памятью человек? Да и для юзабилити посетителя это вообще никак. Есть же фильтры в конце концов, а в 1000 категорий, покупатель запутается и уйдёт нафиг.

Вобщем вопрос остался толко один, как переобозвать поля пакетно, не перечисляя вручную. А то я тут ещё 2 таблицы заджойнить решил, чтобы характеристики выводить. Уж больно длинно получается. Хотя, какая мне разница если оно работает? Просто потом чтобы в своём коде разобраться было проще.

За GROUP_CONCAT спасибо! Годная штука. Пригодится обязательно в другом месте.
 

AnrDaemon

Продвинутый новичок
Есть три таблицы products, categories и products_to_categories. Не самый может удобный вариант для устройства каталога
Единственный адекватный вариант для устройства товарного каталога.
Товар могут искать в разных категориях, товар может ОТНОСИТЬСЯ к разным категориям.

Категорий не больше 1000? Ну вы блин даёте :) Это же кто будет админить такой магазин? С какой памятью человек? Да и для юзабилити посетителя это вообще никак.
366 категорий. И это специализированный магазин.
Не все категории, скажем так, конечные. Но таких меньшинство.
 

VANHALEN

Новичок
Не забудь это amazon-у и aliexpress-у сказать. Они-то, бедные, не знают, что от них все уходят.
Там как раз не 1000 категорий а в разы меньше. Зато продавцов и товаров одинаковых, или с виду одинаковых до хрена. Одинаковые категории, в них одинаковые товары. И вот как раз оттуда все проблемы растут. Купил за 200 рублей блютуз приёмник для наушников. Всё зашибись! Через пару месяцев купил точно такой же за 100 рублей в машину. А он сволочь - моно! Так что ещё как уходят от них. Вон на ютюбе спецом ссылки на товар дают, чтобы не попутать две одинаковых зерни с разными ценами и свойствами
 
Сверху