0

I want to select a value for status or visibility on a specific product for which I have only the sku. I have tried many ways but did not arrive a a solution to the problem. Anyone can help me, thanks in advance

I found this in Magento Forum that may be helpful

SELECT entity_id FROM `catalog_product_entity_int`
WHERE attribute_id = (
SELECT attribute_id FROM `eav_attribute`
WHERE `attribute_code` LIKE 'status'
) AND `catalog_product_entity_int`.value = 2
2
  • Do you want SQL or Magento code?
    – DJ Dev
    Dec 20, 2019 at 10:59
  • If possible both , but I prefer SQL code ,thanks in advance Dec 20, 2019 at 11:03

2 Answers 2

1

To verify the visibility of product (either it is visible in catalog or search) you can use the $product->getData('visibility') method. It will give a numeric value which is set as a constant in \Magento\Catalog\Model\Product\Visibility

In product EAV it is represented by the attribute with code 'visibility' so you can make a inner join using product and the eav table like:

SELECT cpei.value FROM catalog_product_entity as cpe
    INNER JOIN catalog_product_entity_int as cpei ON cpe.entity_id = cpei.row_id
    INNER JOIN eav_attribute as ea ON ea.attribute_id = cpei.attribute_id
WHERE cpe.sku = {YOUR SKU GOES HERE} AND ea.attribute_code = 'visibility';
5
  • Thanks @Marco but it is showing an error Dec 20, 2019 at 11:57
  • this line causes an error INNER JOIN catalog_product_entity_int as cpei ON cpe.entity_id = cpei.row_id exactly in row_id Dec 20, 2019 at 11:58
  • Are you using magento 2 or magento 1? Try to change row_id for entity_id Dec 20, 2019 at 12:02
  • I am using magento 1 Dec 20, 2019 at 13:09
  • Ahmed, changing row_id for entity_id worked for you? Dec 20, 2019 at 17:28
0

If you have enabled Flat Product Tables, you can query those tables directly, using the visibility column for your condition. The names of the table(s) will depend on how many stores you have, but it will look similar to this: catalog_product_flat_1 for your default store, catalog_product_flat_2 for the second store that you created, catalog_product_flat_3 for the third, and so forth.

The query would look something like this:

SELECT sku, visibility
FROM catalog_product_flat_1
WHERE visibility in (2, 4);

Note: I added a condition for the visibility values which will include those shown in categories/other default pages, the possible values are as follows:

1    Not Visible Individually
2    Visible in Catalog Only
3    Visible in Search Only
4    Visible in Catalog AND Search

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.