Retrieving “Not Visible Individually” Simple Products from Magento Database
As a senior developer specializing in e-commerce platforms like Magento, I’ve encountered this query many times while optimizing product visibility for clients. Let’s dive into how to retrieve all simple products that are marked as “not visible individually”.
Understanding the Magento Product Visibility Architecture
In Magento, product visibility is controlled by an integer value stored in the catalog_product_entity_int table under the attribute ID associated with visibility. The possible values and their meanings are:
- 1: Not Visible Individually (the default for simple products)
- 2: Catalog, Search
- 3: In Search
- 4: In Category, Search
- 5: In Category
Writing the SQL Query to Fetch Relevant Simple Products
To fetch all simple products with a visibility value of “1” (not visible individually), you can use the following MySQL query:
SELECT *
FROM catalog_product_entity AS ppe
INNER JOIN catalog_product_entity_int AS pei ON ppe.entity_id = pei.entity_id
WHERE pei.attribute_id IN (42, 43)
AND pei.value = 1;
Let’s break down this query:
- We select all columns from the
catalog_product_entitytable (ppe). This is where Magento stores basic product information like SKU, name, etc. - We join it with the
catalog_product_entity_inttable (pei) on the commonentity_idfield. Thecatalog_product_entity_inttable holds the attribute values for each product. - In the WHERE clause:
- We use
attribute_id IN (42, 43)to filter products where either of these two attributes is set. Attribute IDs are specific to your Magento installation; you can find them in theeav_attributetable.- 42 usually represents the ‘visibility’ attribute.
- 43 often corresponds to the ‘status’ attribute, which we include to ensure only enabled products are fetched.
- We set
pei.value = 1to filter for products that aren’t visible individually.
- We use
Executing the Query and Analyzing Results
You can execute this query directly in your MySQL client or via a script using a library like PDO in PHP:
<?php
$host = 'localhost';
$db = 'your_magento_db_name';
$user = 'your_username';
$pass = 'your_password';
$charset = 'utf8mb4';
$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::ATTR_EMULATE_PREPARES => false,
];
try {
$pdo = new PDO($dsn, $user, $pass, $opt);
} catch (PDOException $e) {
throw new PDOException($e->getMessage(), (int)$e->getCode());
}
// Your query here
$sql = "SELECT * FROM catalog_product_entity AS ppe INNER JOIN catalog_product_entity_int AS pei ON ppe.entity_id = pei.entity_id WHERE pei.attribute_id IN (42, 43) AND pei.value = 1";
$stmt = $pdo->query($sql);
while ($row = $stmt->fetch()) {
echo "Product ID: {$row['entity_id']}, SKU: {$row['sku']}\n";
}
?>
This script fetches the product’s entity_id and sku, outputting them for each matched record.
Conclusion
Retrieving “not visible individually” simple products from your Magento database involves understanding the eav (easy attribute) architecture and writing an appropriate JOIN query. Always remember to filter by both visibility and status attributes if you want only enabled, invisible simple products.
Remember to double-check the specific attribute_id values for ‘visibility’ in your installation, as they can vary between versions or through customizations.
For more advanced Magento SQL query techniques, I recommend checking out our comprehensive guide on Magento database optimization. It provides best practices for managing and querying Magento’s complex relational data structures.