Magento 2: How to Get Product Name and Image Name from Database Using Query

databasemagento2sql

I want to get product name and it's image url from the database in single query. I am using following query. I am able to get product name using attribute_id 73 but i have problem to fetch image name using attribute_id 88.

Query

select product.value as name from catalog_product_entity_varchar as product join catalog_category_product as category on product.entity_id=category.product_id where product.store_id=0 and product.attribute_id=73 and product.attribute_id=88 and category.category_id=3

Best Answer

If you want to retrieve product name and product image togheter you have to use this query

select category.product_id, product1.value as name, product2.value as image
from catalog_category_product as category 
join catalog_product_entity_varchar as product1 on product1.entity_id=category.product_id and product1.attribute_id=60 and product1.store_id=0
join catalog_product_entity_varchar as product2 on product2.entity_id=category.product_id and product2.attribute_id=74 and product2.store_id=0
where category.category_id=3
order by category.product_id asc

Change 60 and 74 with your attribute id 73 and 88.

Related Solutions

Magento – SQL Query to get actual name of custom attribute value

Try following query.

$query = "SELECT ce.sku,           
            ea.attribute_code,
            CASE ea.backend_type
               WHEN 'varchar' THEN ce_varchar.value
               WHEN 'int' THEN ce_int.value
               WHEN 'text' THEN ce_text.value
               WHEN 'decimal' THEN ce_decimal.value
               WHEN 'datetime' THEN ce_datetime.value
               ELSE ea.backend_type
            END AS value
        FROM (select sku,entity_type_id,entity_id from catalog_product_entity ) AS ce
        LEFT JOIN eav_attribute AS ea
            ON ce.entity_type_id = ea.entity_type_id
        LEFT JOIN catalog_product_entity_varchar AS ce_varchar
            ON ce.entity_id = ce_varchar.entity_id
            AND ea.attribute_id = ce_varchar.attribute_id
            AND ea.backend_type = 'varchar'
        LEFT JOIN catalog_product_entity_int AS ce_int
            ON ce.entity_id = ce_int.entity_id
            AND ea.attribute_id = ce_int.attribute_id
            AND ea.backend_type = 'int'
        LEFT JOIN catalog_product_entity_text AS ce_text
            ON ce.entity_id = ce_text.entity_id
            AND ea.attribute_id = ce_text.attribute_id
            AND ea.backend_type = 'text'
        LEFT JOIN catalog_product_entity_decimal AS ce_decimal
            ON ce.entity_id = ce_decimal.entity_id
            AND ea.attribute_id = ce_decimal.attribute_id
            AND ea.backend_type = 'decimal'
        LEFT JOIN catalog_product_entity_datetime AS ce_datetime
            ON ce.entity_id = ce_datetime.entity_id
            AND ea.attribute_id = ce_datetime.attribute_id
            AND ea.backend_type = 'datetime'";

Magento – Get all Magento Categories using SQL query

The problem is that you use an implicit INNER JOIN and define the join conditions for attribute tables in WHERE clauses.

That means, if there is no value saved for one of the attributes with ID 41 and 57, you get no results.

Improved version with LEFT JOIN on the attribute tables and the entity table as main table:

SELECT DISTINCT
    main_table.entity_id AS id,
    at_path.`value` AS path,
    at_name.`value` AS `NAME`,
    main_table.`level`,
    main_table.parent_id
FROM
    catalog_category_entity main_table

JOIN
    eav_entity_type ee
ON
    main_table.entity_type_id = ee.entity_type_id AND ee.entity_model = 'catalog/category'

LEFT JOIN
    catalog_category_entity_varchar at_path
ON
    at_path.entity_id=main_table.entity_id AND at_path.attribute_id = 57

LEFT JOIN
    catalog_category_entity_varchar at_name
ON
    at_name.entity_id=main_table.entity_id AND at_name.attribute_id = 41

Now the column for attribute 57 (which is url_path) will show up as NULL in the result if there is no value.

Best Answer

Related Solutions

Magento – SQL Query to get actual name of custom attribute value

Magento – Get all Magento Categories using SQL query

Related Topic