I have a website which has two stores fr and en. Now I have almost 1500 products and we would like to add language switching in it. we did it but issue is that most of the product have same URL key for both stores, which are working fine. Some of them have different url for different stores. For them I have added URl rewriting in admin.

Is there a way to find number of product which have different URL for different stores. So ot will be easy for me to add url rewriting for them.

1 Answer 1


So firstly sorry that this is just sql, I have not figured out how to get this in Magento yet but to find all entities that have different url keys the following sql should sort it out.

    `catalog_product_entity_varchar` as default_store
    `catalog_product_entity_varchar` as other_store
    ON other_store.attribute_id = default_store.attribute_id
    AND other_store.store_id != default_store.store_id
    AND other_store.value != default_store.value
    AND other_store.entity_id = default_store.entity_id
where default_store.attribute_id = 97 and default_store.store_id = 0

Where attribute_id 97 is the attribute with the code url_key you could easily join on here so that you do not have to hard code it.

Basically what this sql is doing is loading the default store values for the attribute and then performing a join with the same attribute for any other store where the value for that product is different.

  1. ON other_store.attribute_id = default_store.attribute_id so we have the same attribute,
  2. AND other_store.store_id != default_store.store_id so we have different stores,
  3. AND other_store.value != default_store.value so we have different values,
  4. AND other_store.entity_id = default_store.entity_id so we have the same products,

NOTE: if you are using enterprise edition then the table should be catalog_product_entity_url_key


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.