↓ Skip to main content
  1. Dev/

Magento: Get All Products with Categories in a Flat View

·2 mins
     SELECT
        w1.website_id,
        w1.name  as  website_name,
        s1.store_id,
        s1.name  as  store_name,
        p1.entity_id  as  product_id,
        p1.sku,
        pname.value  as  product_name,
        url.value  as  url_path,
        small_image.value  as  small_image,
        msrp.value  as  msrp_price,
        price.value  as  price,
        p1.created_at  as  product_created_at,
        p1.updated_at  as  product_updated_at,
        visibility.value  as  visibility,
        pstatus.value  as  status,
         case
             when
                (pstatus.value  =   1
                     and  visibility.value  >   1 )
             then
                 1
             else   0
         end as  enable_flag,
        c1.entity_id  as  category_id,
        cname.value  as  category_name,
        c1.parent_id,
        c1.created_at  as  category_created_at,
        c1.updated_at  as  category_updated_at
     FROM
        catalog_product_entity p1
             inner join
        eav_attribute p_attr  ON  p1.entity_type_id  =  p_attr.entity_type_id
             and  p_attr.attribute_code  =  'name'
             inner join
        catalog_product_entity_varchar pname  ON  pname.entity_id  =  p1.entity_id
             and  pname.attribute_id  =  p_attr.attribute_id
             inner join
        eav_attribute p_attr2  ON  p1.entity_type_id  =  p_attr2.entity_type_id
             and  p_attr2.attribute_code  =  'url_path'
             inner join
        catalog_product_entity_varchar url  ON  url.entity_id  =  p1.entity_id
             and  url.attribute_id  =  p_attr2.attribute_id
             and  pname.store_id  =  url.store_id
             inner join
        eav_attribute p_attr3  ON  p1.entity_type_id  =  p_attr3.entity_type_id
             and  p_attr3.attribute_code  =  'small_image'
             inner join
        catalog_product_entity_varchar small_image  ON  small_image.entity_id  =  p1.entity_id
             and  small_image.attribute_id  =  p_attr3.attribute_id
             and  pname.store_id  =  small_image.store_id
             inner join
        eav_attribute p_attr4  ON  p1.entity_type_id  =  p_attr4.entity_type_id
             and  p_attr4.attribute_code  =  'msrp'
             inner join
        catalog_product_entity_decimal msrp  ON  msrp.entity_id  =  p1.entity_id
             and  msrp.attribute_id  =  p_attr4.attribute_id
             and  pname.store_id  =  msrp.store_id
             inner join
        eav_attribute p_attr5  ON  p1.entity_type_id  =  p_attr5.entity_type_id
             and  p_attr5.attribute_code  =  'price'
             inner join
        catalog_product_entity_decimal price  ON  price.entity_id  =  p1.entity_id
             and  price.attribute_id  =  p_attr5.attribute_id
             and  pname.store_id  =  price.store_id
             inner join
        eav_attribute p_attr6  ON  p1.entity_type_id  =  p_attr6.entity_type_id
             and  p_attr6.attribute_code  =  'visibility'
             inner join
        catalog_product_entity_int visibility  ON  visibility.entity_id  =  p1.entity_id
             and  visibility.attribute_id  =  p_attr6.attribute_id
             and  pname.store_id  =  visibility.store_id
             inner join
        eav_attribute p_attr7  ON  p1.entity_type_id  =  p_attr7.entity_type_id
             and  p_attr7.attribute_code  =  'status'
             inner join
        catalog_product_entity_int pstatus  ON  pstatus.entity_id  =  p1.entity_id
             and  pstatus.attribute_id  =  p_attr7.attribute_id
             and  pname.store_id  =  pstatus.store_id
             inner join
        catalog_category_product ccp  ON  ccp.product_id  =  p1.entity_id
             inner join
        catalog_category_entity c1  ON  c1.entity_id  =  ccp.category_id
             inner join
        eav_attribute c_attr  ON  c1.entity_type_id  =  c_attr.entity_type_id
             and  c_attr.attribute_code  =  'name'
             inner join
        catalog_category_entity_varchar cname  ON  cname.entity_id  =  c1.entity_id
             and  cname.attribute_id  =  c_attr.attribute_id
             and  pname.store_id  =  cname.store_id
             inner join
        catalog_category_product_index store1  ON  store1.product_id  =  p1.entity_id
             and  store1.category_id  =  c1.entity_id
             inner join
        core_store s1  ON  store1.store_id  =  s1.store_id
             inner join
        core_website w1  ON  s1.website_id  =  w1.website_id

Related

OpenShift Error: Layer 7 Wrong Status, Invalid Response

·1 min
I have been playing with OpenShift for the past several hours. It looks great. But a while back I started to get 503 Internal Server Error. When I checked logs using rhc tail , I saw this error: Layer 7 Wrong Status, Invalid Response 404. I spent an hour or so troubleshooting. Turns out the issue was NetBeans had added src/main/web/app/WEB-INF/jboss-web.xml when I ran the application locally on my machine. I committed this file, thinking I might need it. Deleting it from the repo fixed the issue.

Filter Some Keys in Multi Dimensional Arrays in Php

·1 min
/** * Cleans up multi-dimensional arrays. * 1st dimension is a simple index * 2nd dimension includes the desired keys * * @param mixed $array * @param mixed $keysToInclude */ public function cleanUpArray($array, $keysToInclude) { $returnArray = array(); $i = 0; foreach($array as $item){ foreach($keysToInclude as $key){ $returnArray[$i][$key] = $item[$key]; } $i++; } return $returnArray; }

MySQL Update If Exist Else Insert Procedure

·1 min
Are you tired of checking data in your code before inserting? Well MySQL procedures are here to the rescue. This simple procedure shows how you can do that: CREATE PROCEDURE ` update_insert_user ` ( IN uid2 int ) BEGIN DECLARE last_login2 DATETIME; SELECT ` last_login ` INTO last_login2 FROM ` user ` WHERE ` uid ` = uid2 LIMIT 1 ; IF last_login2 IS NULL THEN INSERT INTO ` user ` ( ` uid ` , ` last_login ` ) values (uid2, now()); ELSE UPDATE ` user ` SET ` last_login ` = now() WHERE ` uid ` = uid2 LIMIT 1 ; END IF ; END