magento-snippets.sql

Export all Blocks : 
mysql -umag2user -p -h database.local magento2 -B -e "SELECT block_id,identifier from cms_block cb JOIN cms_block_store  cbs ON cb.row_id =  cbs.row_id WHERE cbs.store_id = 6;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > block_categories.csv

#php bin/magento queue:consumers:start product_action_attribute.update


----------------

#Step to export product PDP content: 

SELECT * FROM eav_attribute WHERE attribute_code='pdp_pagebuilder_content'

1. Use HS to create CSV file - db magento_uat_9_march, File name pcontent_source.csv

SELECT cpe.sku,st.code as store_view_code, CONCAT('pdp_pagebuilder_content=', cpet.value)  AS additional_attributes  FROM catalog_product_entity_text cpet
JOIN catalog_product_entity cpe ON cpe.row_id=cpet.row_id
JOIN store st ON st.store_id = cpet.store_id
WHERE cpet.attribute_id ='209'
AND cpet.store_id=17

Export grid rows, delimited text 
encloser ""
------------------
2. run ge getallblock.php to get all block id used in source
3. update block_mapping.csv file's target/prodId for each identifier. 

4. copy pcontent_source.csv and block_mapping.csv file to wsl img_extract folder 

5. run updateBlockId.php to create pcontent_import.csv

6. Import pcontent_import.csv

-------------------------

#DESC - NOT USED
SELECT cpe.sku,st.code as store_view_code, cpet.value AS description 
FROM catalog_product_entity_text cpet
JOIN catalog_product_entity cpe ON cpe.row_id=cpet.row_id
JOIN store st ON st.store_id = cpet.store_id
WHERE cpet.attribute_id ='75'
AND cpet.store_id=17
AND cpet.value IS NOT NULL
AND sku='VYPE-EPOD-FR-MG'

-------------------
#SHORT DESC

SELECT * FROM eav_attribute WHERE attribute_code='short_description'


SELECT cpe.sku,st.code as store_view_code, cpet.value AS short_description 
FROM catalog_product_entity_text cpet
JOIN catalog_product_entity cpe ON cpe.row_id=cpet.row_id
JOIN store st ON st.store_id = cpet.store_id
WHERE cpet.attribute_id ='76'
AND cpet.store_id=17
AND cpet.value IS NOT NULL

---------------------------------------
#NAME
SELECT cpe.sku,st.code as store_view_code, cpet.value AS name 
FROM catalog_product_entity_varchar cpet
JOIN catalog_product_entity cpe ON cpe.row_id=cpet.row_id
JOIN store st ON st.store_id = cpet.store_id
WHERE cpet.attribute_id ='73'
AND cpet.store_id=17
AND cpet.value IS NOT NULL


----------------------------------
#Get ALL IMAGE path for download
# Media gallery contains *all* image including base/small/thumbnail/hidden/additional
---------
SELECT DISTINCT cpemg.value 
FROM catalog_product_entity_media_gallery cpemg
JOIN catalog_product_entity_media_gallery_value cpemgv ON cpemg.value_id = cpemgv.value_id
JOIN catalog_product_entity cpe ON cpe.row_id=cpemgv.row_id
JOIN store st ON st.store_id = cpemgv.store_id
WHERE cpemg.attribute_id ='90'
AND cpemgv.store_id=17
AND cpemg.value IS NOT NULL
------------

SELECT cpe.sku,st.code as store_view_code, GROUP_CONCAT(DISTINCT cpemg.value SEPARATOR ', ') 
FROM catalog_product_entity_media_gallery cpemg
JOIN catalog_product_entity_media_gallery_value cpemgv ON cpemg.value_id = cpemgv.value_id
JOIN catalog_product_entity cpe ON cpe.row_id=cpemgv.row_id
JOIN store st ON st.store_id = cpemgv.store_id
WHERE cpemg.attribute_id ='90'
AND cpemgv.store_id=17
AND cpemg.value IS NOT NULL
#AND cpemgv.disabled=1
AND cpe.sku=10053469
GROUP BY cpe.sku

-----------------------------------
#BASE image

SELECT * FROM eav_attribute WHERE attribute_code LIKE "%image%"


SELECT cpe.sku,st.code as store_view_code, cpev.value
FROM catalog_product_entity_varchar cpev
JOIN catalog_product_entity cpe ON cpe.row_id=cpev.row_id
JOIN store st ON st.store_id = cpev.store_id
WHERE cpev.attribute_id ='87'
AND cpev.store_id=17
AND cpev.value IS NOT NULL
AND cpe.sku=10053469