Categories
CMS Development Magento

Magento 1 sql where attribute – yes

I am trying to build a function to get some product data via SQL in order to create a feed for my Magento 1.9 store.

So far the following function works well…

  public function getProducts()
{
            $query = "SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_product'";
    $this->__entityTypeId = (int) $this->_getReadAdapter()->fetchOne($query);

    $query = "SELECT attribute_set_id FROM eav_attribute_set WHERE entity_type_id = '" . $this->__entityTypeId . "'  and attribute_set_name='Default'";
    $this->__attributeSetId = (int) $this->_getReadAdapter()->fetchOne($query);
    $query = "SELECT entity_type_id FROM eav_entity_type WHERE entity_type_code = 'catalog_category'";
    $this->__entityCategoryId = (int) $this->_getReadAdapter()->fetchOne($query);

    $query = "SELECT attribute_set_id FROM eav_attribute_set WHERE entity_type_id = '" . $this->__entityCategoryId . "'  and attribute_set_name='Default'";
    $this->__attributeCategoryId = (int) $this->_getReadAdapter()->fetchOne($query);

    $statusId = $this->__findAttributeID('status');
    $priceId = $this->__findAttributeID('price');
    $specialPrice = $this->__findAttributeID('special_price');
    $sql = "SELECT distinct product.entity_id FROM catalog_product_entity product
                                    INNER JOIN cataloginventory_stock_item stock ON product.entity_id = stock.product_id
                                    INNER JOIN catalog_product_entity_int sta ON product.entity_id = sta.entity_id
                                    INNER JOIN catalog_product_entity_decimal price ON product.entity_id = price.entity_id
                                    WHERE product.attribute_set_id='{$this->__attributeSetId}'
                                    AND sta.attribute_id={$statusId} AND sta.value=1
                                    AND ((price.attribute_id={$priceId} OR price.attribute_id={$specialPrice}) AND price.value <> '0.0000')
                                    AND (
                                            (stock.manage_stock=1 and (stock.is_in_stock=1 AND stock.qty >=0 ))
                                            OR
                                            (stock.manage_stock=0 )
                                    )
                                    ";

    $rows = $this->_getReadAdapter()->fetchAll($sql);
    return $rows;
}

But, I am trying to add a parameter to the query in order to only return products in which the custom attribute “SendToFeed” is set to Yes

Any ideas on how to amend the query?

Leave a Reply

Your email address will not be published. Required fields are marked *