1

How can I find all all child (simple) products that are not visible individually without parent (configurable) products set/linked. these products are not logical as they can never be sold.

I am struggling with this query, however it is not for all to use (attribute id may be different) and I am uncertain if the result is the true answer to my question

Select e.entity_id,e.type_id,e.sku,e.created_at,visib.value FROM catalog_product_entity as e LEFT JOIN catalog_product_entity_int AS visib ON (visib.entity_id = e.entity_id) AND (visib.attribute_id = '95') AND (visib.store_id = 0) WHERE (IF(visib.value_id > 0, 1, 0)) AND e.entity_id  not in (SELECT product_id FROM `catalog_product_super_link`) and type_id='simple' and visib.value = 0
Improve this question

1 Answer 1

Reset to default
2

One thing you should have in mind is that the visibility attribute has values at store view level, so you will have to perform the query for each of your stores.

Here is a query using subselects in order find the simple products which are not associated to any parent product and have visibility 'Not Visible Individually' in the default store (just replace store_id upon convenience with the ID of your store):

select * from catalog_product_entity where entity_id in (select entity_id from catalog_product_entity_int where attribute_id in (select attribute_id from eav_attribute where attribute_code='visibility' and entity_type_id=4) and value = 1 and store_id=0) and entity_id not in (select product_id from catalog_product_super_link) and type_id='simple'

Seeked value = 1 because that's the value of the 'Not visible individually' visibility option (Reference Mage_Catalog_Model_Product_Visibility::VISIBILITY_NOT_VISIBLE = 1)

The query works for any database (even m2 :)).

Improve this answer
5
  • Cool. And it works. However it selects all children without a parent, when it should return all children what are not visible individually without a parent> REALLE APPRECIATE your answer
    – snh_nl
    Commented Mar 5, 2021 at 13:53
  • i did some tests and it returns all simple products without a parent and that are set as not visible individually. i am not sure if you are validating my answer or say it's missing something
    – Diana
    Commented Mar 5, 2021 at 13:57
  • well, if it does the job, please consider marking this as solution, if not, maybe i can help you tweak it until it does the job. (btw, it does the job) :D :D
    – Diana
    Commented Mar 5, 2021 at 14:10
  • Value = 0 should be the filter I guess
    – snh_nl
    Commented Mar 5, 2021 at 14:11
  • you can double check this by inspecting the visibility select options in the admin. the option value is '1' for 'Not visible individually' . you can check in the code as well as you have the constant in the model Mage_Catalog_Model_Product_Visibility::VISIBILITY_NOT_VISIBLE = 1
    – Diana
    Commented Mar 5, 2021 at 14:14

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.