Feed Produse Google (Obsentum)
The snippet can be accessed without any authentication.
Authored by
Cosmin-Romeo TANASE
google.sql 6.39 KiB
SELECT
p.id as `id`,
pt.name as `title`,
pt.description as `description`,
IF(pv.on_hand-pv.on_hold > 0, 'in stock', 'out of stock') as `availability`,
'new' as `condition`,
CONCAT(CEIL(original_price / 100), ' RON') as `price`,
CONCAT('https://obsentum.com/products/', pt.slug) as `link`,
(
SELECT IF(`path` LIKE '%http%', `path`, CONCAT('https://obsentum-prod.ams3.digitaloceanspaces.com/sylius_shop_product_original/', `path`)) FROM sylius_product_image pi WHERE pi.owner_id = p.id LIMIT 1
) as `image_link`,
(
SELECT stt.name FROM sylius_taxon_translation stt
LEFT JOIN sylius_taxon st ON st.id = stt.translatable_id
LEFT JOIN sylius_product_taxon spt ON spt.taxon_id = st.id
WHERE
spt.product_id = p.id
AND st.parent_id = 101
LIMIT 1
) as `brand`,
(
SELECT (IF(`path` LIKE '%http%', `path`, CONCAT('https://obsentum-prod.ams3.digitaloceanspaces.com/sylius_shop_product_original/', `path`))) FROM sylius_product_image pi WHERE pi.owner_id = p.id AND pi.id != (SELECT pii.id FROM sylius_product_image pii WHERE pii.owner_id = p.id LIMIT 1) LIMIT 1
) as `additional_image_link`,
'adult' as `age_group`,
( SELECT REPLACE(REPLACE(REPLACE(json_value, '"', ''), '[', ''), ']', '') FROM sylius_product_attribute_value pav WHERE pav.product_id = p.id AND attribute_id = 16 ) as `gender`,
(
SELECT
CASE
WHEN st.id = 77 THEN '479'
WHEN st.id = 78 THEN '479'
WHEN st.id = 79 THEN '479'
WHEN st.id = 81 THEN '479'
WHEN st.id = 94 THEN '543598'
WHEN st.id = 82 THEN '486'
WHEN st.id = 83 THEN '108'
WHEN st.id = 86 THEN '2526'
WHEN st.id = 87 THEN '5976'
WHEN st.id = 88 THEN '2592'
WHEN st.id = 89 THEN '6262'
WHEN st.id = 90 THEN '528'
WHEN st.id = 92 THEN '474'
WHEN st.id = 93 THEN '2592'
WHEN st.id = 168 THEN '2592'
WHEN st.id = 167 THEN '6104'
WHEN st.id = 95 THEN '2747'
WHEN st.id = 96 THEN '474'
WHEN st.id = 97 THEN '592'
WHEN st.id = 98 THEN '3898'
WHEN st.id = 99 THEN '588'
WHEN st.id = 100 THEN '3898'
WHEN st.id = 159 THEN '2571'
WHEN st.id = 164 THEN '2645'
WHEN st.id = 165 THEN '6304'
WHEN st.id = 166 THEN '2765'
WHEN st.id = 84 THEN '479'
WHEN st.id = 171 THEN '479'
WHEN st.id = 85 THEN '482'
WHEN st.id = 187 THEN '8452'
WHEN st.id = 186 THEN '7467'
WHEN st.id = 182 THEN '474'
WHEN st.id = 181 THEN '526'
WHEN st.id = 184 THEN '2592'
ELSE CONCAT('(',name,')')
END
FROM sylius_product_taxon spt
LEFT JOIN sylius_taxon_translation stt ON stt.translatable_id = taxon_id
LEFT JOIN sylius_taxon st ON st.id = taxon_id
WHERE product_id = p.id AND parent_id is not null AND parent_id != 101
ORDER BY spt.position
LIMIT 1
) as `google_product_category`,
(
SELECT
GROUP_CONCAT(`name` SEPARATOR ' > ')
FROM sylius_product_taxon spt
LEFT JOIN sylius_taxon_translation stt ON stt.translatable_id = taxon_id
LEFT JOIN sylius_taxon st ON st.id = taxon_id
WHERE product_id = p.id AND st.parent_id != 101 AND st.id != 101
ORDER BY spt.position DESC
) as `product_type`,
CONCAT(CEIL(price / 100), ' RON') as `sale_price`,
( SELECT ( CASE
WHEN json_value = '["b43201f8-3589-11eb-a4de-72b20b814dd2"]' THEN '10 ml'
WHEN json_value = '["b4321558-3589-11eb-b35c-72b20b814dd2"]' THEN '15 ml'
WHEN json_value = '["b43219d6-3589-11eb-813d-72b20b814dd2"]' THEN '30 ml'
WHEN json_value = '["b4321e40-3589-11eb-88d4-72b20b814dd2"]' THEN '50 ml'
WHEN json_value = '["b432225a-3589-11eb-8c3e-72b20b814dd2"]' THEN '75 ml'
WHEN json_value = '["b432269c-3589-11eb-adf1-72b20b814dd2"]' THEN '100 ml'
WHEN json_value = '["3db11bae-3679-11eb-a652-72b20b814dd2"]' THEN '250 ml'
WHEN json_value = '["0f461c1e-3891-11eb-8ad3-72b20b814dd2"]' THEN '500 ml'
WHEN json_value = '["74ba7c5a-3938-11eb-a547-72b20b814dd2"]' THEN '80 ml'
WHEN json_value = '["54976110-39a4-11eb-90d6-72b20b814dd2"]' THEN '200 ml'
WHEN json_value = '["5497691c-39a4-11eb-82ab-72b20b814dd2"]' THEN '150 ml'
WHEN json_value = '["dcd4136e-3a1e-11eb-a7ff-72b20b814dd2"]' THEN '125 ml'
WHEN json_value = '["b06d5c7c-3ded-11eb-ae96-72b20b814dd2"]' THEN '120 ml'
WHEN json_value = '["b06d6e24-3ded-11eb-8f6b-72b20b814dd2"]' THEN '1250 ml'
WHEN json_value = '["b06d73d8-3ded-11eb-b00f-72b20b814dd2"]' THEN '2500 ml'
WHEN json_value = '["b06d7860-3ded-11eb-8fa7-72b20b814dd2"]' THEN '5000 ml'
WHEN json_value = '["b06d7d06-3ded-11eb-b797-72b20b814dd2"]' THEN '450 ml'
WHEN json_value = '["ecaac274-3e2e-11eb-998a-766302293fef"]' THEN '1250 ml'
WHEN json_value = '["7b40bff6-3e35-11eb-8fd8-766302293fef"]' THEN '700 ml'
WHEN json_value = '["244365f8-42c9-11eb-87ec-72b20b814dd2"]' THEN '25 ml'
WHEN json_value = '["d2429eb2-49f9-11eb-b439-72b20b814dd2"]' THEN '300 ml'
WHEN json_value = '["38f4ea96-872d-11eb-bd13-2ae2c238c0b9"]' THEN '20 ml'
WHEN json_value = '["57d73bca-8cb5-11eb-875e-2ae2c238c0b9"]' THEN '1000 ml'
WHEN json_value = '["abbb3b38-8cb5-11eb-8a0b-2ae2c238c0b9"]' THEN '500 ml + 500 ml'
ELSE ''
END) FROM sylius_product_attribute_value pav WHERE pav.product_id = p.id AND attribute_id = 19 ) as `size`,
( SELECT REPLACE(REPLACE(REPLACE(json_value, '"', ''), '[', ''), ']', '') FROM sylius_product_attribute_value pav WHERE pav.product_id = p.id AND attribute_id = 14 ) as `scent`
FROM
sylius_product p
LEFT JOIN
sylius_product_variant pv ON pv.product_id = p.id
LEFT JOIN
sylius_product_translation pt ON pt.translatable_id = p.id #Check what you need to link this with
LEFT JOIN
sylius_channel_pricing pp ON pp.product_variant_id = pv.id
WHERE
p.enabled = 1
AND pt.name is not null
AND original_price > 0
Please register or sign in to comment