Большой страшный sql запрос Интернет магазина для Яндекс маркета

SELECT 
    product.goods_code as 'id',
    'В наличии' as 'Статус товара',
    'Есть' as 'Доставка',
    ' ' as 'Стоимость доставки',
    ' ' as 'Срок доставки',
    'Есть' as 'Самовывоз',
    'Можно' as 'Купить в магазине без заказа',
    
    CASE 
        WHEN price.price = (
            select price 
            FROM shop_goods_base 
            where goods_id = product.id 
            AND base_id NOT IN (select id from `base` where hide_contacts = 1)
            ORDER BY price 
            DESC LIMIT 1
        ) 
            THEN CONCAT('https://site.ru/', category.path,'/', product.path) 
        ELSE 
            CONCAT('https://', (
                select domain from base where id =          
                    (
                        select base_id 
                        FROM shop_goods_base 
                        where goods_id = product.id 
                        AND base_id NOT IN (select id from `base` where hide_contacts = 1)
                        ORDER BY price 
                        DESC LIMIT 1
                    )
                ),
                '.site.ru/', 
                category.path, '/', product.path
            ) 
    END AS 'Ссылка на товар на сайте магазина',
    
    brand.name as 'Производитель',
    product.name as 'Название',  
    category.name as 'Категория',
    
    (
        select price 
        FROM shop_goods_base 
        where goods_id = product.id 
        AND base_id NOT IN (select id from `base` where hide_contacts = 1)
        ORDER BY price 
        DESC LIMIT 1
    ) as 'Цена',
    
    '' as 'Цена без скидки',
    'RUR' as 'Валюта',
    
    CONCAT('https://site.ru/u/shop/', SUBSTRING(product.goods_code, 1, 3), '/orig/', image.file) as 'Ссылка на картинку',
    product.description as 'Описание',
    
        CONCAT_WS(
        "",
        (
            select  GROUP_CONCAT('Цвет|', sc.header, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_color as sc
            ON sfv.int_value = sc.id
            where sfv.filter_set_id = 7 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Длина|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 9 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Ширина|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 35 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Высота|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 32 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Пустотность|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 196 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Тип|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 43 and sfv.goods_id = product.id
            limit 1
        ),
        
        (
            select  GROUP_CONCAT('Материал|', sfs.value, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_select as sfs
            ON sfv.int_value = sfs.id
            where sfv.filter_set_id = 18 and sfv.goods_id = product.id
            limit 1
        ),
        (
            select  GROUP_CONCAT('Диаметр|', sfv.float_value, ' ', shc.units, ';')
            from shop_filter_value as sfv
            LEFT JOIN shop_filter_set as shc
            ON sfv.filter_set_id = shc.id
            where sfv.filter_set_id = 8 and sfv.goods_id = product.id
            limit 1
        )
        
    ) as 'Характеристики товара',
    
    'Необходима предоплата.' as 'Условия продажи',
    'Есть' as 'Гарантия производителя',
    country.xart_header as 'Страна происхождения',
    product.goods_code as 'Штрихкод',
    '80' as 'bid'

FROM shop_goods as product

LEFT JOIN shop_goods_base as price
ON product.id = price.goods_id

LEFT JOIN shop_catalog_goods as cat_table
ON product.id = cat_table.goods_id

LEFT JOIN shop_catalog as category
ON cat_table.catalog_id = category.id

LEFT JOIN shop_brand as brand
ON product.brand_id = brand.id

LEFT JOIN shop_country as country
ON product.country_id = country.id

LEFT JOIN shop_image as image
ON product.id = image.goods_id

WHERE product.goods_code IN (1,2,3,4,5) 
GROUP BY product.goods_code

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

SELECT

product.goods_code as 'id',

'В наличии' as 'Статус товара',

'Есть' as 'Доставка',

' ' as 'Стоимость доставки',

' ' as 'Срок доставки',

'Есть' as 'Самовывоз',

'Можно' as 'Купить в магазине без заказа',

CASE

WHEN price.price = (

select price

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

)

THEN CONCAT('https://site.ru/', category.path,'/', product.path)

ELSE

CONCAT('https://', (

select domain from base where id =

(

select base_id

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

)

'.site.ru/',

category.path, '/', product.path

)

END AS 'Ссылка на товар на сайте магазина',

brand.name as 'Производитель',

product.name as 'Название',

category.name as 'Категория',

(

select price

FROM shop_goods_base

where goods_id = product.id

AND base_id NOT IN (select id from `base` where hide_contacts = 1)

ORDER BY price

DESC LIMIT 1

) as 'Цена',

'' as 'Цена без скидки',

'RUR' as 'Валюта',

CONCAT('https://site.ru/u/shop/', SUBSTRING(product.goods_code, 1, 3), '/orig/', image.file) as 'Ссылка на картинку',

product.description as 'Описание',

CONCAT_WS(

"",

(

select GROUP_CONCAT('Цвет|', sc.header, ';')

from shop_filter_value as sfv

LEFT JOIN shop_color as sc

ON sfv.int_value = sc.id

where sfv.filter_set_id = 7 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Длина|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 9 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Ширина|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 35 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Высота|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 32 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Пустотность|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 196 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Тип|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 43 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Материал|', sfs.value, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_select as sfs

ON sfv.int_value = sfs.id

where sfv.filter_set_id = 18 and sfv.goods_id = product.id

limit 1

(

select GROUP_CONCAT('Диаметр|', sfv.float_value, ' ', shc.units, ';')

from shop_filter_value as sfv

LEFT JOIN shop_filter_set as shc

ON sfv.filter_set_id = shc.id

where sfv.filter_set_id = 8 and sfv.goods_id = product.id

limit 1

)

) as 'Характеристики товара',

'Необходима предоплата.' as 'Условия продажи',

'Есть' as 'Гарантия производителя',

country.xart_header as 'Страна происхождения',

product.goods_code as 'Штрихкод',

'80' as 'bid'

FROM shop_goods as product

LEFT JOIN shop_goods_base as price

ON product.id = price.goods_id

LEFT JOIN shop_catalog_goods as cat_table

ON product.id = cat_table.goods_id

LEFT JOIN shop_catalog as category

ON cat_table.catalog_id = category.id

LEFT JOIN shop_brand as brand

ON product.brand_id = brand.id

LEFT JOIN shop_country as country

ON product.country_id = country.id

LEFT JOIN shop_image as image

ON product.id = image.goods_id

WHERE product.goods_code IN (1,2,3,4,5)

GROUP BY product.goods_code

Добавить комментарий Отменить ответ