How can I get all SKUs and Product URLs using only SQL Query?
Add a comment
|
2 Answers
Magento does not save product URLs completely in database tables. It saves only URL key, so you can get SKU and URL key directly from tables.
SKU is saved in catalog_product_entity table.
By default product's URL key is an attribute which is of type varchar, so its value is saved in catalog_product_entity_varchar table. By default attribute id of product's URL key is 124.
Please let me know if you want more detail.
answered Jul 11, 2017 at 10:31
-
The catalog_product_entity table does not contain URL key in 2.1.7 .However, how can we construct the complete URL from the URL key? Commented Jul 11, 2017 at 11:40
-
I think all the URLs are available in the url_rewrite table. Now the question comes in how to link this URL with the SKU. Commented Jul 11, 2017 at 11:55
-
-
You can get ID from sku and then get url from url rewrite table using path. Commented Jul 11, 2017 at 11:57
Here, I am joining the two tables as explained by Mohit, you can pass the attribute_id directly as well.
select e.entity_id,e.sku,v.value from catalog_product_entity e
inner join catalog_product_entity_varchar v on e.entity_id = v.entity_id and
attribute_id = (select attribute_id from eav_attribute where attribute_code = 'url_key' and entity_type_id = 4)
If you want to append .html part to url:
select e.entity_id,e.sku,CONCAT(v.value,'.html') as url from catalog_product_entity e
inner join catalog_product_entity_varchar v on e.entity_id = v.entity_id and
attribute_id = (select attribute_id from eav_attribute where attribute_code = 'url_key' and entity_type_id = 4)
